Question about Microsoft EXCEL 2004 for Mac
I have been sent a spreadsheet that has a column of numbers that looks as follows;
I need to be able to match this column with data in another workbook which has these numbers stored without the apostrophe character. My VLOOKUP formula won't work and I think its because of the delimited character. How do I fix this?
The short of it is this ...
Match the data in each location and store it the same way. As a number or as a string. I would say, looking at your data, that these are probably strings of numbers. Put the apostrophe in front of both pieces of data at both locations so they match. I tried a simple test in Excel and adding the apostrophe in front of all the uses of this data will match everything.
The long of it is this ...
Now, I don't know your technical expertise, but I'll try to put it simply. The hard part to understand in database and data storage, is that not all numbers are numbers.
For example, a phone number 8005551212 is not a number. It's a string of numbers. This is because every single position from the 8 to the 2 is a significant position. Another example is a a social security number. The arbitrary SSN of 002003421 is a string of numbers. The first two 0's will be stripped off in Excel if put in as a number, thus ALL number but be retained in their original significant position.
The apostrophe (added by the user) forces Excel interpret the cell data as a string, instead of number, preserving all the characters in their original form, and doesn't try and tinker with the formatting.
The other reason is that the data you've provided is WAY BIG for a number. Not a lot of systems in the world will do calculations against such big numbers. This is why I "guess" that they should be strings (adding the apostrophe to the front of the data in the cell).
How do you know if data should be stored as a string or as a number. The tall tale question is "will I be using these numbers in calculations." If you do, it's a number, if not, it's a string (add the apostrophe). A second question could be "do I need all positions to retain their original information" as a literal.
Please ask any questions.
Posted on Dec 18, 2007
HOORAY with your help and a bit of fiddling about, I have done the whole job. Thank you thank you thank you
Posted on Dec 27, 2007
Tips for a great answer:
Nov 10, 2013 | Microsoft Excel 2010
May 17, 2011 | Microsoft Office Professional 2007 Full...
Jun 18, 2009 | Microsoft Excel for PC
Jun 16, 2009 | Microsoft Business & Productivity Software
Sep 29, 2008 | Microsoft Business & Productivity Software
Dec 19, 2007 | Business & Productivity Software
Oct 31, 2007 | Microsoft Excel for PC
762 people viewed this question
Usually answered in minutes!
Level 3 Expert
Level 3 Expert
Level 3 Expert
Step 2: Please assign your manual to a product: