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

  • Thread starter DataFreakFromUtah
  • Start date

D

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
 
Ad

Advertisements

M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top