
Apostrophe when exporting to Excel 2000
Bob Yarnall May 15, 2014 9:54 AM (in response to GrantRoss _)I’m curious as to why you need the leading zeros?
If the master lookup range is truly alpha then the apostrophe is needed to make it work. If the lookup is numeric, then the leading zeros are meaningless. Is the lookup range simply formatted to show leading zeros but is actually numeric? This is what it sounds like. Have you tried exporting the data as numeric and then using the lookup?

Apostrophe when exporting to Excel 2000
GrantRoss _ May 15, 2014 9:54 AM (in response to GrantRoss _)The first set of numbers is the account group (xxxx) and the second set of numbers (xxx) is the account sub group. The master lookup range is truely alpha. It is defined as alpha via Excel properties, not by using an apostrophe before the value. I have tried exporting the values as numeric but the master lookup values are character with the leading zero and no apostrophe, so it would not work either.
Originally posted by Bob Yarnall:
I’m curious as to why you need the leading zeros?
If the master lookup range is truly alpha then the apostrophe is needed to make it work. If the lookup is numeric, then the leading zeros are meaningless. Is the lookup range simply formatted to show leading zeros but is actually numeric? This is what it sounds like. Have you tried exporting the data as numeric and then using the lookup? /b[/quote]

Apostrophe when exporting to Excel 2000
Nick OsdalePopa May 15, 2014 9:54 AM (in response to GrantRoss _)Have you tried changing your formula to something like:
=VLOOKUP("'"&B1,LookupRange,2,False)?
Using the appropriate info of course.
EDIT:
Curious...I just did a quick test myself and did not seem to have any problems. I'm using Excel97 and did an export of numbers with leading zeros and they do have the apostrophe in front of them. Then I formated a cell as text, entered a number, and did a VLOOKUP (without the append) and it worked fine. How do you have you number set up again for the lookup value?
[size="1"][ March 19, 2003, 01:46 PM: Message edited by: Nick OsdalePopa ][/size]

Apostrophe when exporting to Excel 2000
Grant Perkins May 15, 2014 9:54 AM (in response to GrantRoss _)Just completed a quick check myself  using Excel 2000 on XP.
VLOOKUP was fine. If I edit the entries (any combination) and remove or include the leading apostrophe it makes no difference to Excel as far as I can see.
However I am checking text against text and assume that text against number or vice versa might be problematic. But then I don't think there is a way of formatting leading zeros into the number cell is there? (Maybe there is some deep part of Excel I have never found).
I'm as puzzled as Nick at the moment.
Grant
Originally posted by Nick OsdalePopa:
EDIT:
Curious...I just did a quick test myself and did not seem to have any problems. I'm using Excel97 and did an export of numbers with leading zeros and they do have the apostrophe in front of them. Then I formated a cell as text, entered a number, and did a VLOOKUP (without the append) and it worked fine. How do you have you number set up again for the lookup value? /b[/quote]

Apostrophe when exporting to Excel 2000
Tom Whiteside May 15, 2014 9:54 AM (in response to GrantRoss _)Nick's solution seems excellent   as always   but in this situation I would be lazy and simply do a global search and replace with your new Excel file. That is, search for all apostrophes ("'") and replace with (""), effectively stripping them out. This could be a solution for both GrantRoss and ttom, whose March 11th post dealt with the same problem. My own suggestion for ttom was not effective.
For Bob Yarnall's question, Excel will let you format a numberfilled cell as text without using a leading apostrophe. This can definitely cause problems when trying to match with a text cell that has the apostrophe. Most finance and inventory applications have leading zero part or account numbers that have to have text formatting.
Grant (R, not P), Excel's VLOOKUP is so painfully sensitive to tiny shades of difference in cell format that I often wind up reformatting my source and target lookup cells to General format and then applying desired formats until they match. I've just switched from Excel 97 to Excel 2000 and sincerely hope that things have gotten better. Keep us apprised of your situation.
Now, unless someone comes up with a Monarch exporting trick (waiting?), we can rest easy that we're not dealing with a Datawatch problem!

Apostrophe when exporting to Excel 2000
Nick OsdalePopa May 15, 2014 9:54 AM (in response to GrantRoss _)Ok, thanks for more insight into this Grant & Tom.
You can format a numeric cell with 000 and all single digits and double digit numbers entered will have 1  2 zero's leading the number. Also you can format a text cell with "0"@ and anything you enter in the cell will have a leading zero.
Now this is where GrantRoss may be experiencing problems, but I may be wrong.
Those formats are DISPLAY formats only and have nothing to do with the underlying value.
Thus if a number in the text cell example I gave is 5, it will display as 05, and if you compare that to a text list of numbers that has a leading zeros, it will be trying to match the number 5 to the list and not 05.
I'd be interested in knowing how his cell is formatted that he is doing the VLOOKUP formula against.
Hope that makes sense.
[size="1"][ March 19, 2003, 06:07 PM: Message edited by: Nick OsdalePopa ][/size]

Apostrophe when exporting to Excel 2000
RGBrooks _ May 15, 2014 9:54 AM (in response to GrantRoss _)Extract the field as a numeric field and add the leading zero's in Excel. That will keep the Excel properties as numeric and you shouldn't have any problems between text and numeric lookups.
To add the leading zeros within an excel spreadsheet: highlight the cells where you need the leading zero's and go to format>cell>format>custom; then under "type:" enter the number zero for the total places of numbers including leading zero's.
Example if you want the number to display 00215, you would enter 00000. That will put in the leading zero's and keep the cell numeric. Hope this helps.