1
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.
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.
So if "Document Title" rows have a Hyperlink, you want to extract that URL in the "External Link" column. If it does not have a hyperlink, you want to add one? So under the
www.fakewebsite.com/how-to-eat-worms
, you want saywww.fakewebsite.com/how-to-dig-a-hole
? – BruceWayne – 2017-04-27T15:49:25.860@BruceWayne The solution doesn't have to do both - either option would work fine. Extracting a hyperlink from column B (if present) to column C is adequate. Applying a hyperlink from column C (if present) to column B is also adequate. What I don't need is for column C to automatically contain a hyperlink based on the text of column B, as the URL does not always follow the format shown in my example. For instance, the URL for the document "How to Dig a Hole" may end up being www.fakewebsite.com/how-to-dig-a-hole-27635 . – Piripero – 2017-04-27T16:10:56.943