Thanks a million. Worked like a charm!
-----Original Message-----
I got lazy and used evaluate to remove the double quotes -
apparently some
of your labels are being interpreted as worksheet
functions and returning
#Value. Here is a revision (change
C:\Data\Textfile1.txt to reflect the
text file to write to)

Sub Tester12aa()
Dim f As Long, sform As String, sFrom1 As String
Dim sForm2 As String, sForm2a As String
Dim sForm3 As String, sForm3a As String
f = FreeFile()
Open "C:\Data\Textfile1.Txt" For Output As #f
For Each cell In Selection
sform = cell.Formula
If InStr(1, sform, "=hyperlink(", vbTextCompare) Then
sform1 = Right(sform, Len(sform) - 10)
iloc = InStr(sform1, ",")
sForm2 = Left(sform1, iloc - 1)
sForm2 = Right(sForm2, Len(sForm2) - 1)
sForm3 = Right(sform1, Len(sform1) - iloc)
sForm3 = Left(sForm3, Len(sForm3) - 1)

End If
sForm2a = Application.Substitute(sForm2, Chr(34), "")
sForm3a = Application.Substitute(sForm3, Chr(34), "")
sLine = sForm2a & ", " & sForm3a
Print #f, sLine
Next
Close #f
End Sub

--
Regards,
Tom Ogilvy


"dre" wrote in message
...
Great,
How can I write the results to a text file though? Also,
some of the values appear like #VALUE! I would assume
that
the length of the data was too long?
-----Original Message-----
Sub Tester12()
For Each cell In Selection
sForm = cell.Formula
If InStr(1, sForm, "=hyperlink(", vbTextCompare) Then
sform1 = Right(sForm, Len(sForm) - 10)
iloc = InStr(sform1, ",")
sForm2 = Left(sform1, iloc - 1)
sForm2 = Right(sForm2, Len(sForm2) - 1)
sForm3 = Right(sform1, Len(sform1) - iloc)
sForm3 = Left(sForm3, Len(sForm3) - 1)

End If
cell.Offset(0, 1).Value = Evaluate(sForm2)
cell.Offset(0, 2).Value = Evaluate(sForm3)
Next

End Sub

--
Regards,
Tom Ogilvy




"dre" wrote in message
...
I have a an excel file that contains one worksheet.
The worksheet contains more than 1500 rows of
hyperlinks.
I need to get the actual value of each cell that
contains
a hyperlink on the worksheet. For example, The
display
value for cell A2 is "Link to document". The actual
value
for the cell A2 is "A2=hyperlink
("document1.doc","Link
to
document")". I need to parse through the cell values
extracting the values that is within the hyperlink
parentheses.

Any help would be appreciated.


.



.