Read text amounts in a mixed column
Turn amount text like 1,234.56 into numbers, and keep real numbers as they are.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8="" | Checks whether the cell is empty. | No, it has text |
| 2 | ISNUMBER(A8) | Checks whether the amount is already a number. | No, it's text |
| 3 | NUMBERVALUE(A8,".",",") | Reads text as a number. A period marks decimals. | 1234.56 |
| 4 | IF(ISNUMBER( … ) … ) | Keeps a number as it is, or uses part 3 for text. | 1234.56 |
| 5 | IF( … ) | Leaves an empty cell blank. | 1234.56 |
Read it left to right. Real numbers pass through unchanged, so only text gets read. The amounts here are made-up.
=IF(A8="","",IF(ISNUMBER(A8),A8,NUMBERVALUE(A8,".",",")))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own amounts. Paste your amounts, text or numbers, into the green column of the Template tab. The numbers fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Text amounts need a period for decimals. Remove currency signs and labels first.
Microsoft's help: NUMBERVALUE function
OTHER ROUTES YOU MIGHT LIKE

Build an amount from units and cents
-12 units and -34 cents → -$12.34
Get the template →
Read amounts in the required currency
USD 1,234.56 (USD required) → 1234.56
Get the template →
Turn currency text into a number
-$1,234.50 minus the $ label → -1234.5
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →