Avatar of ArisaAnsar
ArisaAnsar
Flag 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
VBAMicrosoft Excel

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Sam Jacobs

There is no macro in the attached spreadsheet.
ArisaAnsar

ASKER
Is there a way to accomplish this with formatting?

byundt

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ArisaAnsar

ASKER
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
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
byundt

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.
ArisaAnsar

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
ArisaAnsar

ASKER
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
Bill Prew

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy