Split Excel Cell that has a Formula in it

user_4959591 0
Viewed 54

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.

excel-formula excel
3 Answers

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.

user_57720950

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

user_55147470

You can use text to columns in the Data menu on Excel 365 to specify the delimiter as + that gives you 2 columns with 100 and 200 as text to columns seems to work on the cell formula not the cell value. The solution provided by @Harun24hr is probably better for more complex formula

user_26452520
Related