Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 11th 05, 06:59 PM
Jose
 
Posts: n/a
Default how do I display a hyperlink in a seperate cell in excel?

I have a spreadsheet that has emails hyperlinked. I simply want to display
those emails in another cell

  #2   Report Post  
Old January 12th 05, 12:42 AM
Dave Peterson
 
Posts: n/a
Default

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

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)

Jose wrote:

I have a spreadsheet that has emails hyperlinked. I simply want to display
those emails in another cell


--

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
Extract hyperlink string from excel cell Ryan Sapien Links and Linking in Excel 1 January 20th 05 12:24 AM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM
display negative values as a blank cell in Excel pherozeb Excel Discussion (Misc queries) 3 January 5th 05 04:40 AM
display negative values as a blank cell in Excel Pheroze Bharucha Excel Discussion (Misc queries) 0 January 4th 05 10:51 PM
Display a pix from a URL in a Excel cell Pilot007 Excel Discussion (Misc queries) 1 December 27th 04 12:22 AM


All times are GMT +1. The time now is 05:32 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017