Formula Help to Parse a Comma Separated List of Values into Different Cells

Hi Skriddo,

Put your lists into separated cells in a second table. Here are two examples:

Upper example:

Choose a column on the second table by checking the box in the header row cell of that column.

Use the formula below on the first column:

B2: INDEX(Table 2::$A:$C,ROW(),MATCH(TRUE,Table 2::$1:$1,0))

Enter the formula as shown in B2, then fill down to B6.

As written, the formula will return errors when no box is checked. Wrap the formula in an IF statement to avoid the error flags:

B2: IF(COUNTIF(TABLE 2::::$1:$1,TRUE)<1,"",INDEX(Table 2::$A:$C,ROW(),MATCH(TRUE,Table 2::$1:$1,0))0)

Lower example:

Choose the list to be shown by typing a letter into the header box in column B of the table on the left.

The formula in this version is similar to the one in the first version.

B2: INDEX($A:$C,ROW(),MATCH(B$1,'Table 2-1'::$1:$1,0))

Note that the letter is not case sensitive.

Leaving cell B1 empty will produce a column of error flags, which van be avoided with the same type of IF statement as above.

B2: IF(LEN(B$1)<1,"",INDEX($A:$C,ROW(),MATCH(B$1,'Table 2-1'::$1:$1,0)))

Regards,

Barry

Hi Skriddo,

This may get you started.

In Table 1, Column C adds commas to surround the original string. That makes it easier to use a single formula to find the positions of the commas and then pull out single words.

C2 =", "&B2&", " (Fill Down)

D contains 0 as a starting position.

E2 = FIND(",",$C2,D2+1) (Fill Down and Fill Right.)

Table 1-1

Formula in A2 (and Fill Down and Fill Right) =MID(Table 1::$C2,Table 1::E2+2,Table 1::F2−Table 1::E2−2)

You can transpose Table 1-1 to put the lists into columns, but I don't think that is what you want.

More work with the OFFSET function I think!

We can hide the red error triangles and hide intermediate calculation columns.

Regards,

Ian.