Sort Not Working on Column of Numbers

[therube]

Below is the outcome of my sort when the cells were formatted as number

These are currency values. I copied and pasted the information from a
website into Excel and all cells were formatted as "General" when I checked
my spreadsheet. I have tried to change the cell formatting to everything
imaginable, currency, numbers, removed commas, cleared $ sign formats etc yet
it appears that Excel is treating these numbers as text.

The help section said to change the cell format to number from text. Yet the
data was formatted as "general".

It seems to be sorting from the left rather then by total value.

99198
9500
94012
92661
9175
9172
88933
86999
86250
8620
86154
8400
82335
81678
795888
78689
765552

Thanks for your help.

 

[bapeltzer]

The format is affecting how the data is shown, not how it's stored or
evaluated. To check whether a column is truly numeric, I usually just
highlight it and click on the 'comma style' button. If it's numeric, you'll
see the comma delimiter and probably a decimal point with a couple digitis
following. (After checking, click 'undo').
To convert text to numbers, click in a blank cell. Ctrl-C to copy.
Highlight your column to be converted. Right-click, select Paste Special,
select Values and click OK. Now your text should be coverted to numeric
values and the sort should work as you're expecting.

 

[Ashish Mathur]

Hi,

To convert the entries to figures, highlight the range and go to Data > Text
tot columns and then click on Finish. This will convert all the entries to
numbers.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

HI,

The numbers are really text as everyone is telling you.

If you are using 2002-3 then there should be a green triangle at the top
right of the cells. Highlight the range and put your mouse over the green
error checking triangle and not to popup icon to the left, put your mouse
over it and click it to see a list of options, choose Convert to Number.

Unfortunately, Paste Special Values will not do it. Here the secret is to
select an empty cell and copy it, then select all the text numbers and choose
Edit, Paste Special, Add. This command is ~100 times faster than the Convert
to numbers command. So if you have a large amount of data if would be the
better of the two. Also, the first option is not available in earlier
versions of Excel but this one has always been around.

The Text to Columns command is also a good choice.

 

[bapeltzer]

Thanks Shane; you're correct, I meant to suggest Paste Special > Add. Paste
Special > Values was wrong.