We help IT Professionals succeed at work.

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

ArisaAnsar
ArisaAnsar asked
on
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
Comment
Watch Question

View Solution Only

Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM
SILVER EXPERT

Commented:
There is no macro in the attached spreadsheet.
ArisaAnsar

Author

Commented:
Is there a way to accomplish this with formatting?

byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

ArisaAnsar

Author

Commented:
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.
Mechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you don't want the input box, then delete (or comment out) statements 8, 9 & 10. Statement 7 is the one that adds the leading zeros.
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.