Strange PC apostophe in formula preventing Mac spreadsheet from sorting

[dhdtv]

Hi,

I have downloaded data in Excel format from a PC server onto my Mac OS
X 10.3.9. I have Excel 2001. The data in the formula bar of every
cell starts with an apostrophe ( ' ) before the text or numerals. I'm
trying to sort but the sort function seems to think the apostrophe is
part of the data and so numerial sorts end up looking like 1, 10, 100,
1000 instaed of 1, 2, 3, 4, etc.

Also, I have tried changing the cell format from general to currency in
a column of currency entries. However, the only way it works is if I
first delet the apostrophe. This is not an option as there must be
thousands of cells with these pesky apostrophes.

And also, whenever I do add a format such as left, right or center,
Excel will add an apostrophe to the begining of the data in the formula
bar.

Does anyone know about this issue and what can I do to get rid of these
apostrophes. The Find and Replace functions does not recognise the.

Thank you for your help.

David

 

[JE McGimpsey]

Does anyone know about this issue and what can I do to get rid of these
apostrophes. The Find and Replace functions does not recognise the.

The apostrophe is simply a flag which indicates that the value should be
interpreted as Text. It is not part of the value of the cell. It has
nothing to do with WinXL vs. MacXL - it's a feature of MacXL, too.

To convert the cell to actual numbers, you can, as you've found, delete
the apostrophe in the formula bar after ensuring that the cell has a
number format.

To do more than one cell at a time, copy an empty cell, select the cells
with "text numbers", choose Edit/Paste Special, and selecting the Values
and Add radio buttons. Click OK.

 

[dhdtv]

Thanks JE. You saved me a lot of stress! I appreciate you taking the
time to reply.

David