Leading Zeros based on Len Value Number Formatting

[Pmk1710]

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]

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

Instead of setting each length individually, you could use something like

r.numberformat = worksheetfunction.rept("0",r.value)

to set the number of zeros.
The thing that you need to look at is how the leading zeros are currently applied. If that is already done by formatting then you need to look at the existing format, not the cell value. Any cell value that is formatted to show leading zeros doesn't actually contain those zeros as part of the value so LEN(value) will always be less than the actual number of characters seen in the cell.

We would need to see how the zeros are currently applied in order to identify a way to preserve them. You mention using TRIM on the values which could be converting a text string to numeric on the fly, it might be better to loop through and get the length of the trimmed value before actually trimming it.

Something like

r.Numberformat = WorksheetFunction.Rept("0", Len(Trim(r.Value)))
r.Value = Trim(r.Value)

But that is just an untested theory based on how I have interpreted your description.

 

[Pmk1710]

Thanks Jasonb75,

Let me start by clarifying a little bit more in an effort to give you a better visual of what im trying to do (and mind you, I am a bit new to VBA, so I may be over-coding and not understanding certain shortcuts).

The workbook im creating has 3 different sheets -
Sheet1 - "SALE DATA"
Sheet 2 - "ECRS"
Sheet 3 - "MATCH"

Sheet 2 - is where the Database Query Connection I created is mapped to. This is the only worksheet that I have to use the "TRIM" function to clean up the Item IDs that are populated from the Query Connection. The query places additional spaces to the value (UPC) in Column "B", so Column "A" has the VBA Code:

Worksheets("ECRS").Range("A:A").Formula = "=TRIM(B:B)"

And this works just great for this specific worksheet

Sheet 1 - I add data from other workbooks provided by my suppliers. Mostly Copy and Paste Values in an effort to maintain the number formatting, but sometimes that doesnt work. Other times, I may manually type in the data.

Overall - What im doing (and I have a code already) is:

If Sheet 1 (A:A) = Sheet 2 (A:A) then copy Sheet 1 + Sheet 2 and paste in Sheet 3

But - the issue is formatting the Value on Sheet 1 (A:A) to match exactly what is on Sheet 2(A:A) with or without leading Zeros.
The easiest way I have been able to do it so far is formatting as text to the largest format - being "0000000000000"
But this will add multiple leading zeros to items that dont require leading zeros:
ie "1234" turns into "0000000001234" - not what I need

This is why I attempted to pre-set r.values with the LEN VBA. but like you stated, the LEN(value) doesnt recognize the leading zero and drops it.

So, im stuck with trying to make Sheet 1 (A:A) dynamically change its formatting to show true value of the data present row by row opposed to doing a blanket format of "00000000000000" for the entire column just to get correct leading zeros where they belong, but ultimately adding leading zeros where they dont belong.

Make sense?

 

[jasonb75]

With one of the UPC cells selected, do you see the leading zeros in the formula bar?

We need to establish how the leading zeros get there originally, if there are spaces in the cell then it means that it is text so the leading zeros will be part of the string. This means that what I have already suggested should work. It is not clear from your reply above if you have tried my suggestion or not.

If you don't see the leading zeros in the formula bar, but you do see them in the cell then that means that it is already applied by formatting so you simply need to mimic the existing format with something like

r.NumberFormat = Range("B2").NumberFormat

 

[Pmk1710]

No, I dont see the leading zeros in the formula bar, or in the cell.

I only get that result when I convert the entire column to TEXT, but then at times im still left with Scientific notation based on how the original data was copied/presented. That would be the first correction I would make before copying the data into my Matching workbook - going into formatting, or create a new column and have said column = text(A2, "000000000000").

Here is a random though - a bit backwards - but could work.

Lets say instead of TRIM in Sheet 2 - I Format to the most extreme - "00000000000000"
I do the same on Sheet 1
Match the formatted numbers as "00000000000000"
then vlookup the incorrect formatted number to find the correctly formatted number from Sheet 2 and re-populate into Sheet 3

think that will work?

 

[Pmk1710]

Ill also add another layer to why Im on the verge of breaking Excel -

Im trying to create a tool for others to use that are not familiar with formatting UPC/PLU codes, etc correctly. I know how to do it, but to ask them to follow the same directions and steps I would take without VBA code would be a nightmare - hence why im just trying to automate it all in the background

 

[jasonb75]

I only get that result when I convert the entire column to TEXT, but then at times im still left with Scientific notation based on how the original data was copied/presented.

Now that is a whole different story, everything up to this point (unless I missed something) implied that the data in column B is in the raw format. The leading zeros are shown there, if the cells are formatted as text then they would only be shown if they were in the string, which means trimming should leave the correct number of characters.

I think that we could end up going in circles all day trying to find the correct place to extract the format without having the original raw data to look at. Possibly someone else will pick up your thread and see something that I'm not. I'm done here for today, if you can upload the workbook with the original unchanged raw data to dropbox / one drive and post the link to it here then I will have a look at it when I come back online tomorrow if it has not already been resolved by another member.

 

[Pmk1710]

OK, I think I understand where you might be confused.

Yes, in Sheet2 we do see leading zeros in cell and formula bar. my main issue is working on sheet 1.
I was talking about sheet 1. I need to make sure my sheet 1 data is capable of matching the data on sheet 2. thats the only place im trying to format the codes correctly.

Ill upload a sample workbook shortly
Peter

 

[jasonb75]

In that case, you will need to take the format from Sheet2 to preserve the leading zeros correctly. It is highly likely that when you copy the UPC from sheet2 to sheet1 that it is being converted to a proper number and being truncated. I can't say for definite that that is what is happening but it sounds probable. I'll look into it in more detail when I get home tonight.

 

[Pmk1710]

OK - I have attached 2 excel workbooks.

Workbook 1 = "GENERATOR" - this is the book im trying to set up as a tool to find out what data I have on file from Workbook 2
Workbook 2 = "SUPPLIER U SALES" one example of many workbooks I receive from my product vendors - This data changes.

What I do
1. I take the 3 columns from "SUPPLIER U SALES" - copy and paste into "GENERATOR" - worksheet 1
2. Go to "GENERATOR" - worksheet 2, and refresh the TRIM formula in column "A" to make sure my automatic query pull is up to date and all item codes are as clean as they can be
3. Go to "GENERATOR" - worksheet 3, and press the "MATCH" macro button
4. This takes data from "GENERATOR"-worksheet 1, finds related value (if any) from "GENERATOR"-worksheet 2, and populates results in "GENERATOR"-worksheet 3

The issue - the data I enter in "GENERATOR"-worksheet 1 will change based on Supplier, and the item codes in column "A" will have varying formats - with or without leading zeros/range in length from 2 digit codes to 14 digit codes.

with these 2 attachments - if i simply copy and paste data from Book 2 to Book 1/Sheet 1 - I only populate 46 matches
when I format column A in Book 1/Sheet 1 to "000000000000", I get 370 matches.

What I think might be the ultimate way to match is to:

1 - reformat book 1/sheet 1/ column A into a 14 digit "0" format
2 - after performing the trim on book 1/sheet 2 - also reformat into a 14 digit "0"
3 - try and find the match with all 14 digit "0" codes
4 - vlookup the match 14 digit "0" codes to pull original "trimmed" code from book 1/sheet 2 into sheet 3

i need to get the item codes back to original status, because I return all the data as a .txt file mass import back into my database.

I have already performed a match, but you can clear contents and test with both sheets at your leisure.

mrexcel

I hope this makes sense?