Question about Microsoft EXCEL 2004 for Mac

2 Answers


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?

Posted by on

2 Answers

  • Level 2:

    An expert who has achieved level 2 by getting 100 points


    An expert that gotĀ 5 achievements.


    An expert who has written 50 answers of more than 400 characters.


    An expert whose answer gotĀ voted for 20 times.

  • Expert
  • 150 Answers

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

Add Your Answer

0 characters

Uploading: 0%


Complete. Click "Add" to insert your video. Add


3 Points

Related Questions:

1 Answer

When i open an excel spreadsheet, the columns go right to left instead of left to right.

  1. Click Options on the Tools menu, and then click the International tab.
  2. Do one of the following in the Right-to-left option group.
    • Click Right-To-Left to have new objects displayed in a direction familiar to right-to-left users.
    • Click Left-To-Right to have new objects displayed in a direction familiar to left-to-right users.

Nov 10, 2013 | Microsoft Excel 2010

1 Answer

Y cant i write in any one of my coloms in ms excell

It is possible you have locked the spreadsheet. I would check the spreadsheet to see if you have locked or protected the rows or columns or, maybe even the entire spreadsheet.

May 17, 2011 | Microsoft Office Professional 2007 Full...

2 Answers

I need a running balance in a column for a petty cash spreadsheet

In the first row of numbers, assume row 3 for example (leaving 2 rows for titles, put in cell E3: =C3+D3. In the next row (assuming row 4, put in cell E4: =C4+D4+E3. Use fill down to populate this formula all the way down.

Jun 18, 2009 | Microsoft Excel for PC

1 Answer

Autofilter ceases to work on large Excel 2003 spreadsheet

Sounds like there is a gap between the filter heading and the column data. That's the only time I've seen suggested data missing from the drop-down.

Jun 16, 2009 | Microsoft Business & Productivity Software

2 Answers

Excel data is Delimited

Try opening the file from the Excel File/Open menu rather than double clicking to open it.  If that does not work try Importing from it into a new blank spreadsheet.

Jun 01, 2009 | Microsoft EXCEL 2004 for Mac

1 Answer

Squished Rows in Office 2003

Follow the next steps:
1.- Choose in the menu File.
2.- After Choose the option Sent to.
3.- After Choose the option recipient (attach data).

Nov 20, 2008 | Microsoft Office 2003 Basic Edition...

1 Answer


Are you looking to solve any particular problem?--- because there are a huge number of possible formulas in Excel.

However, in my opinion, the most commonly needed ones are addition, subtraction, division, multiplication, and summing.

Suppose you have the following numbers typed into your Excel spreadsheet:

columns: A B C D
1 20 3
2 10 4
3 15 2
4 1 2 3

Then suppose you type in the following formulas (in the D column):

columns: A B C D
1 20 3 =A1+B1
2 10 4 =A2-B2
3 15 2 =A3*B2
4 1 2 3 =sum(A4:C4)

Then the following answers will appear in the D column:

columns: A B C D
1 20 3 23
2 10 4 6
3 15 2 30
4 1 2 3 6

Sep 29, 2008 | Microsoft Business & Productivity Software

2 Answers

Should I use countif or if or what ??

hi this my id plz send excel material

Mar 25, 2008 | Microsoft Excel for PC

2 Answers

Duplicacy in excel sheet

Since you are searching the data by the phone number , first select all the data in the spreadsheet and sort it in ascending order by the phone number.
Then, assuming you have 5 columns of data A through E, and the phone numbers are in column E, with row 1 occupied by column headings, use the following formula in cell F2=IF(E2=E1,"Duplicate",1)

Drag this formula down column F till the end of your data
Select the entire data and do an auto filter
In column F filter the data by Duplicate and delete all these rows
What remains should be unique data

Dec 19, 2007 | Business & Productivity Software

1 Answer

Multiple letters in each cell, want to add the letters and tally how many of each

Use the =COUNTIF function

For example if column a has the letters in it:


This will count all of the letter 'a' s in
column a from row 1 to row 100.


Oct 31, 2007 | Microsoft Excel for PC

Not finding what you are looking for?
Microsoft EXCEL 2004 for Mac Logo

762 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers


Level 3 Expert

18259 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides