In Excel 365 (version 2407), I have a row of HTML hyperlinks in plain text format. Each cell in that row includes something like:
<a href="https://www.example.com/products/matters/m_document_edit.php?id=1026574&docid=10213208">Display text</a>
I would like to change these HTML hyperlinks to clickable Excel hyperlinks.
I tried the following formula (the HTML hyperlink is in cell D2) but received a #VALUE! error.
=HYPERLINK(MID(D2, FIND("href='", D2) + 6, FIND("'>", D2) - FIND("href='", D2) - 6), MID(D2, FIND(">", D2) + 1, FIND("</a>", D2) - FIND(">", D2) - 1))
What am I doing wrong?
Dave Ga is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
rotabor’s comment has the correct answer:
=HYPERLINK(TEXTBEFORE(TEXTAFTER(D2,”href=”””),”””>”),TEXTBEFORE(TEXTAFTER(D2,”””>”),””))
This formula preserves the original display text of the link.
Dave Ga is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Try the following formula-
=HYPERLINK(SUBSTITUTE(TEXTBEFORE(TEXTAFTER(D2,"href="),">"),CHAR(34),""),"Click Here")
10