Expand K or M amounts to full numbers
Turn 1.2K into 1200 and 0.5M into 500000.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | UPPER(RIGHT(A8,1)) | Reads the last letter, in capitals. | "K" |
| 2 | "K",NUMBERVALUE( … )*1000 | For K, takes the number before it times 1,000. | 1200 |
| 3 | "M",NUMBERVALUE( … )*1000000 | For M, takes the number times 1,000,000. | Not used for row 8 |
| 4 | "Review scale" | Shown when the last letter isn't K or M. | Not used for row 8 |
| 5 | SWITCH( … ) | Picks the part that matches the letter. | 1200 |
Small k and m work too.
=SWITCH(UPPER(RIGHT(A8,1)),"K",NUMBERVALUE(LEFT(A8,LEN(A8)-1),".",",")*1000,"M",NUMBERVALUE(LEFT(A8,LEN(A8)-1),".",",")*1000000,"Review scale")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 list. Paste short amounts like 1.2K into the green column of the Template tab. The full amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
K means thousand and M means million only if your source says so. Check that first.
Microsoft's help: SWITCH function
OTHER ROUTES YOU MIGHT LIKE

Treat an accounting dash as zero
A dash - → 0, a blank stays blank
Get the template →
Convert a math minus sign in amounts
−1,234.50 with a math minus → -1234.5
Get the template →
Put the decimal point back in amounts
12345 with scale 2 → 123.45
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →