SOLVED

Split data from one column into specific columns

New Contributor

Hello

I need to split data from one column in excel to specific columns, depending on what numbers are contained in the combined one.

 

i.e. for each row, I need to split the comma separated data in column B into the corresponding columns. So, if column B has a "1" in it, it is automatically moved to the column headed 'code 1'. If it has a 2 in it, it moves to the column labelled 'code 2', and so on, for every row. And the numbers (and therefore corresponding columns) will go up to 99.

See example below (but it's an excel worksheet): at the moment, the only columns I have populated are 'ID' and 'Column 1' and I have shown for the data in ID row 1 how I would like it to look.

IDColumn 1Code 1Code 2Code 3Code 4Code 5Code 6Code 7Code 8

1

1,3,8,41 34   8
26,2,4,5        
33,2,4,5,6        

 

I have no clue as to how to work in VBA, I am a very basic user of excel.

 

I hope someone can help

 

Thank you!

5 Replies

@sarahhcob 

As variant

image.png

in D3 is

=IF( COUNTIF($C3,"*" & RIGHT(D$2) & "*"), RIGHT(D$2), "")

and drag it to the right and down.

Thank you! It works perfectly, except that when I reach values of 10 and more, it is not recognising the 10, 11, 12 etc. as whole numbers, and is populating code 10 column with a 0, code 11 column with 1, code 12 column with 2, code 13 column with 3 etc, etc...
best response confirmed by Grahmfs13 (Microsoft)
Solution

@sarahhcob 

When

=IFERROR( FILTERXML( "<t><s>"&SUBSTITUTE( $C3,",","</s><s>")&"</s></t>","//s[.="  &    RIGHT(D$2, LEN(D$2) - FIND(" ",D$2) )  & "]"), "")
Perfect, thank you so much!!!

@sarahhcob , you are welcome