Format a TIN by its EIN, SSN or ITIN type

Write an SSN or ITIN like 123-45-6789 and an EIN like 02-3456789.

What you'll make

In Excel: 123456789 + SSN becomes 123-45-6789.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1B8The TIN type: EIN, SSN or ITIN."SSN"
2"EIN",LEFT( … )For an EIN, adds a hyphen after two digits.Not used for row 8
3"SSN",LEFT( … )For an SSN, groups the digits 3-2-4."123-45-6789"
4"ITIN",LEFT( … )For an ITIN, groups the digits 3-2-4 too.Not used for row 8
5SWITCH( … )Picks the part that matches the type."123-45-6789"

Read it left to right. For any other type, the cell says Review TIN subtype. The digits are text, so zeros at the front stay. The numbers here are made-up.

=SWITCH(B8,"EIN",LEFT(A8,2)&"-"&RIGHT(A8,7),"SSN",LEFT(A8,3)&"-"&MID(A8,4,2)&"-"&RIGHT(A8,4),"ITIN",LEFT(A8,3)&"-"&MID(A8,4,2)&"-"&RIGHT(A8,4),"Review TIN subtype")

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 each TIN's digits and its type into the green columns of the Template tab. The results fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Take the type from your records. Nine digits alone can't tell an EIN from an SSN.

Microsoft's help: SWITCH function