| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting by only numbers
How to sort ignoring letters, Excel 2003
ny only containing numbers, For example default sorting wanted sorting AA101 AC02 AB99 AB99 AC02 AA101 Thanks George |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting by only numbers
You'll have to separate the numbers into a different column.
If all the letters are exactly two digits, you could use: =--mid(a1,3,255) (the -- converts the text that =mid() produces to a real number.) Then you can sort your data by using this helper column. George wrote: How to sort ignoring letters, Excel 2003 ny only containing numbers, For example default sorting wanted sorting AA101 AC02 AB99 AB99 AC02 AA101 Thanks George -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting by only numbers
Thank you for your response,
Unfortunately the letters ranging from 2 to 8 Approx, Is there a macro for it? "Dave Peterson" wrote in message ... You'll have to separate the numbers into a different column. If all the letters are exactly two digits, you could use: =--mid(a1,3,255) (the -- converts the text that =mid() produces to a real number.) Then you can sort your data by using this helper column. George wrote: How to sort ignoring letters, Excel 2003 ny only containing numbers, For example default sorting wanted sorting AA101 AC02 AB99 AB99 AC02 AA101 Thanks George -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting by only numbers
Thank you for your response,
Unfortunately the letters ranging from 2 to 8 Approx, Is there a macro for it? "Dave Peterson" wrote in message ... You'll have to separate the numbers into a different column. If all the letters are exactly two digits, you could use: =--mid(a1,3,255) (the -- converts the text that =mid() produces to a real number.) Then you can sort your data by using this helper column. George wrote: How to sort ignoring letters, Excel 2003 ny only containing numbers, For example default sorting wanted sorting AA101 AC02 AB99 AB99 AC02 AA101 Thanks George -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting by only numbers
You could use a formula like:
=--RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1 ) to get the rightmost group of digits. Dave Peterson wrote: You'll have to separate the numbers into a different column. If all the letters are exactly two digits, you could use: =--mid(a1,3,255) (the -- converts the text that =mid() produces to a real number.) Then you can sort your data by using this helper column. George wrote: How to sort ignoring letters, Excel 2003 ny only containing numbers, For example default sorting wanted sorting AA101 AC02 AB99 AB99 AC02 AA101 Thanks George -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting numbers and numbers that contain text in excel | Excel Discussion (Misc queries) | |||
| Sorting high numbers from low numbers between two rows | New Users to Excel | |||
| Sorting - cells containing numbers, numbers and letters | Excel Discussion (Misc queries) | |||
| Sorting numbers with differing numbers of digits | Excel Discussion (Misc queries) | |||
| Sorting texts and numbers as if it was all numbers | Excel Programming | |||
Linear Mode
