Excel problem
#1
Scooby Regular
Thread Starter
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
Excel problem
I have a column of hyperlinks that have different text to display than the address itself. I want to be able to extract the cell ref for each cell that has a hyperlink and the place hyperlink text in the adjacent cell.
Anyone know how to do this?
TIA
Mark
Anyone know how to do this?
TIA
Mark
#2
Scooby Regular
Thread Starter
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
Got this from another forum
Create the following custom function in a standard module:
Function HyperlinkAddress(oCell As Range) As String
On Error Resume Next
HyperlinkAddress = oCell.Hyperlinks(1).Address
End Function
(The line On Error Resume Next avoids error values if the cell referred to does not contain a hyperlink.)
Say that A1 contains a hyperlink. Enter the following formula in B1 to see the hyperlink address:
=HyperlinkAddress(A1)
If you store the function in a module in your Personal.xls, use
=Personal.xls!HyperlinkAddress(A1)
Create the following custom function in a standard module:
Function HyperlinkAddress(oCell As Range) As String
On Error Resume Next
HyperlinkAddress = oCell.Hyperlinks(1).Address
End Function
(The line On Error Resume Next avoids error values if the cell referred to does not contain a hyperlink.)
Say that A1 contains a hyperlink. Enter the following formula in B1 to see the hyperlink address:
=HyperlinkAddress(A1)
If you store the function in a module in your Personal.xls, use
=Personal.xls!HyperlinkAddress(A1)
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
bluebullet29
General Technical
9
05 October 2015 02:17 PM