Build a labeled NPI and TIN line
Write NPI: 1234567890 | TIN: 012345678, skipping a missing one.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | IF(A8="",…,"NPI: "&A8) | Writes NPI: and the NPI, if there is one. | "NPI: 1234567890" |
| 2 | IF(B8="",…,"TIN: "&B8) | Writes TIN: and the TIN, if there is one. | "TIN: 012345678" |
| 3 | TEXTJOIN( … ) | Joins them with a bar and skips empty ones. | "NPI: … | TIN: …" |
The " | " and TRUE inside TEXTJOIN mean: a bar between, skip empty parts. The IDs here are made-up.
=TEXTJOIN(" | ",TRUE,IF(A8="","","NPI: "&A8),IF(B8="","","TIN: "&B8))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 NPIs and TINs, stored as text, into the green columns of the Template tab. Leave a cell empty when there isn't one.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Check and format each ID before you paste it. The formula only adds labels, it doesn't fix the digits.
Microsoft's help: TEXTJOIN function
OTHER ROUTES YOU MIGHT LIKE

Keep only IDs of an approved type
NPI + 1234567890 → 1234567890 (approved type)
Get the template →
Choose an NPI or a local fallback
1234567890 + 000123 → NPI: 1234567890
Get the template →
Format an NPI or EIN by its type
EIN + 023456789 → 02-3456789
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →