Can anyone help?

[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

 

[Bill Ridgeway]

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

 

[Guest]

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

 

[Gerald Oliver Swift]

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

 

[Gerald Oliver Swift]

Thank you, kind sir. Worked perfectly!

Many thanks
Gerry