Read a provider ID from a data snippet
Turn JSON text like {"providerId":"000123"} into 000123, zeros kept.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The text. | "{"providerId":"000123"}" |
| 2 | TEXTAFTER( … ,"""providerId"":""") | Keeps the text after "providerId":". | "000123"}" |
| 3 | TEXTBEFORE( … ,"""") | Keeps the text up to the next quote mark. | "000123" |
| 4 | IFERROR( … ) | Shows Review field when there is no quoted providerId. | "000123" |
Each bracket wraps the one before it, so Excel works from the inside out. Two quote marks in a row stand for one ".
=IFERROR(TEXTBEFORE(TEXTAFTER(A8,"""providerId"":"""),""""),"Review field")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 list. Paste the text into the green column of the Template tab. The provider IDs fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only for short, simple text. A space after the colon, or a providerId listed twice, needs a look.
Microsoft's help: TEXTAFTER function
OTHER ROUTES YOU MIGHT LIKE

Group a 16-character ID in blocks of four
1234567890123456 → 1234 5678 9012 3456
Get the template →
Right-pad an ID for a fixed-width export
A7 with width 4 → [A7 ] (4 characters)
Get the template →
Remove one identifier label at the start
NPI: 1234567890 → 1234567890
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →