Format an NPI or EIN by its type
Keep an NPI as it is, and write an EIN like 02-3456789.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The ID type: NPI or EIN. | "EIN" |
| 2 | "NPI",B8 | For an NPI, shows the digits as they are. | Not used for row 8 |
| 3 | "EIN",LEFT( … ) | For an EIN, adds a hyphen after two digits. | "02-3456789" |
| 4 | "Review ID type" | What the cell says for any other type. | Not used for row 8 |
| 5 | SWITCH( … ) | Picks the part that matches the type. | "02-3456789" |
Read it left to right. The digits are text, so zeros at the front stay. The IDs here are made-up.
=SWITCH(A8,"NPI",B8,"EIN",LEFT(B8,2)&"-"&RIGHT(B8,7),"Review ID type")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 ID type and its digits into the green columns of the Template tab. The IDs fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
This doesn't count digits. Check that each NPI has ten and each EIN has nine.
Microsoft's help: SWITCH function
OTHER ROUTES YOU MIGHT LIKE

Display a numeric EIN as 01-2345678
12345678 → 01-2345678
Get the template →
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 →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →