Can I modify a Hyperlink with a function?
 

I have a column of several thousand cells that have different hyperlinks
in each one. I would like to set up a column adjacent to this column
and have the hyperlink address in this new column. I have found that
there is not a paste special function that will do this and the excel
"CELL" function does not work either - although it should. Any
suggestions? a VBA solution would be just fine as well.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375695

I am able to simply copy/paste a cell with a link and the link appears
in the new location. Try it.

Right click on the link, select Copy. Select your new location, right
click, Paste.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=375695

Does not work. I am not trying to duplicate a hyperlink. My cell has
the text "Yahoo" in it and the hyperlink address is
"http://www.yahoo.com". I want to put the "http://www.yahoo.com" in
the text of the cell adjacent to the original cell and I do not want to
have to modify the hyperlink manually to do so.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375695

copy, edit, paste as hyperlink, if that does not work, try edit, paste
special, values


--
badpuppie
------------------------------------------------------------------------
badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453
View this thread: http://www.excelforum.com/showthread...hreadid=375695

that does not work.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375695

odd, it works on mine, perhaps try text to columns, then the same thing


--
badpuppie
------------------------------------------------------------------------
badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453
View this thread: http://www.excelforum.com/showthread...hreadid=375695

You are able to copy a cell that has a hyperlink and paste the target
location of the hyperlink with the edit paste special values? When I
do that it simply pastes the display text and not the hyperlink
address. I want the hyperlink address.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375695

You can request enhancements by sending an email to:

Saved from a previous post:

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

barrfly wrote:

I have a column of several thousand cells that have different hyperlinks
in each one. I would like to set up a column adjacent to this column
and have the hyperlink address in this new column. I have found that
there is not a paste special function that will do this and the excel
"CELL" function does not work either - although it should. Any
suggestions? a VBA solution would be just fine as well.

--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375695

--

Dave Peterson