Excel Removing 0

Hi Karla,

Unfortunately, if the NDCs in your file no longer contain dashes and some have fewer than 11 digits, there is no Excel formula that can reliably convert them to the correct 11-digit format.

The reason is that NDCs are made up of three segments, and the missing zero may belong in different positions depending on the original NDC format.

Padding on the left is safe here, because Excel only strips zeros from the front of a number. Zeros in the middle or at the end are real digits and are never removed. If a number has 9 digits, the only thing missing is 2 leading zeros.

Formula (assuming the NDC is in A2):

=TEXT(A2,"00000000000")
 

This pads to 11 digits and returns text. If some cells are already stored as text, this works either way:

=RIGHT("00000000000"&A2,11)

Fill it down, then use the padded column for your comparison. For example, to flag which reference NDCs appear on your report (padded reference in column D, padded report NDCs in column B):

=IF(COUNTIF($B:$B,D2)>0,"On report","Not on report")

Or =XLOOKUP(D2,$B:$B,$B:$B,"Not found")

However, left-padding only works if those numbers are 11-digit NDCs that lost leading zeros. If any are true 10-digit NDCs (formatted 4-4-2, 5-3-2, or 5-4-1) with the dashes removed, you can’t tell which segment needs the missing zero, so padding on the left can produce wrong matches. The standard 11-digit format is 5-4-2, and a 10-digit code is converted by adding a zero to the front of whichever segment is short. To do that reliably, you’d need the original dashed versions or the labeler/product/package breakdown.

If you have any questions or need further assistance, please feel free to share them in the comments on this post.

If the answer is helpful, please click 'Yes' and kindly upvote it.

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.