Forum Discussion

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

excel

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

4 Replies

HansVogelaar
MVP
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"}))
- jmscott89
  Copper Contributor
  Oct 12, 2022
  HansVogelaar Amazing Work!! Thanks
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))

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