Sort hyperlinks by time

[Martin Wheeler]

xl2003, win xp

I have a column of hyperlinks that show the time but not the web address. In
addition to the time some cells show things like "4:21 F4" or "11:30 AM".
Others are just "4:00"

And this mucks up the sort order, with the cells with "F4" geting sorted
first. What I need is to filter out the "F4" so that the cells are sorted
by time only.

I have tried :- Range("J1:J57").Sort Key1:=Range("J1"),
Order1:=xlDescending, XlSortDataOption:=xlSortTextAsNumbers but it will not
compile.

Any help would be greatly appreciated.
Ta,
Martin

[Martin Wheeler]

Just tried it by ascending order and it does the times with no letters after
the time first, followed by the times with AM and then the times with F4.
I actually want the list in ascending order
Ta,
Martin

"Martin Wheeler" <[email protected]> wrote in message
news:%[email protected]...
> xl2003, win xp
>
> I have a column of hyperlinks that show the time but not the web address.
> In addition to the time some cells show things like "4:21 F4" or "11:30
> AM". Others are just "4:00"
>
> And this mucks up the sort order, with the cells with "F4" geting sorted
> first. What I need is to filter out the "F4" so that the cells are sorted
> by time only.
>
> I have tried :- Range("J1:J57").Sort Key1:=Range("J1"),
> Order1:=xlDescending, XlSortDataOption:=xlSortTextAsNumbers but it will
> not compile.
>
> Any help would be greatly appreciated.
> Ta,
> Martin
>

[Jim Cone]

Martin,

Depending on the exact nature of your data... In a helper column,
you could run a "Find" function to look for the first space.
Then convert the characters to the left of the space using the
"TimeValue" function and sort on the helper column.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Martin Wheeler" <[email protected]> wrote in message
xl2003, win xp

I have a column of hyperlinks that show the time but not the web address. In
addition to the time some cells show things like "4:21 F4" or "11:30 AM".
Others are just "4:00"

And this mucks up the sort order, with the cells with "F4" geting sorted
first. What I need is to filter out the "F4" so that the cells are sorted
by time only.

I have tried :- Range("J1:J57").Sort Key1:=Range("J1"),
Order1:=xlDescending, XlSortDataOption:=xlSortTextAsNumbers but it will not
compile.

Any help would be greatly appreciated.
Ta,
Martin

[Martin Wheeler]

Hi Thanks for that Jim.
Ta,
Martin


"Jim Cone" <[email protected]> wrote in message
news:%[email protected]...
> Martin,
>
> Depending on the exact nature of your data... In a helper column,
> you could run a "Find" function to look for the first space.
> Then convert the characters to the left of the space using the
> "TimeValue" function and sort on the helper column.
>
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
> "Martin Wheeler" <[email protected]> wrote in message
> xl2003, win xp
>
> I have a column of hyperlinks that show the time but not the web address.
> In
> addition to the time some cells show things like "4:21 F4" or "11:30 AM".
> Others are just "4:00"
>
> And this mucks up the sort order, with the cells with "F4" geting sorted
> first. What I need is to filter out the "F4" so that the cells are sorted
> by time only.
>
> I have tried :- Range("J1:J57").Sort Key1:=Range("J1"),
> Order1:=xlDescending, XlSortDataOption:=xlSortTextAsNumbers but it will
> not
> compile.
>
> Any help would be greatly appreciated.
> Ta,
> Martin
>
>