help with unusual sorting

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 with this for a week and its driving me crazy.

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.


howard11 wrote:
>
> 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 with
> this for a week and its driving me crazy.
>
> --
> howard11
> ------------------------------------------------------------------------
> howard11's Profile: http://www.excelforum.com/member.php...o&userid=26750
> View this thread: http://www.excelforum.com/showthread...hreadid=400038

--

Dave Peterson

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43130A4B.8A8D8441@verizonXSPAM.net...
> 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.
>
>
> howard11 wrote:
> >
> > 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 with
> > this for a week and its driving me crazy.
> >
> > --
> > howard11
> > ------------------------------------------------------------------------
> > howard11's Profile:
http://www.excelforum.com/member.php...o&userid=26750
> > View this thread:
http://www.excelforum.com/showthread...hreadid=400038
>
> --
>
> Dave Peterson

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"howard11" <howard11.1uj1ea_1125320703.8972@excelforum-nospam.com> wrote in
message news:howard11.1uj1ea_1125320703.8972@excelforum-nospam.com...
>
> 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 with
> this for a week and its driving me crazy.
>
>
> --
> howard11
> ------------------------------------------------------------------------
> howard11's Profile:
http://www.excelforum.com/member.php...o&userid=26750
> View this thread: http://www.excelforum.com/showthread...hreadid=400038
>