| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract email address in hyperlink
I copied several hundred email address hyperlinks from an html page into a
spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
If the hyperlink is in A1, then
=hyp(A1) will return the linkage part. Here is the VBA 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 If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Brossyg" wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
Check your other post, too.
Brossyg wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
I copied this VB code to the sheet's VB editor and then put =hyp(a1) in the
B1 cell. I get back this error: #NAME? What should I do? "Gary''s Student" wrote: If the hyperlink is in A1, then =hyp(A1) will return the linkage part. Here is the VBA 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 If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Brossyg" wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
Check your other post.
It becomes a pain for you when you multipost. Now you have two threads to check. And you essentially wasted Gary's Student's time or mine. Brossyg wrote: I copied this VB code to the sheet's VB editor and then put =hyp(a1) in the B1 cell. I get back this error: #NAME? What should I do? "Gary''s Student" wrote: If the hyperlink is in A1, then =hyp(A1) will return the linkage part. Here is the VBA 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 If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Brossyg" wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
Works for me in a standard module, not worksheet code.
-- Gary''s Student gsnu200702 "Brossyg" wrote: I copied this VB code to the sheet's VB editor and then put =hyp(a1) in the B1 cell. I get back this error: #NAME? What should I do? "Gary''s Student" wrote: If the hyperlink is in A1, then =hyp(A1) will return the linkage part. Here is the VBA 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 If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Brossyg" wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract email address in hyperlink
The OP had to check multiple threads. And he posted back in the other thread
that he got it working. Gary''s Student wrote: Works for me in a standard module, not worksheet code. -- Gary''s Student gsnu200702 "Brossyg" wrote: I copied this VB code to the sheet's VB editor and then put =hyp(a1) in the B1 cell. I get back this error: #NAME? What should I do? "Gary''s Student" wrote: If the hyperlink is in A1, then =hyp(A1) will return the linkage part. Here is the VBA 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 If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Brossyg" wrote: I copied several hundred email address hyperlinks from an html page into a spreadsheet. They all showed text as "Click her to email" on the html page. They copied correctly as hyperlink "mailto" links, but the text in the excel field is still "Click her to email". The email links span A1 - A500. I am trying to find a way to show the email address only in B1 - B500. How do i do this? Brossyg -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to set up a hyperlink in excel to email the doc as attachment | Excel Discussion (Misc queries) | |||
| Don't use your email address in your posts | Excel Discussion (Misc queries) | |||
| Extract email addresses | Excel Worksheet Functions | |||
| Excel Email Hyperlink Bug | Excel Discussion (Misc queries) | |||
| copy/convert column email addresses Hyperlink "mailto:" excel97 | Excel Discussion (Misc queries) | |||
Linear Mode
