Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort hyperlinks by time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort hyperlinks by time
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort hyperlinks by time
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort hyperlinks by time
Hi Thanks for that Jim.
Ta, Martin "Jim Cone" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks change during sort | Excel Discussion (Misc queries) | |||
Locking a spreadsheet and allowing hyperlinks and sort | New Users to Excel | |||
Excel losing hyperlinks when I sort the worksheet | Links and Linking in Excel | |||
Auto-Sort Hyperlinks | Excel Discussion (Misc queries) | |||
How do I sort rows with hyperlinks? | Excel Discussion (Misc queries) |