how to sort anything that looks like a number as a number

[Guest]

I need to sort a large amount of data by a column that contains 3-character
numbers; some of the numbers are a mix of numbers and text (e.g. 16C) and
some of the numbers are all numbers (e.g. 151). I am using Excel 2003 on a
Windows XP computer. According to the Excel Help, I should be able to "sort
anything that looks like a number as a number" as long as I have formatted
all the numbers in the column to be text (which I have done). However, I
can't find a way to specify to sort numbers stored as text as numbers. I want
16C to show in the column after 151 but before 161, but when I select
Data/Sort... there is nothing that indicates which way I want to sort the
numbers. Instead, the numbers are sorted first by number, then by alpha (i.e.
16C shows up after 999). It is sorted the same way regardless of whether the
numbers are formatted as numbers or text. This is very frustrating because
the Help doesn't give me any information on how to force Excel to sort the
way I need it to sort.

I've read some of the recent posts about sorting numbers that include text,
but the solutions all seem to require an extra column with a long formula to
make Excel sort correctly. While I am familiar with formulas and have
successfully used them, I don't understand why this type of sorting would
require formulas when the Help documentation seems to indicate that Excel
will already sort the way I need. Is this a known glitch? Why would it be
mentioned in the Help but not actually perform the way it is documented?

Thanks for your help.

 

[Carim]

Hi Cyndi,

Since XL2002. possibility exists in Sort Options ...

Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortTextAsNumbers

HTH
Cheers
Carim

 

[Dave Peterson]

There's an option to sort Text as numbers.

But you want to sort numbers as text. That's different.

I would use a helper column with a formula like:

=a1&""
or
=text(a1,"000")

to convert the numbers to text.

Then sort by that helper column.

 

[Guest]

Hi, Carim:

I can't find the Sort Options that you list below. How do I get to it? When
I click on Data/Sort... then the Sort box comes up. I need to sort by three
criteria, with the third being to sort by the column that contain the numbers
stored as text. I select the criteria and sort orders, then when I click on
the Options button the only choice showing for the First key sort order is
"Normal" (the other sort orders are for days of the week and months). There
is an option for Case sensitive, which I leave unchecked. For Orientation I
select top to bottom. There are no other options available. How do I get to
the options you suggested? Thanks for your patience and help.

 

[Carim]

Cyndi,

Take a look at the following post :

http://www.ozgrid.com/forum/showthread.php?t=22170&highlight=sort+text+numbers

HTH
Cheers
Carim