LinkBack URL
About LinkBacks
Hi All,
Is there a way to extract the Hyperlink Text of a cell into another cell by way of using a formula (not by VBA).
Extract Hyperlink Text by Formula
Hi All,
Is there a way to extract the Hyperlink Text of a cell into another cell by way of using a formula (not by VBA).
It depends what you want to do with it. Give more details, please.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If you have ver 2010 (as your profile says), probably no formula to extract what you have inside the HL, but if you have 2016, you could try using =formulatext() to show the formula in text, then extract from that?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I have a column where certain cells have a hyperlink to 3 types of destinatiins:
1 link to other cells within the workbook;
2 link to cells in other workbooks; and
3 link to a web
I need to extract only those web links and show it as a text in the adjacent column.
Can you give some examples of text contents? I'm struggling to see why you can't just do =A1, for example, which will give the text of a hyperlink in A1.
=A1 will not give me the Text of Hyper Link, rather it will give me the text in the Cell.
I hope I am clear here, the Text in the cell is not same as the text of the Hyper Link.
e.g in Cell A2 I have a Text : "ABCD" where as the hyper link refers to "https://www.google.com....etc"
In that case, you can't do what you want without a bit of VBA. This is a UDF written by Rick Rothstein which has been shared here before:
It needs to go into a standard workbook module.
Please Login or Register to view this content.
Then use the UDF thus:
=Hlink(A1)
If hyperlink is constructed via =HyperLink() formula, then you can use Get.Cell() Excel4Macro function.
Assuming following is in A1:
=HYPERLINK("https://www.google.com","ABCD")
In Name manager add following, while you have B1 selected.
cel_Formula_Text =GET.CELL(6,Sheet1!$A1)
0.JPG
Then in B1. Enter following. Adjusting 50 to larger number as needed.
Formula:
Please Login or Register to view this content.
1.JPG
Note that file must be saved as Macro enabled workbook (.xls, .xlsm, .xlsb).
See attached file.
If the hyperlink is done manually, use UDF by AliGW.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks a lot, I will consider the same.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks