Im stuck with trying to write a code that will keep specific number formatting per LEN value in VBA. Long store short, I am am setting up a workbook that takes Data from "sheet1" - matches to query pulled data in "sheet2" - and populates results in "sheet3" with information from both "sheet1" and "sheet2" based on finding matching data in "A:A" from each sheet.

The issue is formatting the data between the worksheets in order to populate correct matches. I have Item ID codes that range from 1 digit to 14 digits, and they must retain their "text" value.

The Query data is easy with the TRIM function, because the Query data retains the "text" value, just with unnecessary spaces. So I pull my Query and populate in column B, then have the VBA macro for (A:A) = TRIM(B:B), simple

the issue im having is the following:

in "Sheet1" I copy and paste data from suppliers into (A:A) - the Data is primarily UPC and EAN codes with the occasional PLU retail code - so these codes vary in length. AND some of them have leading Zeros while others do not.

I have set up an IF LEN = "" then numberformat = "" VBA for each value between 1 and 13, but I have no idea how to keep the leading zeros where I need them to be.

Here is my code for defining number format per cell LEN:

Sub FormatItemID()

'Item ID Range Definitions

Dim SALEID As Range

Dim r As Range

'Range Values

Set SALEID = Worksheets("SALE DATA").Range(Worksheets("SALE DATA").Range("a2"), Worksheets("SALE DATA").Range("A2").End(xlDown))

'Format Function

For Each r In SALEID

If Len(r.Value) = 1 Then

r.NumberFormat = "0"

End If

If Len(r.Value) = 2 Then

r.NumberFormat = "00"

End If

If Len(r.Value) = 3 Then

r.NumberFormat = "000"

End If

If Len(r.Value) = 4 Then

r.NumberFormat = "0000"

End If

If Len(r.Value) = 5 Then

r.NumberFormat = "00000"

End If

If Len(r.Value) = 6 Then

r.NumberFormat = "000000"

End If

If Len(r.Value) = 7 Then

r.NumberFormat = "0000000"

End If

If Len(r.Value) = 8 Then

r.NumberFormat = "00000000"

End If

If Len(r.Value) = 9 Then

r.NumberFormat = "000000000"

End If

If Len(r.Value) = 10 Then

r.NumberFormat = "0000000000"

End If

If Len(r.Value) = 11 Then

r.NumberFormat = "00000000000"

End If

If Len(r.Value) = 12 Then

r.NumberFormat = "000000000000"

End If

If Len(r.Value) = 13 Then

r.NumberFormat = "0000000000000"

End If

Next

End Sub

What am I missing to retain leading zeros based on (len)r.value, but still retain correct (len).rvalue?

In other words:

How can I keep PLU "1234" as "1234" and not "01234", "001234", "0001234", etc... and at the same time, keep UPC "012345678901" to populate with its leading zero?

And if there is a way to shorten this VBA to begin with, im all up for it!

Peter