Macro to insert "0" in front of 4 character string

  • Thread starter Thread starter Lowell B. Copeland
  • Start date Start date
L

Lowell B. Copeland

I need to write a small macro that will accomplish the
following. I am working with a column of numbers (as text)
they are zip codes. The zipcodes that begin with zero have
had their starting zero removed by the program in which
they were initially stored. I need to insert a zero in
front of any zipcode that has only 4 characters. Here is
what I am currently doing in an active cell like [2345]

[F2] to go into edit mode
[Home] to move to the front of the string
[0] to insert the "0" at the front of the string
[Enter] to go down to the next cell.

I want to create a keystroke macro that is faster

Any ideas.

Thanks Lowell
 
Just format the selection as TEXT and use this
Sub insertzero()
For Each c In Selection
If Len(c) = 4 Then c.Value = "0" & c
Next
End Sub
 
Sub Addzero()
Dim cell As Range
Selection.NumberFormat = "@"
For Each cell In Selection
cell.Value = Right("000000" & cell.Value, 5)
Next
End Sub


worked for me. Select the range of cells containing zip codes (it won't
hurt the ones with 5 numbers) and run the macro
This assumes only 5 digit zipcodes - if you have 9 digit zipcodes this won't
work.
 
Back
Top