Variable Leading Zeros (Number Formatting)

user_12458212 0
Viewed 510

If I have a list of values in column A ranging from 1-1000, how can I format the column so each value is '0000' ie. based on len(max(A:A)) which would be 4. This would need to be dependent on the max value.

If the max in column A was 10,500, I would want every value to have a format '00000'. Any way to automatically set this - either in VBA or another formatting method? Thanks

vba excel
3 Answers
Option Explicit

Public Sub example()
    ' define the location/column
    Dim Location As Range
    Set Location = ThisWorkbook.Worksheets("Sheet1").Range("A:A")
    
    ' get the amount of digits the maximum number has
    Dim Digits As Long
    Digits = Len(CStr(Application.WorksheetFunction.Max(Location)))
    
    ' set the number format of the location to the amount of digits
    Location.NumberFormat = String(Digits, "0")
    
End Sub

or even better write a generic procedure that you can re-use with any range:

Option Explicit

Public Sub SetNumberFormatToMaxDigits(ByVal Location As Range)
    Dim Digits As Long
    Digits = Len(CStr(Application.WorksheetFunction.Max(Location)))
    
    Location.NumberFormat = String(Digits, "0")
End Sub

and just call it like

SetNumberFormatToMaxDigits ThisWorkbook.Worksheets("Sheet1").Range("A:A")
user_32196130 
Range("A2:A10").NumberFormat="00000"

should do it. For more dynamic, something like:

set rng = Range("A2:A10")
rng.NumberFormat = Left("0000000000000", len(application.max(rng)))
user_785220

To set your NumberFormat to have Leading Zeros in VBA, you can use Range.NumberFormat="0…0" So, 4 zeros would beRange.NumberFormat="0000", 5 zeros would beRange.NumberFormat="00000", etc.

To dynamically create the correct number of zeros, we can use the String function. For example, Range.NumberFormat = String(4, "0") for 4 zeroes, or Range.NumberFormat = String(5, "0") for 5 zeroes.

To count the maximum length of text in a range, we can use the SUMPRODUCT, MAX, and LEN Worksheet Functions: =SUMPRODUCT(MAX(LEN(A:A))). To ensure that this is always at least 4, we include 4 in the MAX: =SUMPRODUCT(MAX(4, LEN(A:A))).

To use that in VBA, we can pass it to the Evaluate function of the Worksheet. For example, if the column is in Sheet1, then you can use Sheet1.Evaluate("SUMPRODUCT(MAX(4, LEN(A:A)))") to get the maximum length of that column. (If you have headers, you might need to specify your data more exactly than just A:A for the entire column — or ensure that the header is 4 characters or fewer!)

And, combining everything into a neat little bit of code for a Range:

'Define Range however you want
Dim Target AS Range
Set Target = Sheet1.Columns(1)

'Set the NumberFormat
With Target
    .NumberFormat = String(.Worksheet.Evaluate("SUMPRODUCT(MAX(4,LEN(" & .Address() & ")))"), "0")
End With
user_92909860
Related