Sorting Account Numbers

E

Erika

I have a listing of account numbers, for example

103, 104a, 103a, 104, etc.

How do I get them to sort 103, 103A, 103B, 104, 104A, 104B?

Currently it is sort all the numbers and then putting all of the numbers and
letters together at the end.
 
E

Erika

Formatted everything to text and still got the same result.

103, 104, 103a, 103b, 104a, 104b
 
J

Jim

I entered the number and number letter combinations in column A. I then
highlighted the column and selected Format, Cells. Then I hit the number tab
and selected Text and hit Enter. With the column still highlighted I
selected the drop down menu Data and selected Sort, Sort by Column A and
then Ascending, after hitting Enter the column sorted 103, 103a, 103b, 104,
104a and so on. This has worked for me. I am using Excel 2000, so I think
this is possible to do on all versions. Apart from this procedure I don't
know what to say.
Hope this helps.
Jim
 
A

Andy Wiggins

I tried this and got some interesting results.

By formatting a range and then inputting the data, it sorted as the OP
requires.
Inputting the data into an unformatted range (i.e. General) and then
formatting it as text, all the numbers sort to the top and those with
letters to the end.
Highlighting a number cell (still formatted as text) pressing F2, to edit,
and then Enter, without making changeds to the number, and then sorting, put
the number where the OP wanted it.
Performing a Search and Replace of 0 and then sorting ordered the list as
the OP requires, but that assumes a 0 in every cell.

In the end I used a formula.
Assume the list is in column A.
In column B (the cells are not formatted) put the formula =TEXT(A1,"#") and
copy it down.
Highlight the formulas and copy and paste the values over themseleves.
The result will sort as the OP requires, and the cells are still in General
format.
 
E

Erika

Thanks Andy - that worked great!!

Andy Wiggins said:
I tried this and got some interesting results.

By formatting a range and then inputting the data, it sorted as the OP
requires.
Inputting the data into an unformatted range (i.e. General) and then
formatting it as text, all the numbers sort to the top and those with
letters to the end.
Highlighting a number cell (still formatted as text) pressing F2, to edit,
and then Enter, without making changeds to the number, and then sorting, put
the number where the OP wanted it.
Performing a Search and Replace of 0 and then sorting ordered the list as
the OP requires, but that assumes a 0 in every cell.

In the end I used a formula.
Assume the list is in column A.
In column B (the cells are not formatted) put the formula =TEXT(A1,"#") and
copy it down.
Highlight the formulas and copy and paste the values over themseleves.
The result will sort as the OP requires, and the cells are still in General
format.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
Click to expand...
 
Top