IF List, Extract text before first comma ELSE copy value

I hope the title is self explanatory.

Basically I have a text column with data values seperated by commas as well single data points within cells. If the value contains a list I would like to extract text before first comma Else I would like to just copy the single data point into a new row.

As well, would it be possible to go further and extract and subsequent substring in between commas or after into new columns...?

excel question example.jpg

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Also... please confirm that you are still using Excel 2003, or UPDATE your profile...

Edit 2008...

That's a non-editable picture. I asked for an Excel file!!!!

However, this should do what you want:

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",125)),125*COLUMNS($A:A),125))

dragged across and down.

attachment is in original post.

I am getting a SPILL error message.

Thanks for your help btw!!!

And did you try the formula?

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.