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.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | B8 | The 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 |
| 5 | SWITCH( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Turn a ten-digit NPI number into text
1234567890 as a number → 1234567890 as text
Get the template →
Restore a nine-digit TIN from a number
12345678 → 012345678
Get the template →
Insert the EIN hyphen preserving zeroes
012345678 → 01-2345678
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →