0

My spreadsheet has variable number of comma separated entries in multiple columns. I need to calculate for each column the max number of entries, insert this many blank columns and then run the text to columns function. Then I need to repeat this on any other columns in the spreadsheet that have more than 1 comma separated entry. For example:

Column 1: Row 1 = 2 entries, Row 3 = 5 entries

Column 2: Row 1 = 6 entries, Row 30 = 4 entries

Thanks in advance.

0
  1. Insert one column to the right of your comma separated values column
  2. Enter formula in the new row for each respective cell

    =LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

  3. Result tells you how many fields are within a given cell (number of commas plus "1")

  4. Via sort or =max(...) formula, determine the greatest number; insert the number of empty columns for a given reference column for your desired purpose.

If this solution solves your problem, please mark it as answered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.