help with unusual sorting

H

howard11

Does any one know how to get Excel to sort a column such as this:

O:6.1/0
O:6.1/1
O:6.1/10
O:6.1/11
O:6.1/12
O:6.1/13
O:6.1/14
O:6.1/15
O:6.1/2
O:6.1/3
O:6.1/4
O:6.1/5
O:6.1/6
O:6.1/7
O:6.1/8
O:6.1/9
O:6.11/0
O:6.11/1
O:6.11/2
O:6.11/5
O:6.11/6
O:6.12
O:6.2/0
O:6.2/1
O:6.2/2
O:6.2/3
O:6.2/4
O:6.2/5
O:6.2/6
O:6.2/7
O:6.7/0
O:6.7/2

in numerical order by the digit after the 6? I've been playing wit
this for a week and its driving me crazy
 
D

Dave Peterson

I would use a few helper columns.

I'd copy the original column to another column.

Then select that column and do
edit|replace
what: : (colon)
with: | (vertical bar)
replace all

followed by
edit|replace
what: . (dot)
with: | (vertical bar)
replace all

followed by
edit|replace
what: / (slash)
with: | (vertical bar)
replace all

Then insert 4 more columns to the right of that helper column

Then with that helper column still selected, I'd do
data|text to columns
delimited by |

Now all the values are in their own column.

Select the whole range and sort by the column that you want.
 
P

Paul Sheppard

howard11 said:
Does any one know how to get Excel to sort a column such as this:

O:6.1/0
O:6.1/1
O:6.1/10
O:6.1/11
O:6.1/12
O:6.1/13
O:6.1/14
O:6.1/15
O:6.1/2
O:6.1/3
O:6.1/4
O:6.1/5
O:6.1/6
O:6.1/7
O:6.1/8
O:6.1/9
O:6.11/0
O:6.11/1
O:6.11/2
O:6.11/5
O:6.11/6
O:6.12
O:6.2/0
O:6.2/1
O:6.2/2
O:6.2/3
O:6.2/4
O:6.2/5
O:6.2/6
O:6.2/7
O:6.7/0
O:6.7/2

in numerical order by the digit after the 6? I've been playing wit
this for a week and its driving me crazy.
Click to expand...

Assuming your data to be in columnA,

In ColumnB put this formula: =MID(A1,(SEARCH("/",A1)-3),3)
In ColumnC put this formula: =MID(A1,(SEARCH("/",A1)+1),2)

Then do your sort on ColumnB as the first sort and ColumnC as th
second

Once in the right order you can then delete B&
 
T

Tom Ogilvy

Slightly fewer steps: (if you mean the digits after the six and before the
/)

Edit|Replace
what / (right slash)
with , (comma)

Select the column with the copy of your original and do
Data=>Text to columns

select delimited

select comma and other
in the other box, put a period (.)

sort on the on the center column.
 
R

Ragdyer

Since you want the digit *after* the 6 to be the sort key, and it's
separated from the other characters by a decimal (dot, period), just use TTC
(Text to Columns) to create a *new*, "helper" column, without changing the
original column, and then select both columns, and use the "helper" as the
sort key, and then throw it (helper) away when you're finished.

Select the original column and then:
<Data> <TextToColumns>
<Delimited> <Next>

Under "Delimiters", check "Other", and enter a period in the box,
In the "Data Preview" window, you'll see that the data is separated as you
wanted.
<Next>

In the "Data Preview" window, you'll see that the first column is *selected*
and has a heading of General.
While it's still selected, click on "Do Not Import (skip)"
You'll now see the heading change to "Skip".

Now click in the second column to select it.
Under "Column Data Format", click in "Text".
You'll see the heading change for the second column.

Now, to make sure that you're original column remains intact, you must
change the "Destination" of this second column.
The default destination location is the location of the original column,
meaning it would be replaced with this revised, second column.
Just change that to the column *next* to your original, either to the left
or the right, since you'll want them together for the final sort.
Don't forget, this destination location will *replace* whatever is in that
column, so make sure you have an empty column, left or right, ready to
receive this data.

Now, <Finish>

All you have to do now is select both columns and sort using the helper
column as the sort key.
 
Top