Read amounts like 1.234,56 as numbers

Remove a known label like € so 1.234,56 € becomes the number 1234.56.

What you'll make

In Excel: 1.234,56 € becomes 1234.56.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The amount, written as text."1.234,56 €"
2B8The currency label to remove."€"
3SUBSTITUTE( … ,"")Takes the label out of the amount."1.234,56 "
4NUMBERVALUE( … ,",",".")Reads it as a number. A comma marks decimals.1234.56

Each bracket wraps the one before it, so Excel works from the inside out. The amounts here are made-up.

=NUMBERVALUE(SUBSTITUTE(A8,B8,""),",",".")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own amounts. On the Template tab, paste amounts into column A. Put the label they use, like € or EUR, into column B.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This only works when a comma marks decimals. Amounts written like 1,234.56 won't read right.

Microsoft's help: NUMBERVALUE function