Can anyone help?

  • Thread starter Thread starter Gerald Oliver Swift
  • Start date Start date
G

Gerald Oliver Swift

Take a column of data. The data in about half the cells of that column are
all hyperlinks. The data in the rest of the column's cells are not
hyperlinks. And needless to say, all the data (cells) are jumbled up.

Is it possible to sort the data alphabetically so that all the
hyperlink-containing cells are grouped together in one block, followed by
another block of non-hyperlink data?

Any help would be much appreciated.

Gerry
 
My first impression is to add a helper column in which there is a formula -
=IF(ISNUMBER(SEARCH("WWW",A1)),0,1)

This will test cell A1 for the existence of www (assuming that this is
included in the cells with hyperlinks) and return a 0 (zero) otherwise it
will return 1. All you need then to do is sort on the helper column and the
data column.

Regards.

Bill Ridgeway
Computer Solutions
 
First enter this tiny UDF:

Function hyp2(r As Range) As String
hyp2 = r.Hyperlinks(1).Address
End Function

Say you data is in column A, then in B1 enter:
=ISERROR(hyp2(A1))
and copy down

The last step is to sort you data first by column B and by column A
 
Thanks for your help, Bill. Alas a lot of these hyperlinks aren't of the
www. variety.
However, Gary's Student's little UDF has worked a treat!

Regards
Gerry
 
Back
Top