ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sorting Account Numbers (https://www.excelbanter.com/excel-worksheet-functions/31541-sorting-account-numbers.html)

Erika

Sorting Account Numbers
 
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.

Jim

Format the column with the account numbers as text then it will sort as you
desire.

"Erika" wrote in message
...
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.




Erika

Formatted everything to text and still got the same result.

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

"Jim" wrote:

Format the column with the account numbers as text then it will sort as you
desire.

"Erika" wrote in message
...
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.





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

"Erika" wrote in message
...
Formatted everything to text and still got the same result.

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

"Jim" wrote:

Format the column with the account numbers as text then it will sort as

you
desire.

"Erika" wrote in message
...
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.







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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Jim" wrote in message
...
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

"Erika" wrote in message
...
Formatted everything to text and still got the same result.

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

"Jim" wrote:

Format the column with the account numbers as text then it will sort

as
you
desire.

"Erika" wrote in message
...
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.








Erika

Thanks Andy - that worked great!!

"Andy Wiggins" wrote:

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
-

"Jim" wrote in message
...
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

"Erika" wrote in message
...
Formatted everything to text and still got the same result.

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

"Jim" wrote:

Format the column with the account numbers as text then it will sort

as
you
desire.

"Erika" wrote in message
...
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.










All times are GMT +1. The time now is 08:22 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com