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 more comments 
  • Kayw Dec 18, 2007

    Wow that was fast thank you. I appreciate the very clear explanation. I have no technical expertise so can you help me with a step-by-step guide. First in terms of your response - The file in which I will have to add the apostrophe to the number has about 7000 rows so what is the quick way to do this?

    The second part of my question relates to what i am looking to do with the data. These are Unique Identity Numbers stored:

    Workbook A has ID Numbers with the apostrophe
    Workbook B without the apostrophe.

    I have to identify which ID Numbers in Workbook A are also in Workbook B and i will then need to delete the matching numbers in Workbook B. File A has way more records than than file B. Thanks again for your help.

  • Kayw Dec 19, 2007

    Thank you very much. Let me see what I can do. Happy with VLOOKUP which is what I wanted to use but thought it might not be right.

    My last question then - if its easier to take the character out, how can I do that?

  • Kayw Dec 21, 2007

    Brilliant - thank you so much you have been very helpful. Hooray I have managed to strip out the apostrophe character

  • Kayw Dec 24, 2007

    I am back again with a new problem. I managed to strip the comma from all the ID Numbers. However my VLOOKUP formula won't work and it seems the reason is because no matter what I have tried, these ID Numbers are not converting to values. Even though the cell format appears to be General or Number, I think Excel is still reading the column as text (ID numbers remain stubbornly left justified) If it would help, I could mail a sample of the files to you? This data was pulled from SAP sent to me as an Excel file.



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

  • 1 more comment 
  • Greg Wright
    Greg Wright Dec 19, 2007

    You're welcome.

    To update the records with an apostrophe, use one of these options:

    1. One by one update each cell with the apostrophe (monotonous)

    2. Programming using Visual Basic (harder)

    3. Use a database with a string data type

    4. Use a third-party application that allows you to modify data easier (UltraEdit, in column mode or Search/Replace the "^P" with "^P'")
    It's easy to take a character out, but it's harder to add it. I use an application called UltraEdit and it allows me to modify data, prepend or postpend any characters and then put it back in Excel. That's beyond the scope of this question/solution. That starts to approach some contract work for hire.

    For the second questions if you are using Excel 2007, which I use, there is a button on Data Ribbon bar called "Remove Duplicates". This will help you.

    If you don't have Excel 2007, but Excel 2003, then you can use the VLOOKUP to see if the Lookup reference indicates if it exists, and then remove the cells that don't have a match.

  • Greg Wright
    Greg Wright Dec 21, 2007

    You can use the Find and Replace feature. Look for ' with the Options to look in selection or column only and then replace it with "" (nothing, empty string). Don't put anything in the replace box.

    It's easier, but you may not want to do this, because the values that have leading zeros, will disapper, if this doesn't matter, then you're that much closer to having them match.

  • Greg Wright
    Greg Wright Dec 27, 2007

    Make sure you have set both range columns to the Format of Numbers with 0 decimals. So you'll have to keep trying until you can set the columns this way until it works for you.



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


1 Suggested Answer

  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi there,
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

Good luck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%


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



Related Questions:

1 Answer

SpreadsheetHave old Lotus Symphony with over 50 friends on my Address List (9 columns of info on each).Can I transfer it to a simple address format to replace it? HELP (84 yr. old widow)

can you save as a csv file in the old lotus symphony ?
go to file
save as
type as
csv comma delimited

and this should be able to be imported on new spreadsheet program or computer .

Jan 11, 2017 | Lotus Video Game Consoles & Games

1 Answer

How to add comma in spreadsheet? thanks

To add commas to your spreadsheet you need the format the numbers. If you want your whole spreadsheet to use commas, click on the area to the left of column A and above number 1. This should highlight the whole spreadsheet. Click on the "," icon in the "number" area, and your spreadsheet will be formatted to put commas in.

Good luck,


May 06, 2016 | Casio Office Equipment & Supplies

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

2 Answers

How to make a 7 row and a 7 coloum in a spreadsheet

Creating a table in Microsoft Excel 2007 allows you to work with that data independently of the rest of the worksheet. First you must define your table, though, which you can do either from scratch or from data already in the worksheet. The process is a simple one. Read on to learn how to define a table in Excel 2007
  • 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.

  • Thanks for choosing Fixya............ Sandeep

    Mar 18, 2011 | Computers & Internet

    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 Computers & Internet

    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


    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 Computers & Internet

    1 Answer

    Works suite 2006 spreadsheet


    Mar 02, 2008 | Microsoft Windows XP Home Edition

    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 | Computers & Internet

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

    776 people viewed this question

    Ask a Question

    Usually answered in minutes!

    Top Microsoft Computers & Internet Experts

    micky dee

    Level 3 Expert

    3015 Answers

    Les Dickinson
    Les Dickinson

    Level 3 Expert

    18425 Answers

    Alun Cox

    Level 3 Expert

    2678 Answers

    Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

    Answer questions

    Manuals & User Guides