Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting an alphanumeric list - please someone help!
I posted this yesterday and got no responses. Is there someone who could
help me? _____________________________________________ would like to sort some text which is formatted in column one as: 1' 100 1000' 2 2' and so on. Any number that does not have an "'" at the end should come before any number that does and all the 1s, 2s, 3s, etc. should be grouped together. Is this possible? The result would look like: 1 100 1000' 2 2' Boy, would I appreciate some help |
#2
|
|||
|
|||
Joanne,
If your values start in cell A1, enter this formula in cell B1: =VALUE(LEFT(A1,1)) + IF(ISERROR(VALUE(A1)),VALUE(LEFT(A1,LEN(A1)-1))+50000,VALUE(A1))/100000 and copy down to match your data in column A. Then select columns A and B, and sort based on column B. Note: if your values can be higher than 50,000 you will need to change the 50,000 and 100,000 in the formula to a higher value, like 500,000 and 1,000,000 HTH, Bernie MS Excel MVP "Joanne" wrote in message ... I posted this yesterday and got no responses. Is there someone who could help me? _____________________________________________ would like to sort some text which is formatted in column one as: 1' 100 1000' 2 2' and so on. Any number that does not have an "'" at the end should come before any number that does and all the 1s, 2s, 3s, etc. should be grouped together. Is this possible? The result would look like: 1 100 1000' 2 2' Boy, would I appreciate some help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting alphanumeric | Excel Discussion (Misc queries) | |||
Sorting List of Numbers | Excel Discussion (Misc queries) | |||
Does font color affect sorting a list in Excel? | Excel Worksheet Functions | |||
Sorting for specific words/numbers in a list | Excel Worksheet Functions | |||
Sorting a list with subtotals | Excel Worksheet Functions |