Search code examples
stringexcelexcel-formulaadditionworksheet-function

Format cell to make calculation with it

My cell A1 contains EUR 1.00 and A2 EUR 2.00. This is in A3:

=A1+A2

I'm supposed to get EUR 3.00 or 3,00 but I got error.

Solution

  • The easiest way to use in cell formulas is to only have numbers in the cells that your adding. i.e. get rid of 'EUR' from A1 and A2 and it will work. Otherwise excel is trying to add strings instead of numbers.

    You can also use the 'right()' function to strip out the 'EUR' from each string like this:

    =RIGHT(A1,4)+RIGHT(A2,4)

    If the input is "EUR 1.5" and "EUR 0.5" and you need "EUR 2,0" as the output try this:

    =SUBSTITUTE("EUR " & (RIGHT(A1,4)+RIGHT(A2,4)),".",",")

    If your set up as comma deliminated decimal values you'll have to substitute first rather than last. Try this:

    ="EUR " & (RIGHT(SUBSTITUTE(A1,".",","),4)+RIGHT(SUBSTITUTE(A2,".",","),4))