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.

What you'll make

In Excel: 1,234.50 becomes 1234.5; 1,23.00 becomes Review amount pattern.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1n,NUMBERVALUE(A8,".",",")Reads the text as a number, called n.1234.5
2TEXT(n,"#,##0.00")=A8Writes 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
4IF( … )Gives n when they match, otherwise part 3.1234.5
5IFERROR( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own amounts. Paste amount text into the green column of the Template tab. The amounts fill in by themselves.
  3. 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