Oct 12 2022 02:48 AM
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?
Oct 12 2022 03:34 AM
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
Oct 12 2022 03:39 AM
Oct 12 2022 03:43 AM
SolutionIf 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"}))
Oct 12 2022 03:43 AM
SolutionIf 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"}))