Hello. I need help please. I have an excel spreadsheet we get from our customer. Column A has 5 digit number, example 12345. They have formatted the cell under special, adding in a leading zero and adding in a dash between, so finished number looks like, 012-345.
I have a macro that removed the formatting, adds in the leading zero. What I need to complete is to add in the dash. Also it needs to be save as text, not a number. Any help would be greatly appreciated.
Sub LeadingZeros1()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp))
rg.NumberFormat = "@"
For Each cel In rg.Cells
cel.Value = "0" & cel.Value
Next
End Sub
I have tried “0##-###”, but I don’t know what code to use….
Thanks so much
Amy
Hi Amy
Try changing this line from
cel.Value = "0" & cel.Value
to
cel.Value = Format(cel.Value, "000-000")
Hope this helps
Sunny
You could just do this in a formula if it helps - i have used this so many times in my work.
=REPT("0",6-LEN(A1))&A1
Purfleet
To include a dash, you can try TEXT(A3,"000-000")
SunnyKow
Thank you so much. This works great!
Amy
