Just in case, here's some code that will work through a column and look for
cells with =HYPERLINK( formula in them, and turn the formula into an actual
hyperlink with same displayed text as before. That might solve your problem.
Change sheet name, column and first possible row as needed. No need to ever
change anything again, since after altering a =HYPERLINK( formula, it won't
be there and future runs of the same macro won't affect the already converted
cells at all.

Sub HyperlinksFromFormula()
Const linkSheetName = "Sheet1" ' change to real sheet name
Const hlColumn = "C" ' change as needed
Const firstRow = 2 ' 1st possible row with HYPERLINK() formula
Const formulaPhrase = "=HYPERLINK("
Dim lastRow As Long
Dim hlCells As Range
Dim anyCell As Range
Dim hText As String
Dim hLink As String
Dim p1 As Integer
Dim p2 As Integer
Dim p3 As Integer
Dim p4 As Integer

lastRow = Worksheets(linkSheetName).Range(hlColumn & _
Rows.Count).End(xlUp).Row - 1
Set hlCells = Worksheets(linkSheetName).Range(hlColumn & _
firstRow & ":" & hlColumn & lastRow)
For Each anyCell In hlCells
If anyCell.HasFormula Then
If Left(anyCell.Formula, Len(formulaPhrase)) = formulaPhrase Then
'must dig out text and hyperlink
p1 = InStr(anyCell.Formula, Chr$(34)) ' position of 1st "
p2 = InStr(p1 + 1, anyCell.Formula, Chr$(34)) ' position of 2nd "
p3 = InStr(p2 + 1, anyCell.Formula, Chr$(34)) ' position of 3rd "
p4 = InStrRev(anyCell.Formula, Chr$(34)) ' position of last/4th "
hLink = Mid(anyCell.Formula, p1 + 1, p2 - p1 - 1)
hText = Mid(anyCell.Formula, p3 + 1, p4 - p3 - 1)
If hText = "" Then
hText = hLink
End If
anyCell.Formula = "" ' delete formula
Worksheets(linkSheetName).Hyperlinks.Add Anchor:=anyCell, _
Address:=hLink, TextToDisplay:=hText
End If
End If
Next ' end of hlCells loop

End Sub

"MarkB" wrote:

I have a CRM system that allows me to export information into excel. As a
part of the export process I'm having the program generate what I hoped was a
active hyperlink back to the specif record in the host application. I'm using
the "HYPERLINK" function but it doesn't result in displaying the active
hyperlink in the target cells. After export in excel I need to format the
cells to general and then to select each cell and hit enter in the formula
bar to convert it to a hyperlink. Way too tedeous! Is there a short cut or
macro that could do this conversion for me? Or is there a way that I could
set up the export routine logic differently? Below is an example of what is
exported in excel...

=HYPERLINK("https://na2.salesforce.com/0064000000907ry", "WRS - SOSCOE
Studio - Eng Serv for LOCI 2.1 / LOS-SE 5.0 (March)")

Thanks for your help!

MarkB