Forum Discussion

jmscott89's avatar
jmscott89
Copper Contributor
Oct 12, 2022
Solved

formatting NDC numbers

I have to format a series of numbers in a 5-4-2 sequence - example 55555-4444-22 and my excel list shows them without zeros at the front..  example 4589-378-1 vs. 04589-0378-01.  How can excel help m...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Oct 12, 2022

    jmscott89 

    If you have a recent version of Microsoft 365, you can use a formula.

    With the original values in A1 and down, enter the following formula inn B1, then fill down:

     

    =TEXTJOIN("-", TRUE, TEXT(TEXTSPLIT(A1, "-"), {"00000", "0000", "00"}))

sivakumarrj's avatar
sivakumarrj
Brass Contributor
Oct 12, 2022
Alternatively try for Concat function
=CONCAT(0,LEFT(A1,5),"-",0,MID(A1,6,3),"-",0,RIGHT(A1,1))

Resources