Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup text values
Hi,
I am having a difficulty that has been bugging me for some time. I have 14 digits SCC codes. Some start with 1-9, some start with 0's. Numbers stating with 0's have to be set up as text or the 0 disappears. Whenever I set up a number as text and I do a vlookup, those numbers are not being recognized as matching by the vlookup function. My question is: "how can I have a 14 digit number displaying zeros in front and still be able to compare it to another spreadsheet with the same number using the vlookup function. Please help. Mike |
#2
|
|||
|
|||
My suggestion: Format ALL of your SCC codes as "00000000000000", then you
won't have to worry about it. ******************* ~Anne Troy www.OfficeArticles.com wrote in message ups.com... Hi, I am having a difficulty that has been bugging me for some time. I have 14 digits SCC codes. Some start with 1-9, some start with 0's. Numbers stating with 0's have to be set up as text or the 0 disappears. Whenever I set up a number as text and I do a vlookup, those numbers are not being recognized as matching by the vlookup function. My question is: "how can I have a 14 digit number displaying zeros in front and still be able to compare it to another spreadsheet with the same number using the vlookup function. Please help. Mike |
#3
|
|||
|
|||
All of the data in your lookup array need to have the same format. Where do
these numbers originate? I would format the column as text before the data is inserted. Then the leading 0's will be preserved. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th Stockyards Hotel - Ft. Worth, Texas www.ExcelUserConference.com wrote in message ups.com... Hi, I am having a difficulty that has been bugging me for some time. I have 14 digits SCC codes. Some start with 1-9, some start with 0's. Numbers stating with 0's have to be set up as text or the 0 disappears. Whenever I set up a number as text and I do a vlookup, those numbers are not being recognized as matching by the vlookup function. My question is: "how can I have a 14 digit number displaying zeros in front and still be able to compare it to another spreadsheet with the same number using the vlookup function. Please help. Mike |
#4
|
|||
|
|||
You could try something like this:
=VLOOKUP(TEXT(A1,"00000000000000"),Sheet1!A1:B10,2 ,0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== wrote in message ups.com... Hi, I am having a difficulty that has been bugging me for some time. I have 14 digits SCC codes. Some start with 1-9, some start with 0's. Numbers stating with 0's have to be set up as text or the 0 disappears. Whenever I set up a number as text and I do a vlookup, those numbers are not being recognized as matching by the vlookup function. My question is: "how can I have a 14 digit number displaying zeros in front and still be able to compare it to another spreadsheet with the same number using the vlookup function. Please help. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
text and values combined in one cel | Excel Discussion (Misc queries) | |||
Lookup text values | Excel Worksheet Functions | |||
VLookup accesses half the text in a field? | Excel Worksheet Functions |