Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting Numbers | Excel Discussion (Misc queries) | |||
macro to eliminate repeating account numbers | New Users to Excel | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |