Forum Discussion
Solved
Split data from one column into specific columns
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.
| ID | Column 1 | Code 1 | Code 2 | Code 3 | Code 4 | Code 5 | Code 6 | Code 7 | Code 8 |
1 | 1,3,8,4 | 1 | 3 | 4 | 8 | ||||
| 2 | 6,2,4,5 | ||||||||
| 3 | 3,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!
When
=IFERROR( FILTERXML( "<t><s>"&SUBSTITUTE( $C3,",","</s><s>")&"</s></t>","//s[.=" & RIGHT(D$2, LEN(D$2) - FIND(" ",D$2) ) & "]"), "")
As variant
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...
When
=IFERROR( FILTERXML( "<t><s>"&SUBSTITUTE( $C3,",","</s><s>")&"</s></t>","//s[.=" & RIGHT(D$2, LEN(D$2) - FIND(" ",D$2) ) & "]"), "")
