Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Numbers that have Text
I need to sort like this
1 5 90 90a 90b 100 170a 170b instead of 1 5 90 100 90a 90b 170a 170b I found a message from 2006 that said to do this: "I'd convert the column to Text. (Data-Text to Columns...) Then, sort, making sure to check "Sort number and numbers stored as text separately." I did what was suggested and got this: 1 100 170a 170b 5 90 90a 90b So, how do I get it to sort the way I want? (the numbers go up into the 1000's) -- Ginger Kohler |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Numbers that have Text
On Thu, 3 Dec 2009 09:30:01 -0800, Ginger Kohler
wrote: I need to sort like this 1 5 90 90a 90b 100 170a 170b instead of 1 5 90 100 90a 90b 170a 170b I found a message from 2006 that said to do this: "I'd convert the column to Text. (Data-Text to Columns...) Then, sort, making sure to check "Sort number and numbers stored as text separately." I did what was suggested and got this: 1 100 170a 170b 5 90 90a 90b So, how do I get it to sort the way I want? (the numbers go up into the 1000's) You need to set up TWO helper columns The first column has just the numeric values The second column has just the alpha values Then you select all three columns Sort by column 2 then by column 3 Column 1 will wind up sorted as you describe. So, with your data A1:A8 B1: =LOOKUP(1E+307,--LEFT(A1,ROW(INDIRECT("1:10")))) C1: =SUBSTITUTE(A1,B1,"") Select B1:C1 and fill down to B8:C8 Select A1:C8 Data/Sort First by column B then by Column C Voila! --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Numbers that have Text
Say starting in A2 we have:
7 4 1a 5 7a 4b 3c 4a 4b 9b 4c 10c 3c 3c 5a 4b 2a 7 5b 10c 9a 10 4a 5a 10b 3b 4a 10 6c 6a 1b 5a 2a 9a 7b 3 1b 3c 10 In B2 enter: =IF(ISERROR(--RIGHT(A2,1)),--(LEFT(A2,LEN(A2)-1)),--A2) and copy down In C3 enter: =IF(ISERROR(--RIGHT(A2,1)),RIGHT(A2,1),"") and copy down We see: 7 7 4 4 1a 1 a 5 5 7a 7 a 4b 4 b 3c 3 c 4a 4 a 4b 4 b 9b 9 b 4c 4 c 10c 10 c 3c 3 c 3c 3 c 5a 5 a 4b 4 b 2a 2 a 7 7 5b 5 b 10c 10 c 9a 9 a 10 10 4a 4 a 5a 5 a 10b 10 b 3b 3 b 4a 4 a 10 10 6c 6 c 6a 6 a 1b 1 b 5a 5 a 2a 2 a 9a 9 a 7b 7 b 3 3 1b 1 b 3c 3 c 10 10 We have basically de-coupled the numbers and letters. First sort by column B and then by column C : 1a 1 a 1b 1 b 1b 1 b 2a 2 a 2a 2 a 3 3 3b 3 b 3c 3 c 3c 3 c 3c 3 c 3c 3 c 4 4 4a 4 a 4a 4 a 4a 4 a 4b 4 b 4b 4 b 4b 4 b 4c 4 c 5 5 5a 5 a 5a 5 a 5a 5 a 5b 5 b 6a 6 a 6c 6 c 7 7 7 7 7a 7 a 7b 7 b 9a 9 a 9a 9 a 9b 9 b 10 10 10 10 10 10 10b 10 b 10c 10 c 10c 10 c -- Gary''s Student - gsnu200909 "Ginger Kohler" wrote: I need to sort like this 1 5 90 90a 90b 100 170a 170b instead of 1 5 90 100 90a 90b 170a 170b I found a message from 2006 that said to do this: "I'd convert the column to Text. (Data-Text to Columns...) Then, sort, making sure to check "Sort number and numbers stored as text separately." I did what was suggested and got this: 1 100 170a 170b 5 90 90a 90b So, how do I get it to sort the way I want? (the numbers go up into the 1000's) -- Ginger Kohler |
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 text with numbers | Excel Worksheet Functions | |||
Sorting numbers with text | New Users to Excel | |||
Help sorting text as numbers | Excel Worksheet Functions | |||
SORTING TEXT AND NUMBERS | Excel Worksheet Functions |