Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 05, 10:03 PM
pat_rick
 
Posts: n/a
Default How can I extract hyperlink value

I copied a table from web site to excel ehich has the email address as
hyperlink to names of people. In Excel it appears as hyperlink and shoes the
name, and email address as hyperlink value.
How can I extract the hyperlink value and have the acctual email address on
another cell.

Thanks in advance

  #2   Report Post  
Old January 8th 05, 02:17 AM
Dave Peterson
 
Posts: n/a
Default

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

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getURL(a1)

=====
this will return


If you want to drop the mailto:, you could just use:
=mid(getURL(a1),8,255)

(make 255 big enough for the longest address)


pat_rick wrote:

I copied a table from web site to excel ehich has the email address as
hyperlink to names of people. In Excel it appears as hyperlink and shoes the
name, and email address as hyperlink value.
How can I extract the hyperlink value and have the acctual email address on
another cell.

Thanks in advance


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving rows with Hyperlink doesn't move hyperlink address Samad Excel Discussion (Misc queries) 15 June 22nd 06 12:03 PM
Extract hyperlink string from excel cell Ryan Sapien Links and Linking in Excel 1 January 20th 05 01:24 AM
Hyperlink to a word document JS Excel Discussion (Misc queries) 1 December 10th 04 11:49 PM
Hyperlink to word document problem JS Links and Linking in Excel 0 December 8th 04 11:54 PM
Hyperlink to specific sheet in Excel Web File jd17 Links and Linking in Excel 0 December 8th 04 10:03 PM


All times are GMT +1. The time now is 04:03 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017