On Sat, 6 Dec 2008 13:41:01 -0800, Quin wrote:

I thought I understood most aspects of formatting numbers and text in Excel
until this came up...

I have a spread sheet with numbers. I select the numbers and then right
click to Format Cells and I select currency. The spread sheet seems to
ignore the currency formatting and does not put in the dollar signs. What is
worse than that is the column of numbers will not sort correctly. If I sort
it gives me an incorrect sort order like:
119.42
12.62
120.38
122.43
Any idea what is going on?

Quin

Your values were either
1. Entered as text
2. Entered in a column which had been pre-formatted as text
3. Imported from the web and have a trailing nbsp.

To confirm, try this formula:

=ISTEXT(cell_ref) where cell_ref is a cell containing one of these values that
won't convert.

To convert, first try:

Select empty cell
Edit/Copy

Select column of values
Edit/Paste Special/Add

If that does not work, then enter this formula in an adjacent cell:

=--TRIM(SUBSTITUTE(cell_ref,CHAR(160),""))

and fill down as far as required.

Then

Edit/Cut
select original data (after making a backup)
Edit/Paste Special/Values

--ron