Seperating hyperlink address from viewable text
 

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?

Seperating hyperlink address from viewable text
 

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.
--
Gary''s Student - gsnu200801


"Aominay" wrote:

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?

Seperating hyperlink address from viewable text
 

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200801


"Aominay" wrote:

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?

Seperating hyperlink address from viewable text
 

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/Writin...ionsInVBA.aspx
for specifics on UDFs




--
Gary''s Student - gsnu200801


"Aominay" wrote:

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200801


"Aominay" wrote:

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?

Seperating hyperlink address from viewable text
 

YOU ARE AWESOME! Thank you so much!!!

"Gary''s Student" wrote:


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/Writin...ionsInVBA.aspx
for specifics on UDFs




--
Gary''s Student - gsnu200801


"Aominay" wrote:

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200801


"Aominay" wrote:

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?

Seperating hyperlink address from viewable text
 

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.


--
KATHERINE


"Gary''s Student" wrote:


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/Writin...ionsInVBA.aspx
for specifics on UDFs




--
Gary''s Student - gsnu200801


"Aominay" wrote:

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!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200801


"Aominay" wrote:

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?