Ask Dr. Dotnetsky - How can I copy/paste text's mailto: email address

Asked By Mark Robinson on 30-Jun-06 07:10 PM
I have a Excel workbook containing 1,200 names (e.g., Smith, John) in column A.  Each name has a hyperlinked email address (e.g., mailto:john.smith@aol.com).  I need to copy each hyperlinked email address and paste it (e.g., john.smith@aol.com) into a new column so that I can create a distribution list.  None of the options in "Paste Special" seem to achieve this task.  What do I need to do so that I don't have to type each one manually?

Thanks!

Mark

Why not try save as HTML? - Asked By Peter Bromberg on 30-Jun-06 10:25 PM

That should preserve the link.

An example how to extract email and URL - Asked By Mark Robinson on 01-Jul-06 01:30 PM

Here's one line of the spreadsheet:

Smith, Tyler 	Dallas	TX	Ascension Group Architects	Architect

mailto:tda_tx@aol.com is linked to "Smith, Tyler" which all appears in cell A6.
http://www.ascensiongroup.com/ is linked to "Ascension Group Architects" in cell D6.

The last time I created a distribution list for these fellow members, I had to roll over each name and company with my cursor in order to see the mailto and URL info and, then, typed each one individually.  Or, I right clicked, selected "Edit hyperlink", and copied / pasted the email or URL into a new column.

Can I avoid the manual data entry?  How can I extract all 1,200 email addresses linked to "Last name, First name" in column A?  And, how can I extract and paste into another column the 1,200 URL's that are hyperlinked to the company names in column D?

Thank you for any shortcuts you can provide.

Mark

How copy/paste invisible mailto: emails & URLs - Asked By Mark Robinson on 01-Jul-06 04:45 PM

I have an Excel workbook containing 1,200 names (e.g., Smith, John) in column A and 1,200 company names in column D. Each name in Column A is hyperlinked to an email address (e.g., mailto:john.smith@aol.com). Similarly, each company name in column D is hyperlinked to a URL (e.g., "http://www.ascendinggroup.com/). 

Here's one line of the spreadsheet: 

Column A: Smith, Tyler (hyperlinked to invisible email address as "mailto: tda_tx@aol.com) 
Column B: Dallas 
Column C: TX 
Column D: Ascending Architects (hyperlinked to invisible URL as "http://www.ascendinggroup.com/") 
Column E: Architect 


I need to copy each invisible hyperlinked email address and URL and paste them as visible text into two new columns so that I can create a distribution list. 

The last time I created a distribution list for these fellow members, I had to roll over each name and company with my cursor in order to see the mailto and URL info and, then, typed each one individually. Or, I right clicked, selected "Edit hyperlink", and copied / pasted the email or URL into a new column. 

None of the options in "Paste Special" seem to achieve this task. 

What do I need to do so that I don't have to type each one manually? Can I avoid the manual data entry? How can I extract all 1,200 email addresses linked to "Last name, First name" in column A? And, how can I extract and paste into another column the 1,200 URL's that are hyperlinked to the company names in column D? 

Thank you for any shortcuts you can provide.

Mark
user defined Excel function - Asked By mv ark on 03-Jul-06 09:10 AM
Try the user defined function with detailed instructions at http://www.ozgrid.com/VBA/HyperlinkAddress.htm and then you can use it like any other Excel function.
Just place the user defined formula  =GetAddress(cell#) in the cell where you want show details of the hyperlink.
Thanks! The UDF works! - Asked By Mark Robinson on 03-Jul-06 04:55 PM
Thanks everyone.  The user defined function worked great!