Pad an amount with stars to a set width
Turn 12.34 into *****12.34 for a width of 10, and flag amounts that don't fit.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXT(ROUND(A8,2),"0.00") | Writes the amount with two decimals, called s. | "12.34" |
| 2 | LEN(s)>B8 | Checks if s is longer than the width. | No, 5 is not more than 10 |
| 3 | "Review amount field width" | Shown when the amount doesn't fit. | Not used for row 8 |
| 4 | REPT("*",B8-LEN(s))&s | One star for each spare place, then the amount. | "*****12.34" |
| 5 | LET( … ) | Names the text s. IF picks part 3 or part 4. | "*****12.34" |
The amount is rounded to cents first. The amounts are made-up.
=LET(s,TEXT(ROUND(A8,2),"0.00"),IF(LEN(s)>B8,"Review amount field width",REPT("*",B8-LEN(s))&s))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 amounts and the width each field needs into the green columns of the Template tab. The star-filled amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use star fill only where your form asks for it. An amount that's too long is flagged, not cut.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Total three amounts if all are numbers
120.00 + 15.50 + 9.75 → 145.25
Get the template →
Show tiny negative amounts as $0.00
-0.004 → $0.00
Get the template →
Keep amounts between set limits
640.25 with limits 25 to 500 → 500
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →