problem with vlookup removing leading 0's

[cbrf23]

I'm using VLOOKUP and I'm finding that within the function, excel is taking it upon itself to remove leading zeros. This is really annoying.

Also, I cant use the TEXT(number, "format") function, because the variety of numbers I need to lookup are NOT in the same format. \

For example, I need to use this formula to lookup the part numbers:
02-005-105-0000
27010-06
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=64>28207-030</TD></TR></TBODY></TABLE>

They need to be looked up exactly like that, because that is how they appear in the table. Excel changes them to:
02-005-0
27010-6
28207-30

Can anyone please help me figure out how to fix this behavior, because its holding up more people than just myself.

Thanks.

 

[cbrf23]

I ended up writing a custom lookup function in VBA because I needed to get the workbook I was working on finished.

I dont feel like this is the ideal solution, so if anyone knows a way to fix the problem with vlookup I would still love to hear it.

 

[shg]

Excel changes them to:
02-005-0
27010-6
28207-30

What leads you to believe Excel is doing that, unless you are catenating values from several cells in the lookup formula?

And it that's what you're doing, you could instead format the cells as text with leading zeros as appropriate.

 

[cbrf23]

Well, I'm using the vlookup function like so:

VLOOKUP($J$4,'mydatasheet!$B$4:$BB$63556,3,FALSE)

and that wasn't working...so I tried using the formula auditing tools to step through the code and debug, which made things really confusing because all the data appeared to be entering right, but still it wasnt finding the part number in my data range. (believe me, I checked a million times that the data was there, and even tried copying the cell contents and pasting them into J4 directly, but still was returning n/a)

So i decided to try typing the number in directly, to rule out any other possibilities and thats when I discovered that the leading zeros were being removed by excel. I typed:

VLOOKUP(02-005-105-0000,'mydatasheet!$B$4:$BB$63556,3,FALSE)

and when I hit enter, the cell contents was:

VLOOKUP(02-005-105-0,'mydatasheet!$B$4:$BB$63556,3,FALSE)

And I tried putting an apostrophe in front of 02-005-105-0000 and that generated an error. But no matter what I type in, excel automatically gets rid of those leading zeros.

My efforts searching google yielded not much info on this besides the fact that this its just a native behavior in excel, and that the work around is to use text(your string,"format") but like I pointed out above, I can't really make use of that because the values I need to lookup come in different formats.

My custom lookup works. But I was just wondering if there is a way to do this natively within excel, or to change the way excel handles leading zeros (some setting maybe?) because that would be a better solution for me.

I'd like to use a native function because of the little prompt that comes up and tells you what your supposed to be entering - like intellitype with vba - and that doesnt happen with a custom function, which makes things a little more difficult when other people need to modify the form.

I guess I can put some sort of instruction dialog box that comes up with an on open event, but I feel like that would be annoying...