Function to calculate a string with formula.

[csleong]

Hi all, a string in a cell A1 is "4000 + 200", without using VBA, how to convert it to a value? Thanks.
3.xlsx

[FDibbins]

A bit long-winded, but try this (It allows for variable length values)...
=LEFT(A1,FIND("+",A1,1)-2)+MID(A1,FIND("+",A1,1)+2,99)

If they will always be in that format...
=LEFT(A1,4)+RIGHT(A1,3)

[benishiryo]

hi csleong. try this array formula:
=SUM(IFERROR(--MID(SUBSTITUTE(A1," + ",REPT(" ",LEN(A1))),(ROW($1:$111)-1)*LEN(A1)+1,LEN(A1)),""))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

it's explained in the file of my link below*Tips & Tutorials I Compiled, sheet 54. the EVALUATE option needs to be macro enabled

[csleong]

Thank you! both solution works fine for me, but Benishiryo's can work even further!
Just a question I want to ask Benishiryo, what is the function of this "--" in the equation?

[FDibbins]

LEFT/MID/RIGHT all create a text answer, even if it looks like a number.

There are a few ways to convert the text back to a number -
add 0 to it
multiply by 1
use a double negative, which 1st converts to a new value (same as adding 0), then uses the 2nd neg to swap the answer back to a pos value

[csleong]

Thanks FDibbins.