I have some data in an MS Excel 2007 cell that I want to split into multiple columns based on a delimiter.
For example take the following data in a single cell -
I use a formula [SUBSTITUTE(A1,CHAR(10),";
")] to remove the spaces (Alt+Enter) at the end of each line to to get the following -
Now I want to take the above data and split into 2 columns so that I get all the colors in one column with a "," [comma] between them and the dash numbers in the other column with a "," [comma] between them. So for example I want the following -
Blue/Green,Dark Teal,Wine,Royal = Column 1
-07,-44,-63,-41 = Column 2
I would like to point out that some of the cells in the column in question may have one color, two colors, three colors, etc. so each cell is not the same. I am attaching an image to illustrate what I am looking for.
Any help with this is greatly appreciated.