Excel formula for string split into specific cell-CodePudding

any formula that works on both excel and googles sheet for splitting text to their column.

I want a formula that split text from string to specific column.

CodePudding user response：

For Excel ms365, try:

Formula in B2:

=IFERROR(VLOOKUP(B1:H1,TRIM(TEXTSPLIT(A2,": ","|",1)),2,0),"")

Or, if you are not willing to remove the needless colon's and spaces in your data in the 1st row:

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(B1:H1,":",)),TRIM(TEXTSPLIT(A2,": ","|",1)),2,0),"")

I suppose the GS equivalent for the 1st option would be:

=INDEX(IFERROR(VLOOKUP(B1:H1,TRIM(SPLIT(FLATTEN(SPLIT(A2,"|")),":")),2,0),""))

But GS is not my forte.

CodePudding user response：

You may try this as well in Excel for MS365

• Formula used in cell B3 --> Needs to drag right.

=TEXTBEFORE(TEXTAFTER($A3&" | ",B$1,,,,"")," | ",,,,"")

Or,

• Formula used in cell B5 --> It will spill.

=DROP(IFERROR(REDUCE(0,A5,LAMBDA(x,y,
VSTACK(x,BYCOL(B1:I1,LAMBDA(z,TEXTBEFORE(
TEXTAFTER(y&" | ",z,,,,"")," | ",,,,"")))))),""),1)

Or,

• Formula used in cell B7 --> Will spill right but needs to drag down for below cells.

=BYCOL(B1:I1,LAMBDA(x,TEXTBEFORE(
TEXTAFTER($A7&" | ",x,,,,"")," | ",,,,"")))