Leading Zeros based on Len Value Number Formatting

Pmk1710

New Member
Joined
Jun 3, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hey Everyone,

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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,565
Office Version
  1. 365
Platform
  1. Windows
Should everything have a match?

By changing TRIM to VALUE in the column A formula, it goes up to 710 matches.

The reason that I have done it this way is because the sale data has no leading zeros so we have no way of knowing the correct format for each entry. Using VALUE instead of TRIM removes the leading zeros from the ECRS sheet so that both are directly comparable.

This can not distinguish between 2 supposedly different codes that are identical apart from a leading zero. If this is something that can happen then it can not be fixed in excel, or at least not in the workbooks that you have shown here. That would need to be corrected either at the point when the data in 'SUPPLIER U SALES' is generated or where it is sourced from.
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top