1
I am automating an excel based report and need a formula that will take cell A2 with its hyperlink from Sheet2 and copy it to another column in Sheet 1. Hyperlink points to external web address. When I use just =A2 it copies the text but without the hyperlink.
How to do this? Thank you for your help
Velin
Posted 2018-05-10T07:41:55.927
Reputation: 11
1
The below will work:
=HYPERLINK(MID(FORMULATEXT(Sheet2!A2),SEARCH("www.",FORMULATEXT(Sheet2!A2)),SEARCH(".com",FORMULATEXT(Sheet2!A2))-SEARCH("www.",FORMULATEXT(Sheet2!A2))+4),"Click")
It uses FORMULATEXT, to display the hyperlink as text, the SEARCH to find the stat an end of the link, and then MID to recreate the link,
Thne wrap the whole thing in HYPERLINK
This will only work on links starting with www. and ending .com
you can change it to suit the link you have in your sheet.
Note. FORMULATEXT is availiable for excel 2016
PeterH
Posted 2018-05-10T07:41:55.927
Reputation: 5 346
Hello, I cannot make it work. My link is very complex - it points to an object in SAP related tool located in company intranet and includes redirect. I receive NA when trying to change values for link start and end in the formula – Velin – 2018-05-10T08:34:11.103
what does the link start and end with ? – PeterH – 2018-05-10T08:35:48.440
link end: OBJECT_ID&crm-object-value=9000272438 – Velin – 2018-05-10T08:40:40.567
for every cell in the source column the number in the end is different and I want to be able to expand the formula with dragging down in destination column – Velin – 2018-05-10T08:42:54.493
link start update: globe7. – Velin – 2018-05-10T08:46:53.930