Copying delimited values within a cell into their own cells

[rybals]

Hi all I'm currently working on a worksheet that has some cells containing numbers delimited with commas. All these cells are in the same column. The cells either have a single value with no commas, two values separated by one comma or three values with a comma after the first and second. I would like to copy these values and put them into their own columns/cells (without the commas). I have managed to get it working when there is one or three values separated by commas, but not two. Here are the formulas I am using for the three columns:

Cell A1 contains 1,2 or 3 values separated by commas (actually on another worksheet but I have called A1 to simplify things).

Cell B1 Formula: =IF(ISNUMBER(SEARCH(",",A1)),LEFT(A1,SEARCH(",",A1,1)-1),IF(A1<>"",A1,""))
Works for 1,2 or 3 values in A1.

Cell C1 Formula: =IF(ISNUMBER(SEARCH(",",A1)),MID(A1,SEARCH(",",A1)+1,SEARCH(",",A1,SEARCH(",",A1)+1)-SEARCH(",",A1)-1),"")
Works for 1 or 3 values in A1. Not working for 2 (cell displays #VALUE! instead of value).

Cell D1 Formula: =IF(ISNUMBER(SEARCH(",",A1)),RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1)+1)),"")
Works for 1 or 3 values in A1. Not working for 2 (cell displays #VALUE! but should be blank).

I would also like to eventually expand this to be able to read half a dozen delimited values from one cell and insert data into their own cells/columns.

If anyone is able to provide a better (and fully working) formula that is more easily expandable, it would be much appreciated. Thank you.

 

[steve the fish]

Try this:

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),LEN($A1)*COLUMNS($A1:A1)-LEN($A1)+1,LEN($A1)))

 

[rybals]

Thanks. That works great when the source cell is in same table, however I cannot get it to work when the source is in another worksheet i.e. sheet2!A1.

 

[steve the fish]

It's irrelevant which sheet it's in as long as you refer to that cell in all instances in the formula.