Link to home
Start Free TrialLog in
Avatar of ArisaAnsar
ArisaAnsarFlag for United States of America

asked on

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

Hi All,
I have a macro that I used regularly.  However, need to update the macro to reformat column A.
Column A should be formatted to text and should always be five characters for the account number.
So if the account number is 14, add 000 infront to make it five characters.
Test-Spreadsheet.xlsx
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

There is no macro in the attached spreadsheet.
Avatar of ArisaAnsar

ASKER

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

Open 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.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Bill Prew
Bill Prew

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

Open in new window


»bp