sorting question

[Peter]

Hello! I'm trying to sort a spreadsheet numerically. The column
I'm using for sorting contains either single numbers, or ranges of
numbers, as in these examples:

614
612-613

Excel wants to put 614 ahead of 612-613 (apparently assuming that a
7-character number is always greater than a 3-character number).

How can I sort this properly?

Thanks much!

 

[Jim Cone]

Excel classifies data as numbers or text.
614 is a number. 612-613 is text. Numbers sort before text.
You could format the column as Text -before- entering the data
and Excel should then sort the way you want.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


http://www.realezsites.com/bus/primitivesoftware
"Peter"
<[email protected]>
wrote in message
Hello! I'm trying to sort a spreadsheet numerically. The column
I'm using for sorting contains either single numbers, or ranges of
numbers, as in these examples:

614
612-613

Excel wants to put 614 ahead of 612-613 (apparently assuming that
7-character number is always greater than a 3-character number).
How can I sort this properly?
Thanks much!

 

[Peter]

Excel classifies data as numbers or text.
614 is a number. 612-613 is text. Numbers sort before text.
You could format the column as Text -before- entering the data
and Excel should then sort the way you want.

Thanks much - do you happen to know if there is a way to
intelligently cut and paste pre-existing data into a new spreadsheet
so that I can avoid re-entering all the data? I've tried Paste
Special/Values into a brand new column, pre-formatted as text, but
it doesn't work.

Thanks -

 

[Jim Cone]

You can sort it using an adjacent blank column.
Enter a formula in the new column. Something like...
= B5 & "- " and fill down.
This forces all data in the new column to text.
Paste values over the formulas and sort using the new column as the key.
Remove the new column.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Peter"
<[email protected]>
wrote in message

Excel classifies data as numbers or text.
614 is a number. 612-613 is text. Numbers sort before text.
You could format the column as Text -before- entering the data
and Excel should then sort the way you want.

Thanks much - do you happen to know if there is a way to
intelligently cut and paste pre-existing data into a new spreadsheet
so that I can avoid re-entering all the data? I've tried Paste
Special/Values into a brand new column, pre-formatted as text, but
it doesn't work.

Thanks -