Thank you

Guess I didn't do well explaining. What I need this formula to do is exactly what the "Text to columns" wizard does. All items "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread" need to remain in the same row, I just need this string broken apart so that each value goes to a separate cell in the same row.

I know this will be utilizing LEFT, MID, RIGHT, SEARCH, and LEN to make it happen (maybe others as well) I also suspect there would need to be error handling as well since not every record would contain all 4 items.

Here's the whole story. I am using data validation to create a dropdown list where a choice of upto 4 items from the list (multi select). I found some VBA code that will strings together the selections made separating each with a comma (i.e. "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread") to populate the drop-down cell. The column J, every cell in the column contains the same dropdown and is populated with up to 4 selections.

Next thing I need to do is count the number of times Mash Potato was selected, number of times corn bread was selected and so on. I can search this column directly using COUNTIF together with wild cards which works great. But in a scenario where the list may contain both "corn" and "corn bread" searching "corn" with wild cards would also count the "corn bread" so COUNTIF with wild cards won't work.

By breaking the string down and splitting so each choice goes to a single cell I think I can then group those 4 columns into a range and then perform the COUNT on whole cell values only.

Hope this makes sense, I think it would be pretty much straight forward, but I can also see that it could become quite complex because we need to account for any where from zero to four selection made.

I know there are similar threads here on the subject, I just haven't found a post that hits all the points so I can follow suit by example.

Thank you
Elso