Seperating hyperlink address from viewable text

  • Thread starter Thread starter Aominay
  • Start date Start date
A

Aominay

I have a list of 551 hyperlinked email addresses. Column A shows the persons
name, and the name is hyperlinked with the persons email address. I would
like to figure out how to keep the person's name in Column A, and be able to
view the email address as text in another column without having to type each
one out individually (or go through each one and copy and paste from the Edit
Hyperlink box). Is there a way to do this in MS Excel 2003 quickly?
 
Try this UDF:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

So if A1 contains the e-mail hyperlink, then:
=hyp(A1) will display the mailto: address.
 
I'm function stupid, so I don't know what UDF is? Totally lost on this answer
and how to apply it ... need layman's terms.

Thanks!
 
User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=url(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs
 
YOU ARE AWESOME! Thank you so much!!!

Gary''s Student said:
User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=url(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs
Click to expand...
 
Gary can I ask you for help.

I need to seperate a hyperlink so that i can see an email address.
If I click on the work yes it links to an email address. I want to stop the
hyperlink and show the email address.
 
Back
Top