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.

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.

David McRitchie wrote:
Hi ...,
Changing the format between number and text (either way)
has no effect until the data is reentered.


Thanks; I didn't realise that. If that's how it works, it could
explain a lot of problems. Especially given the apparent inconsistency
whereby you can change the number format of existing data.

How can one tell the difference between a cell whose format shows as eg
"text" because it was entered that way, and one which shows the same
format which wasn't applied because the contents were already there?
In either case, you go to look at the format and it will show "text".

What means can one use of reentering the data when it's large?
Presumably cutting and pasting doesn't work? Is there an option
somewhere that says "reformat existing cell contents on changing cell
format"?


You can effect
reentry with F2 followed by Enter, or you can use a macro.


Yes, this is fine one at a time, but not for a large column, and it
also risks mistakes (eg deleting more than one character).



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


Since then I've found that if you make a new column and do a Paste
Special and choose Values, it will paste the contents without the
apostrophe. However, it doesn't let you change the format, even if the
column you paste into is already formatted as Number.


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


This was the first thing I tried, but it said it couldn't find matching
data. Is there some kind of code you can put in the search field to
indicate a special character? Or I think you mean only when the
apostrophe isn't a leading hidden one?




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.


This was what I would have expected, but perhaps my problems are
explained by the earlier point that despite changing the whole column
format to Date, the text that was already there was still sorting
alphabetically instead of numerically because you can't change the
format of existing contents.



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


Thanks for all the references.

Hi ...,

There are a few other points; hopefully you'll resolve them
as you proceed. But to check if a value is currently
treated (still treated) as a number or as text.
=ISTEXT(A1)
=ISNUMBER(A1)
there is further discussion on that at and above the
link to the TrimALL macro. (I think you skipped looking there)
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
and in
Determine if cell is Number or Text and why is it seen that way (#debugformat)
http://www.mvps.org/dmcritchie/excel...tm#debugformat

pasting values will, of course, lose formulas.

Another page if formulas are involved is
http://www.mvps.org/dmcritchie/excel/formula.htm

Please use your name in the Microsoft Excel newsgroups.

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

David McRitchie wrote:
Hi ...,
Changing the format between number and text (either way)
has no effect until the data is reentered.


Thanks; I didn't realise that. If that's how it works, it could
explain a lot of problems. Especially given the apparent inconsistency
whereby you can change the number format of existing data.

How can one tell the difference between a cell whose format shows as eg
"text" because it was entered that way, and one which shows the same
format which wasn't applied because the contents were already there?
In either case, you go to look at the format and it will show "text".

What means can one use of reentering the data when it's large?
Presumably cutting and pasting doesn't work? Is there an option
somewhere that says "reformat existing cell contents on changing cell
format"?


You can effect
reentry with F2 followed by Enter, or you can use a macro.


Yes, this is fine one at a time, but not for a large column, and it
also risks mistakes (eg deleting more than one character).



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


Since then I've found that if you make a new column and do a Paste
Special and choose Values, it will paste the contents without the
apostrophe. However, it doesn't let you change the format, even if the
column you paste into is already formatted as Number.


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


This was the first thing I tried, but it said it couldn't find matching
data. Is there some kind of code you can put in the search field to
indicate a special character? Or I think you mean only when the
apostrophe isn't a leading hidden one?




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.


This was what I would have expected, but perhaps my problems are
explained by the earlier point that despite changing the whole column
format to Date, the text that was already there was still sorting
alphabetically instead of numerically because you can't change the
format of existing contents.



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


Thanks for all the references.