formatting NDC numbers

[jmscott89]

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 me convert a column of incorrectly formatted numbers into a corrected one?

[#2]

@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

[#3]

Alternatively try for Concat function
=CONCAT(0,LEFT(A1,5),"-",0,MID(A1,6,3),"-",0,RIGHT(A1,1))

[#4 · Accepted solution]

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

[#5]

@Hans Vogelaar Amazing Work!!  Thanks