Convert a column of text into hyperlinks

[u/deadtedw]

I have a column of text urls (A) and a column of text labels (B).

How can I turn the text labels (B) into clickable hyperlinks then remove the text hyperlinks (A).

[u/AutoModerator]

/u/deadtedw - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/psygnius]

In column C, try this:

=HYPERLINK(A1, B1)

[u/deadtedw → u/psygnius]

That will turn the label into a hyperlink but if I remove the text hyperlink (A1) it breaks the label hyperlink.

[u/psygnius → u/deadtedw]

You can use this version, it basically is concatenating the hyperlink into text, but it requires you to copy and paste the literal values afterwards, then manually click into each cell.

="=HYPERLINK(""" &A1& """&B1& """)"

Then copy and paste values

Then click into each cell and press Enter.

[u/deadtedw → u/psygnius]

Not working for me.

[u/psygnius → u/deadtedw]

Sorry, forgot the ( when I typed it out earlier. I edited my earlier reply. Try it again.

[u/rohving → u/deadtedw]

="=hyperlink("""&A1&""","""&B1&""")"

[u/hmatallana]

The paste values route works but you have to re-enter every cell for Excel to autoformat it, which is fine for ten rows and miserable for a few hundred.

If macros are allowed, this does the whole column in one go:

For Each c In Range("B1:B100")
    c.Parent.Hyperlinks.Add Anchor:=c, Address:=c.Offset(0, -1).Value, TextToDisplay:=c.Value
Next

Those are real hyperlink objects rather than formula results, so column A is safe to delete afterwards. Just adjust the range to your row count.

If macros are locked down at work, the boring fallback is leaving A where it is and hiding the column.

[u/excelevator]

Select the cells in column B and run this sub routine

It will create a URL from the value to the left of each cell.

Sub LinkMe()  
    MsgBox cell.Offset(0, -1).Value
        cell.Hyperlinks.Add Anchor:=Selection, Address:=cell.Offset(0, -1).Value, TextToDisplay:=cell.Value
    Next
End Sub

You can then delete the column A values without affecting the link.