Restore a nine-digit TIN from a number

Turn a TIN that lost its zero, like 12345678, back into the text 012345678.

What you'll make

In Excel: 12345678 becomes 012345678.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The TIN as a number, with its zero lost.12345678
2"000000000"The pattern: always nine digits."000000000"
3TEXT( … )Adds zeros in front until there are nine."012345678"

The result is text, so Excel keeps the leading zero. The TINs here are made-up.

=TEXT(A8,"000000000")

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 IDs. Paste TIN numbers into the green column of the Template tab. The nine-digit TINs fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only on a column you know holds nine-digit TINs. It adds zeros in front but can't recover other lost digits.

Microsoft's help: TEXT function