Read amount text 00000123M as -12.34

Turn the last character into a digit and a sign, with two decimal places.

What you'll make

In Excel: 00000123M becomes -12.34.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1FIND(RIGHT(A8,1),"{ABCDEFGHI}JKLMNOPQR")Finds the last character in the letter list. LET calls this p.15
2VALUE(LEFT(A8,LEN(A8)-1)&MOD(p-1,10))Swaps the last character for its digit. LET calls this n.1234
3IF(p>10,-1,1)*n/100Makes } and J to R minus, then adds the cents.-12.34
4LET( … )Works out p and n once, then uses them.-12.34
5IFERROR( … ,"Review: check the last character")Shows Review when the last character isn't in the list.-12.34

Excel works from the inside out. { and A to I mean plus; } and J to R mean minus.

=IFERROR(LET(p,FIND(RIGHT(A8,1),"{ABCDEFGHI}JKLMNOPQR"),n,VALUE(LEFT(A8,LEN(A8)-1)&MOD(p-1,10)),IF(p>10,-1,1)*n/100),"Review: check the last character")

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

This uses one common letter list. Check that your source uses the same letters before you send amounts.