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"}))

HansVogelaar's avatar
HansVogelaar
MVP
Oct 12, 2022

jmscott89 

Select the range and run this macro:

Sub ConvertValues()
    Dim rng As Range
    Dim v() As String
    Application.ScreenUpdating = False
    For Each rng In Selection
        v = Split(rng.Value, "-")
        v(0) = Format(v(0), "00000")
        v(1) = Format(v(1), "0000")
        v(2) = Format(v(2), "00")
        rng.Value = Join(v, "-")
    Next rng
    Application.ScreenUpdating = True
End Sub

Resources