Can you copy email addresses in to new column out of Hyperlink column

[Guest]

I had been using Excel worksheet as my contacts manager. It contains columns last name, first name, addr, tel, and Email address(as hyperlink). I am trying to import this data into outlook. Unfortunaltey Outlook requires email addresses as text field and Hyperlink field. Any clue, how I can copy addresses out of hyperlinks through full column into new column with email address as Text only

P

 

[Paul Falla]

Dear PM
Try this...
Select and copy the entire column of hyperlinked email
addresses, then in your new column paste special, values.
this should hopefully do the trick.

Hope this helps

Paul Falla

-----Original Message-----

I had been using Excel worksheet as my contacts manager.

It contains columns last name, first name, addr, tel, and
Email address(as hyperlink). I am trying to import this
data into outlook. Unfortunaltey Outlook requires email
addresses as text field and Hyperlink field. Any clue,
how I can copy addresses out of hyperlinks through full
column into new column with email address as Text only?

 

[Guest]

I had tried that. But that does not work

I have about 2000 email addresses Hyperlink to convert to text. Doing it one at a time manually is going to be painful. I there a VBA procedure that can help

 

[Norman Harker]

Hi PM!

It doesn't work if you copy and paste special values to the same
column but it does work if you copy and paste special values to a new
column.

Tried OK on Excel 97 through to Excel 2003
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I had tried that. But that does not work

I have about 2000 email addresses Hyperlink to convert to text.

Doing it one at a time manually is going to be painful. I there a VBA
procedure that can help?

 

[Guest]

I am using Office 2003

The Email addresses are in column E. The hyperlink text to display of each cell of that column is "Click to E-Mail" with different email addresses. I copied full column E, clicked Paste Special to column G (a blank column), checked Values and click OK. What it copied to new column was "Click to E-Mail" all through; not the email addresses! I also tried to Paste Special on a new Sheet. It gave same results :<

 

[Dave Peterson]

How did you create the hyperlink?

Via the worksheet function =hyperlink() or via Insert|Hyperlink?

If it from insert|hyperlink, then you could use a UserDefinedFunction:

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

 

[jberkson]

I was just wondering if you've made any progress on this item. I hav
been working on the same item.... but to no avail...

The following MS KB article did offer some helpful VB that gets u
half-way there
http://support.microsoft.com/default.aspx?scid=kb;EN-US;156353

 

[jberkson]

I found the solution in another thread, so I figured I'd post here:
http://www.excelforum.com/showthread.php?s=&threadid=194194&highlight=copy+hyperlin