Read amounts in the required currency

Turn USD 1,234.56 into 1234.56 when USD is required. Other codes get flagged.

What you'll make

In Excel: USD 1,234.56 (USD required) becomes 1234.56.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(A8,4)=B8&" "Checks the amount starts with the required code.TRUE
2NUMBERVALUE(MID(A8,5,99),".",",")Reads the rest as a number. A period marks decimals.1234.56
3"Review currency"The message when the codes don't match.Not used for row 8
4IF( … )Picks the number or the message.1234.56

Capitals don't matter: usd matches USD. Nothing is converted between currencies. The amounts are made-up.

=IF(LEFT(A8,4)=B8&" ",NUMBERVALUE(MID(A8,5,99),".",","),"Review currency")

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. Paste amounts like USD 1,234.56 into column A and the code you need into column B of the Template tab.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

A flagged row is in another currency. Check it by hand before you use the amount.

Microsoft's help: NUMBERVALUE function