how to separate text from a hyperlink into two cells

[j2i]

I'm going nuts... I have a column of hyperlinks in Excel... they display as blue underlined text... like "Joe's web site" "Bill's web site"... etc...

Hidden behind the text displayed is the actual hyperlink to "Joe's web site" i.e. "http:// joeswebsite.com"

I need to import hundreds of these into a contact database as two columns - one with the text name of the web site and the actual URL in a second.

I can do it one at a time... by using the Edit Hyperlink command... copying the hyperlink... and pasting it in another field... but that's mindless.

I tried to create a macro... but for some reason... it doesn't work... it pastes the same hypelink in every sell... I tried the Copy and Paste Special command... that doesn't seem to work.

There must be a function or a trick to do this... HELP please... (there is probably a VBA trick but I don't know VBA)

B.T.W..... I'm using Excel 2002.

Thanks if you can help!

[Russell Hauf]

On 2002-05-18 17:28, j2i wrote:
I'm going nuts... I have a column of hyperlinks in Excel... they display as blue underlined text... like "Joe's web site" "Bill's web site"... etc...

Hidden behind the text displayed is the actual hyperlink to "Joe's web site" i.e. "http:// joeswebsite.com"

I need to import hundreds of these into a contact database as two columns - one with the text name of the web site and the actual URL in a second.

I can do it one at a time... by using the Edit Hyperlink command... copying the hyperlink... and pasting it in another field... but that's mindless.

I tried to create a macro... but for some reason... it doesn't work... it pastes the same hypelink in every sell... I tried the Copy and Paste Special command... that doesn't seem to work.

There must be a function or a trick to do this... HELP please... (there is probably a VBA trick but I don't know VBA)

B.T.W..... I'm using Excel 2002.

Thanks if you can help!

Try the code in this post:

http://www.mrexcel.com/board/viewtop...ic=200&forum=2

To paste this code into your workbook, open the VB editor (Alt+F11 will do this), then go to Insert-Module. In the link above, copy everything from Option Explicit to End Function, and paste this into your module.

Then just use this function like you would any regular Excel function
(ex: =ShowAddress(A1), where A1 has the hyperlink you want to see the address of). Let me know if you need more help.

-Russell

[anchorman]

-- wrong thread...

[ This Message was edited by: anchorman on 2002-12-09 14:36 ]