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
Save hours of searching online or wasting money on unnecessary repairs by talking to a 6YA Expert who can help you resolve this issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
Here's a link to this great service
Posted on Jan 02, 2017
Tips for a great answer:
Jan 11, 2017 | Lotus Video Game Consoles & Games
May 06, 2016 | Casio Office Equipment & Supplies
Nov 10, 2013 | Microsoft Excel 2010
Start off by doing one of the following: Select the range of cells that you want to make into a table within your worksheet then proceed to Step 2 OR Start immediately with Step 2.
Click on the "Insert" tab at the top of the document.
Find the "Tables" group, then click on "Table." The "Create Table" dialog box will appear.
Type in a range for your table if you did not select a range in Step 1. If you did already select a range then that range will automatically appear in the dialog box.
Check the "My Table Has Headers" box if the data set you selected already contains the headers you want to use. If you don't check this box, the table will display default header names, which you can then go in and change.
Hit the "OK" button at the bottom of the Create Table dialog box to create your table.
Mar 18, 2011 | Computers & Internet
Jun 16, 2009 | Microsoft Computers & Internet
Sep 29, 2008 | Microsoft Computers & Internet
Dec 19, 2007 | Computers & Internet
776 people viewed this question
Usually answered in minutes!
Step 2: Please assign your manual to a product: