CODEWITHSUNDEEP
excelhyperlink
Dave A
Dave A

Reputation: 9

Excel convert hyperlink formulas to links with friendly names

I need to convert columns of cells with HYPERLINK formulas into cells with just the friendly_name hyperlinked to link_location. For instance, =HYPERLINK("https://www.google.com/search?hl=en&q="&SUBSTITUTE(A2," ","+")&"&tbm=isch&gws_rd=ssl","G images") to a cell that just has G images in it and is hyperlinked to the hyperlink that the formula produces, e.g. https://www.google.com/search?hl=en&q=search+term&tbm=isch&gws_rd=ssl. I can do this by pasting into Word and pasting back into Excel, but I need to do it within Excel, whether by some copy/paste trick or a macro.

Upvotes: 0

Views: 4385

Answers (3)

Harry Lee
Harry Lee

Reputation: 149

I know this is old but not sure if this is resolved. Try the following.

Sub convert_hyperlink_formula_to_hyperlink_cell()
    Dim address_string As String, display_string As String
    Dim current_range As Range

    For Each current_range In Selection
        address_string = current_range.Hyperlinks(1).Address
        display_string = current_range.Value
        current_range.ClearContents
        ActiveSheet.Hyperlinks.Add Anchor:=current_range, Address:=address_string, TextToDisplay:=display_string
    Next current_range
End Sub

Upvotes: 0

David
David

Reputation: 11

Found this at Chihiro, Excel Ninja: One way to do it is to copy and paste out to Word Doc. And then copy and paste back from Word Doc to Excel. This will retain link and title/friendly name.

For large documents or many columns with links, this will be less practical of course.

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

I think I understand, try this (adjust rng as necessary):

edit: New code, per comment below:

Sub replace_Hyperlink_Formula_With_Text()
Dim linkWS As Worksheet, newWS As Worksheet
Dim linkText$, url$, address$, subPart$
Dim cel As Range, rng As Range
Dim commaPos&

Set linkWS = Sheets("Sheet1") ' Change this to the sheet name that has =Hyperlink("http://...","Link")
Set newWS = Sheets.Add
newWS.Name = "Links Only" ' or whatever you want the non-Hyperlink Formula sheet to be called.

Set rng = linkWS.Range("A1:A2") ' Edit this as needed

For Each cel In rng
    linkText = cel.Value
    commaPos = InStrRev(cel.Formula, ",")

    subPart = WorksheetFunction.Substitute(linkWS.Range("A2"), " ", "+")
    url = Mid(cel.Formula, WorksheetFunction.Search("(", cel.Formula) + 2, commaPos)
    Debug.Print url

    commaPos = InStrRev(url, ",")
    url = Left(url, commaPos - 2)
    url = WorksheetFunction.Substitute(url, "&", ";", 1)
    Debug.Print url


    ' Now, trim SUBSTITUTE out of it
    Dim leftUrl$, rightURL$
    leftUrl = Left(url, WorksheetFunction.Search(";", url) - 2)
    rightURL = Mid(url, WorksheetFunction.Search("&", url) + 2, Len(url))
    url = leftUrl & subPart & rightURL
    Debug.Print url
  '  cel.ClearContents
    address = cel.address
    newWS.Range(address).Hyperlinks.Add anchor:=newWS.Range(address), address:=url, TextToDisplay:=linkText
Next cel

End Sub

It's kludgy, but it should succeed in parsing out the &Substitute...& part, and replacing it with what's in A2.

Upvotes: 0

Related Questions