'Text to column' delimit function but for multiple cells-CodePudding

I have 10 cells with data in a row, and I have to delimit them one by one by using the 'text to column' function in excel and placing the output in a separate column/area. Is it possible to delimit multiple cells and place the outputs in order? Below is an example of what I am aiming for:

My goal is to delimit the Col_A to Col_D cell values and place the outputs in the Part1 and Part 2 columns to the right in order like so:

The method I am using now is to use the 'Text to columns' function to manually delimit each of the Col_A to Col_D 1 by 1 into their respective output columns.

Is there a way for me to delimit the Col_A to Col_D values all at once by order?

PS: I'm not very good at VBA, so if the solution requires VBA/Macros kindly leave some documentation for me. Thank you.

CodePudding user response：

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(";",,1:1),";","</y><y>")&"</y></x>","//y")) is the best option without VBA (like JvdV already posted)

For non-Windows you could use this in a cell in the first column: =IFERROR(IF(ISEVEN(COLUMN()),RIGHT(INDEX(1:1,,ROUNDUP(COLUMN()/2,0)),LEN(INDEX(1:1,,ROUNDUP(COLUMN()/2,0)))-FIND(";",INDEX(1:1,,ROUNDUP(COLUMN()/2,0)))),LEFT(INDEX(1:1,,ROUNDUP(COLUMN()/2,0)),FIND(";",INDEX(1:1,,ROUNDUP(COLUMN()/2,0)))-1)),"")

CodePudding user response：

With ms365, try:

Formula in E1:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(";",,A1:D1),";","</s><s>")&"</s></t>","//s"))

For previous version of Excel there would be multiple routes; one could be:

=FILTERXML("<t><s>"&SUBSTITUTE($A1&";"&$B1&";"&$C1&";"&$D1,";","</s><s>")&"</s></t>","//s["&COLUMN(A1)&"]")

Drag right. Or, a bit more dynamic:

=INDEX(CHOOSE({1;2},MID($A1:$D1,FIND(";",$A1:$D1) 1,LEN($A1:$D1)),LEFT($A1:$D1,FIND(";",$A1:$D1)-1)),MOD(COLUMN(),2) 1,CEILING(COLUMN(A1)/2,1))

Confirm through CtrlShiftEnter and drag right.

CodePudding user response：

Just for the record, another which will work for all Excel versions:

=TRIM(MID(SUBSTITUTE(INDEX($A1:$D1,INT((1 COLUMNS($A1:A1))/2)),";",REPT(" ",99)),99*ISEVEN(COLUMNS($A1:A1)) 1,99))

and copied right.

Or, for O365, if returning the results to a rectangular range is acceptable:

=LEFT(MID(A1:D1,FIND(";",A1:D1)^{0;1} {0;1},99),FIND(";",A1:D1)^{1;9}-1)