Extracting/transposing data from a delimited text string

[nyconfidential]

Hi all - I have a delimited string of numbers/letters in one cell that I am trying to transpose into another set of cells(the string could be something like "A, B, 179, C")

Normally I would just use the split function in VBA, break this into an array and assign each cell I want to insert into the array index value, but I am not allowed to do this in case(has to be done via formula, no code).

So, is there is a simple way to take a string like ""A, B, 179, C" and assign each value to a cell via a formula? This is what I want the cells to look like:

A1 - "A"
A2 - "B"
A3 - "179"
A4 - "C"

Thanks!

 

[tyija1995]

Hey,

If each part is separated by a comma then assuming the string is in cell A1:

A2:

TRIM(MID(SUBSTITUTE($A$1,CHAR(44),REPT(" ",LEN($A$1))),(ROW()-2)*LEN($A$1)+1,LEN($A$1)))

Drag this down the A column to uncover the string in all of its glory!

 

[nyconfidential]

Beautiful, thanks Tyija!!!

 

[tyija1995]

No problem!!

Also, if the delimiter was to change then all you have to edit would be the CHAR(44) (which is a comma) to whatever the new delimiter is, e.g. CHAR(59) (semi-colon), so it's quite flexible.
Or you could put the delimiter in to B1 and reference that instead of using CHAR function.

Glad I could help