Hi ...,
Changing the format between number and text (either way)
has no effect until the data is reentered. You can effect
reentry with F2 followed by Enter, or you can use a macro.

One macro that you might find helpful along these lines is the
TrimALL macro. http://www.mvps.org/dmcritchie/excel/join.htm#trimall

The leading apostrophes when entering data are not part of the data,
but used to indicate a text value. If you see the apostrophe in the cell
then it is part of the data.

You can remove all apostrophes using Ctrl+H

Dates are numbers, you can change the format of a number
to another number format and the effect is immediate. A date
is the number of days past a given reference point for most
Excel users that would be the number of days past Dec 31, 1899
but with 1900 incorrectly treated as a leap year.

Some additional information on sorting can be found
in http://www.mvps.org/dmcritchie/excel/sorting.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message ps.com...
As has been noted many times, imported text often has apostrophes at
the beginning of each cell.

Firstly, how can one get rid of it? The Help menu says "uncheck
Transition Navigation Keys", but this is already unchecked and makes no
difference anyway. Doesn't anyone ever edit help menus? Was this
pasted in from the help menu of some other application?

In the end I saved as CSV, opened it again and saved as Excel without
the apostrophes, but opening CSV from Excel causes all kinds of
problems unless the whole thing is very simple (and has no dates).

Secondly, my problem arose when I wanted two columns of numbers sorted
in the same order, one in general format and one with apostrophes. It
didn't really matter which order as long as they were the same.

When I changed the format of the apostrophe fields to Number, they
remained untouched of course.

When I changed the format of the number fields to text, I expected them
to sort in alphabetical order, but they remained in numerical order.
How does one control the sort order in Excel? It isn't among the
options, like day of the week etc.

This is the opposite of another situation I had where dates kept
sorting in alphabetical order despite being in date format.