Turn currency text into a number

Remove a known label like $ so -$1,234.50 becomes the number -1234.5.

What you'll make

In Excel: -$1,234.50 minus the $ label becomes -1234.5.

The formula

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

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 USD, 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 period marks decimals. Amounts like 1.234,50 or (40.00) need a look first.

Microsoft's help: NUMBERVALUE function