How to work with leading zeros and vlookup

[zombiemaster]

Help needed...again...

I have a sheet that has 5-digit number codes in column B. I am trying to use a vlookup in column A to pull in names from a named table "CP_NAMES" in another workbook "Freeze Code Template".

Column B sometimes has leading zeros (1 or 2), which makes the vlookup give me an #N/A error.

Here's an example of what happens:

My code does a ton of formatting, inserting columns, deleting garbage data, etc.
Right before the vlookup happens, column B is formatted as General, the leading zeros are visible and the column is left-justified.
When I insert the vlookup into column A, all I get are #N/A errors.
​

I found code online to fix that, and it works great for everything except the leading zero rows:

    Sheets("WORKING-REPS").Select
    Columns("B:B").Select
    Selection.texttocolumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

    Range("A4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[1],'FREEZE CODE TEMPLATE.xltm'!CP_NAMES[#Data],2,FALSE)"
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A4:A" + endRow$)

After i run that code, the vlookup updates column A to show the names correctly (except for the leading zero rows). Also, column B is now right-justified and the leading zeros are no longer visible.

(FYI - I mark the endRow$ earlier in the code and use it repeatedly throughout the module. I know there are other ways of marking the end row but I'm accustomed to doing it this way...)

So what I am looking for is an adjustment to the code above that will allow the leading zeros in column B to remain visible AND be able to be included for the vlookup formula to pull data from.

Thanks as always,
~ZM~

 

[Reggie Perrin]

Have you tried using a custom number format for the leading 0's data? Or adding an apostrophe so they're treated as text?

 

[zombiemaster]

Thanks for the thought, Reggie - and I did try the apostrophe but the vlookup did not recognize it. I looked through the list of custom formats but don't see anything useful there...

UPDATE: since it's a five-digit number, I tried setting it as ZIP CODE thinking that might be the trick...and it did make it so the leading zeros are visible, but the vlookup still won't work for those rows.

~ZM~

 

[zombiemaster]

Ok, so after changing the formatting of my table to General and also formatting my column B to General to do the vlookup, it works...mostly... I can't see the leading zeros but hopefully management won't notice that part and I can get away with it for now.

If anyone has any solution for this so I can retain the leading zeros AND get the vlookup to work, I'm all ears...

Thanks!
~ZM~

 

[Reggie Perrin]

You can create your own customer number format. When you're in the custom format area, where it says Type: you can use 000##### or however many leading zeros you want.