Read exact 1,234.50 amount text
Turn 1,234.50 into 1234.5, and flag text like 1,23.00 with a misplaced comma.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | n,NUMBERVALUE(A8,".",",") | Reads the text as a number, called n. | 1234.5 |
| 2 | TEXT(n,"#,##0.00")=A8 | Writes n as 1,234.50 and compares it with the cell. | Yes, they match |
| 3 | "Review amount pattern" | The flag for text that doesn't match. | Not used for row 8 |
| 4 | IF( … ) | Gives n when they match, otherwise part 3. | 1234.5 |
| 5 | IFERROR( … ) | Also flags text that isn't a number at all. | 1234.5 |
LET gives the number a short name, n. The amounts are made-up.
=IFERROR(LET(n,NUMBERVALUE(A8,".",","),IF(TEXT(n,"#,##0.00")=A8,n,"Review amount pattern")),"Review amount pattern")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 amount text into the green column of the Template tab. The amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Only amounts written like 1,234.50 pass. Usable amounts like 1234.50 get flagged too, so check each one.
Microsoft's help: NUMBERVALUE function
OTHER ROUTES YOU MIGHT LIKE

Turn cents into amounts, flag part cents
12345 cents → 123.45
Get the template →
Read amount text 00000123M as -12.34
00000123M → -12.34
Get the template →
Read CR as negative and DR as positive
125.50 CR → -125.5
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →