Automatically applying hyperlinks from one cell to another

Question

I have a workbook that has a column which contains the titles of different documents which are published to a website (B4:B6). Another column contains the URLs for those documents (C4:C6). The URL is created by the website when the document is uploaded, and always appends a number to the URL that is not predictable ahead of time.

Sample spreadsheet

I have a user who is used to manually inserting a hyperlink into the cell which contains the title, so that the title cell points to the URL. He wants the title cells to be hyperlinked, understands the need to have the hyperlink in its own cell for reference purposes, but doesn't want to re-type or copy-paste the URL. How do I setup the URL cells (C4:C6) to check the title cells for embedded hyperlinks, and if it finds one, to replicate it in the appropriate URL cell?

Alternatively, how do I setup the title cells to check the URL cells for text, and if it finds some, to automatically apply that text as a hyperlink in the appropriate title cell?

And worst of all, how do I do this without using VBA/macros?

Put another way:

Current state: User manually applies hyperlink to title in column B. User also manually enters text of the URL in column C.

Desired state (option 1): User manually applied hyperlink to title in column B. Text of the URL is automatically extracted from the hyperlink (not the text of the title) and placed in column C.

Desired state (option 2): User manually enters text of the URL in column C. The URL is automatically applied as an embedded hyperlink in the cell containing the title in column B.

Helper columns are totally fine if necessary.

Answer 1

If you want to use formulas and links do not follow a simple pattern then you need to copy your titles into another column (or sheet) and refer to them in your hyperlink formula.

HYPERLINK("link","copied_title")

Look below;

For the other desired option, you still have to use VBA but maybe only a user-defined option would be sufficient as described in one of the answers in this thread: Extracting URLs from Hyperlinks

---

VBA Solution

---

If you are interested in VBA, something like this would help:

Sub hyperlink_title()
    Dim i As Integer
    Dim wsh as Worksheet
    Set wsh = ThisWorkbook.ActiveSheet

    i = 4
    With wsh
    While .Cells(i, 2) <> ""

        ActiveSheet.Hyperlinks.Add Anchor:=.Cells(i, 2), Address:=.Cells(i, 3).Value, _
        TextToDisplay:=.Cells(i, "B").Value2

        i = i + 1

    Wend

End Sub

Answer 2

This formula will look at a cell, and if there's a hyperlink (using the Hyperlink() formula), will return the URL. If there's no link and it's just text, it creates a link to http://www.fakewebsite.com/[text]:

=IFERROR(MID(FORMULATEXT(A2),SEARCH("http",FORMULATEXT(A2)),SEARCH(",",FORMULATEXT(A2))-SEARCH("http",FORMULATEXT(A2))-1),HYPERLINK("http://www.fakewebsite.com/"&SUBSTITUTE(A2," ","-")))

Note: If there's no "http", just replace "http" with "www" in the above formula. It works like this:

Edit: Per clarification, this will extract a link from "Document Title" column and just put some text if there's no link:

=IFERROR(MID(FORMULATEXT(A2),SEARCH("http",FORMULATEXT(A2)),SEARCH(",",FORMULATEXT(A2))-SEARCH("http",FORMULATEXT(A2))-1),"No Link")