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.

What you'll make

In Excel: 12.34 with width 10 becomes *****12.34.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXT(ROUND(A8,2),"0.00")Writes the amount with two decimals, called s."12.34"
2LEN(s)>B8Checks 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
4REPT("*",B8-LEN(s))&sOne star for each spare place, then the amount."*****12.34"
5LET( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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