Can I modify a Hyperlink with a function?

[barrfly]

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

 

[swatsp0p]

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

 

[barrfly]

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

 

[badpuppie]

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

 

[barrfly]

that does not work

 

[badpuppie]

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

 

[barrfly]

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

 

[Dave Peterson]

You can request enhancements by sending an email to: (e-mail address removed)

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