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.
>Solution :
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]")
