Split Excel Cell that has a Formula in it

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]")

enter image description here

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.

Related