Sorting Wrong

[Sam Weber]

Greetings,

My Excel worksheet is sorting numbers wrong. It's doing
this:

94
92
9
9
9
84
84
8
8
8
76
72
71
7
7

Is there anyway to get it to sort normally? Like:

94
92
84
76
72
71
9
9
9
8
8
8
7
7

 

[Lady Layla]

Make the cells formatted as number with 0 decimals




: Greetings,
:
: My Excel worksheet is sorting numbers wrong. It's doing
: this:
:
: 94
: 92
: 9
: 9
: 9
: 84
: 84
: 8
: 8
: 8
: 76
: 72
: 71
: 7
: 7
:
: Is there anyway to get it to sort normally? Like:
:
: 94
: 92
: 84
: 76
: 72
: 71
: 9
: 9
: 9
: 8
: 8
: 8
: 7
: 7
:

 

[Norman Harker]

Hi Sam!

Your numbers are formatted as text so you need to convert to text.

Select an empty cell
Copy
Select text numbers
Edit > Paste Special > Add
OK

Now try sorting.

 

[Guest]

It's still sorting wrong even when I did that.

 

[CLR]

Everybody is giving you the straight scoop........your cells ARE formatted
as TEXT and need to be converted to numbers.........only problem is, some
times Excel will allow that to happen one way, and sometimes
not.........you're dealing with a "not" <G>.......here's another way to try.

Use a helper column, say your data is in column A, but this in B1 and copy
down.;.

=B1+0

Then you can select column B and do Copy > Paste special > values .....to
eliminate the formulas...........then you can delete the old data in column
A....

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Sorry..........formula should have been =A1+0...........my bad

Vaya con Dios,
Chuck CABGx3

 

[Lady Layla]

Or Copy a blank cell (formatted as General)
Select Area with numbers, Edit Paste Special, Add

: Everybody is giving you the straight scoop........your cells ARE formatted
: as TEXT and need to be converted to numbers.........only problem is, some
: times Excel will allow that to happen one way, and sometimes
: not.........you're dealing with a "not" <G>.......here's another way to try.
:
: Use a helper column, say your data is in column A, but this in B1 and copy
: down.;.
:
: =B1+0
:
: Then you can select column B and do Copy > Paste special > values .....to
: eliminate the formulas...........then you can delete the old data in column
: A....
:
: Vaya con Dios,
: Chuck, CABGx3
:
:
:
: : > Greetings,
: >
: > My Excel worksheet is sorting numbers wrong. It's doing
: > this:
: >
: > 94
: > 92
: > 9
: > 9
: > 9
: > 84
: > 84
: > 8
: > 8
: > 8
: > 76
: > 72
: > 71
: > 7
: > 7
: >
: > Is there anyway to get it to sort normally? Like:
: >
: > 94
: > 92
: > 84
: > 76
: > 72
: > 71
: > 9
: > 9
: > 9
: > 8
: > 8
: > 8
: > 7
: > 7
: >
:
:

 

[Norman Harker]

Hi Lady Layla!

As a minor point. There's no need for the blank cell to be formatted as
General. Even if the blank cell is formatted as text the Copy and Paste
Special Add will still convert text numbers to numbers.

--

 

[Lady Layla]

Thanks Norman. Was not sure about that as I had seen the General format note on
a couple of other suggestions over the years


: Hi Lady Layla!
:
: As a minor point. There's no need for the blank cell to be formatted as
: General. Even if the blank cell is formatted as text the Copy and Paste
: Special Add will still convert text numbers to numbers.
:
: --
: --
: Regards
: Norman Harker MVP (Excel)
: Sydney, Australia
: (e-mail address removed)
:
:

 

[Norman Harker]

Hi Lady Layla!

I wasn't sure either so I did a test. It follows the standard approach
of Excel. If you pre-format two cells as text, and then use a formula
to add them, Excel will coerce the text numbers to numbers and resolve
the formula.

 

[Spike9458]

I like the way you copy/paste special/add.

I've been using another work around by inserting a 'helper' column beside
the column needing conversion. For example, column A needs conversion,
helper column is B. Put the formula =VALUE(a1) in cell B1, and copy down.
Then I would select the newly converted column, copy it, and paste
special/values right over the original column, then delete the helper
column. It's laborious compared to copy/paste special/add, but it works.

--Jim