Question about Microsoft EXCEL 2004 for Mac

I have been sent a spreadsheet that has a column of numbers that looks as follows;

'4605100080076

'3504110060010

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?

Re: HOW TO STRIP DELIMITED CHARACTERS FROM SPREADSHEET

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

Re: HOW TO STRIP DELIMITED CHARACTERS FROM SPREADSHEET

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

- Click
**Options**on the**Tools**menu, and then click the**International**tab. - 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

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...

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

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

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

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).

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...

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**

**rows**

**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**

**rows**

**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**

**rows**

**1 ** 20 3 23

**2 ** 10 4 6

**3** 15 2 30

**4** 1 2 3 6

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:

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

Then the following answers will appear in the D column:

Sep 29, 2008 | Microsoft Business & Productivity Software

hi this my id :dadu_mf@rediff.com plz send excel material

Mar 25, 2008 | Microsoft Excel for PC

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

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

Use the =COUNTIF function

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

For example if column a has the letters in it:

=COUNTIF(a1:a100,"a")

or

@COUNTIF(a1..a100,"a")

This will count all of the letter 'a' s in

column a from row 1 to row 100.

Mike

Oct 31, 2007 | Microsoft Excel for PC

762 people viewed this question

Usually answered in minutes!

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.

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?

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

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.

×