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

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

  • 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 May 10 '18 at 8:34
  • what does the link start and end with ? – PeterH May 10 '18 at 8:35
  • link end: OBJECT_ID&crm-object-value=9000272438 – Velin May 10 '18 at 8:40
  • 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 May 10 '18 at 8:42
  • link start update: globe7. – Velin May 10 '18 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.