Number representation by Excel

I'm building a VBA program on Excel 2007 inputing long string of numbers (UPC). Now, the program usually works fine, but sometimes the number string seems to be converted to scientific notation and I want to avoid this, since I then VLook them up.

So, I'd like to treat a textbox input as an exact s开发者_运维技巧tring. No scientific notation, no number interpretation.

On a related side, this one really gets weird. I have two exact UPC : both yield the same value (as far as I or any text editor can tell), yet one of the value gives a successful Vlookup, the other does not.

Anybody has suggestions on this one? Thanks for your time.

Long strings that look like numbers can be a pain in Excel. If you're not doing any math on the "number", it should really be treated as text. As you've discovered, when you want to force Excel to treat something as a string, precede it with an apostrophe.

There are a couple of common problems with VLOOKUP. The one you found, extra whitespace, can be avoided by using a formula such as

=VLOOKUP(TRIM(A1),B1:C:100,2,FALSE)

The TRIM function will remove those extraneous spaces. The other common problem with VLOOKUP is that one argument is a string and the other is a number. I run into this one a lot with imported data. You can use the TEXT function to do the VLOOKUP without having to change the raw data

=VLOOKUP(TEXT(A1,"00000"),B1:C100,2,FALSE)

will convert A1 to a five digit string before it tries to look it up in column B. And, of course, if your data is a real mess, you may need

=VLOOKUP(TEXT(TRIM(A1),"00000"),B1:C100,2,FALSE)