Excel Formula Concat a text cell to a hyperlink cell and keep the hyperlink

0
votes

I have text in cell A8 and a hyperlink in cell B8.

A8 contains Text

B8 contains a Proper Hyperlink with the name HyperName (and URL).

I want to concat the two cells in cell C8 with a hyphen between the two components and have a hyperlink to the right of the hyphen.

When I try this, the two cells are combined, but the hyperlink disappears.

CONCAT(A8, " - ", B8) - this does not work.

To work around this:

  1. I copy the two cells and paste into MS Word.
  2. I then in MS word convert the two cell table into text.

This looks great - the hyperlink is working, and the text to the left of the hyphen is not hyperlinked.

  1. I then paste that back into a cell in Excel

The cell C8 now has Text - HyperName where only HyperName has a link to the URL.

How do I do this totally within Excel?

1
excelhyperlinkconcatenation

1 Answers

0
votes

NO VBAimperfect

What about using the HYPERLINK builtin function ? That being said, it looks like you are going to have to consider expliciting your url, for example putting it in D8

= CONCAT(A8, " - ", HYPERLINK(D8, B8))

which will render as

"Text - HyperName"

and redirect to the url specified within D8. So, in conclusion, there is no way to avoid expliciting the url.

VBA A little bit of code, but perfectly suits you need(!)

There exists a solution if you are okay with doing some VBA.

Open Microsoft Visual Basic (using the keyboard shorcut ALT+F11). You will see a left panel showing the internal tree-like structure of your VBA project (a priori nought for the moment).

Locate the VBAProject (<THE-NAME-OF-YOUR-FILE-HERE>). Right-click it -> Insert -> Module. Doing so has created an empty "script" called Module1.

enter image description here

Double-click it. On the right panel, copy/paste the following (credits)

Function EXTRACT_HYPERLINK(rng As Range) As String
     On Error Resume Next
     EXTRACT_HYPERLINK = rng.Hyperlinks(1).Address
End Function

Go back to the non-vba part of your excel file, save it, taking care of selecting the xlsm extension instead of xlsx (you will be alerted if you don't).

You now have your custom function (indeed, I could have chosen a name totally different than EXTRACT_HYPERLINK) and you can henceforth simply do

= CONCAT(A8, " - ", HYPERLINK(EXTRACT_HYPERLINK(B8), B8)