Turn a ten-digit NPI number into text

Keep all ten digits of 1234567890 as text, so it never shows as 1.23E+09.

What you'll make

In Excel: 1234567890 as a number becomes 1234567890 as text.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ISNUMBER(A8)Checks the cell holds a real number.Yes, a number
2A8=INT(A8)Checks the number has no decimals.Yes, a whole number
3A8>=1000000000, …Checks the number has exactly ten digits.Yes, ten digits
4TEXT(A8,"0")Writes all ten digits as text."1234567890"
5IF(AND( … ))Gives the text when every check passes, or flags it."1234567890"

It checks the shape only, not whether the NPI is real. The NPIs in this file are made-up.

=IF(AND(ISNUMBER(A8),A8=INT(A8),A8>=1000000000,A8<10000000000),TEXT(A8,"0"),"Review NPI")

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 NPIs. Paste NPI numbers into the green column of the Template tab. The text versions fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Check every Review NPI row against the original. A missing first digit is not added back.

Microsoft's help: TEXT function