Split Excel Cell that has a Formula in it-CodePudding

Is there are way to split an Excel cell that has a formula in it?

For example, the cell has this:

=100 200

(so when viewing it, it says 300)

And I would like to insert two cells to the right, one with 100 and the other with 200.

CodePudding user response：

You may try FILTERXML()

=TRANSPOSE(FILTERXML(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"=","<t><s>")," ","</s><s>")&"</s></t>","//s"))

If your version of excel do not support dynamic array then try-

=FILTERXML(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"=","<t><s>")," ","</s><s>")&"</s></t>","//s[1]")

and for 2nd value-

=FILTERXML(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"=","<t><s>")," ","</s><s>")&"</s></t>","//s[2]")

CodePudding user response：

You can do it like this (broken out) but someone may have a better answer ...

A1 = =100 200

B1 = =MID(FORMULATEXT(A1),2,100)

C1 = =LEFT(B1,FIND(" ",B1) - 1)

D1 = =MID(B1,FIND(" ",B1) 1, 1000)

Throw data in a spreadsheet in those cells and check the result.