Pad a code with zeros to a set width

Turn the code A7 into 00A7 for a width of 4, keeping letters and zeros.

What you'll make

In Excel: A7 + width 4 becomes 00A7.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEN(A8)>B8Checks if the code is longer than the width.No, 2 is not more than 4
2"Review width"Shown when the code is too long.Not used for row 8
3REPT("0",B8-LEN(A8))One zero for each missing place."00"
4A8The code itself, added after the zeros."00A7"
5IF( … )Uses part 2 if part 1 says yes, else parts 3 and 4."00A7"

Read it left to right. The & joins the zeros and the code. The codes here are made-up.

=IF(LEN(A8)>B8,"Review width",REPT("0",B8-LEN(A8))&A8)

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 codes. Paste codes, stored as text, and the width each needs into the green columns of the Template tab. The padded codes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Pad only codes whose system asks for zeros in front. A code that's too long is flagged, not cut.

Microsoft's help: REPT function