Need Help with VBA - Formatting column contents to be 5 characters

There is no macro in the attached spreadsheet.

Is there a way to accomplish this with formatting?

Here is a macro to convert numbers to text with leading zeros to make 5 characters. It will display an input box if the cell value is not a number.

Sub FiveCharacters()
Dim cel As Range, rg As Range
Set rg = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
If rg.Row = 1 Then Set rg = Range(rg.Cells(2), rg.Cells(rg.Cells.Count))
rg.NumberFormat = "@"
For Each cel In rg.Cells
    If IsNumeric(cel.Value) Then cel.Value = Format(cel.Value, "0000#")
    If Len(cel.Value) <> 5 Then
        cel.Value = InputBox("Account number must be 5 characters. " & cel.Value & " is not. What should it be?")
    End If
Next
End Sub

Select allOpen in new window

Thank you.  The spreadsheet will have thousands of rows so the input box will not work.  I just need to add 3 leading zeros to column if it does not have five digits.

If you use Custom number format 0000#, the account numbers will appear with leading zeros as needed to five characters. But the value will be stored as a number.

If you have a lookup formula expecting  text value for the account number, VLOOKUP and MATCH won't match it. So I respectfully suggest that you avoid using a Custom number format to get the five characters.

byundt - I did what you mentioned but I am getting an error "Compile Error:  End If without block If"  I cannot seem to get it to work.

Can you post the VBA macro code exactly that you are using now.  It sounds like you may have removed an IF but left the END IF?

---

»bp

Thank you.  Here it is.

Sub FiveCharacters()
Dim cel As Range, rg As Range
   Set rg = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
   If rg.Row = 1 Then Set rg = Range(rg.Cells(2), rg.Cells(rg.Cells.Count))
   rg.NumberFormat = "@"
   For Each cel In rg.Cells
   if IsNumeric(cel.Value) Then cel.Value = Format(cel.Value, "0000#")    
   End If
   Next
End Sub

This should fix that:

Sub FiveCharacters()
    Dim cel As Range, rg As Range
    Set rg = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
    If rg.Row = 1 Then Set rg = Range(rg.Cells(2), rg.Cells(rg.Cells.Count))
    rg.NumberFormat = "@"
    For Each cel In rg.Cells
       If IsNumeric(cel.Value) Then cel.Value = Format(cel.Value, "0000#")
    Next
End Sub

Select allOpen in new window

---

»bp