Add leading zero(s) to cell values in selection - An Example

[DataFreakFromUtah]

No question here, just an Excel VBA procedure for the archive.

Search Criteria: Add leading zeros to number, cell value, lead cell
value with zeros, precede value with zero, add zero before value, and
concatenate number with zero

Sub LeadingZeroAddPrompt()

'Prompts user and adds the leading zeros to cell value for all
'cells in selection. Note this procedure must format values as TEXT
'for the procedure to work properly.


Dim v As Integer
On Error Resume Next
v = InputBox("Enter # of zeros to add to front. Five (5) is the
most you can add.", "Add Leading Zeros")

If v = 1 Then
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = "0" & cell.Value
Next cell
End If

If v = 2 Then
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = "00" & cell.Value
Next cell
End If
If v = 3 Then
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = "000" & cell.Value
Next cell
End If
If v = 4 Then
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = "0000" & cell.Value
Next cell
End If
If v = 5 Then
For Each cell In Selection
cell.NumberFormat = "@"
cell.Value = "00000" & cell.Value
Next cell
End If
End Sub

 

[Mike Ogden]

I've done similar functions in the past before it dawned on me that there's
a much simpler way to achieve the same result:

v = InputBox("Enter # of zeros to add to front. Five (5) is the most you can
add.", "Add Leading Zeros")
for each cell in Selection
cell.Value = right("000000000000" & cell.Value, len(cell.Value) + v)
next