Converting email address hyperlink to the actual email text

[srsport1]

Hi,

Please help me with a question:

I would like to be able to convert email address hyperlinks to the actual email text.

For example, let's say Alex Rodriguez's email address is arod@yankees.net. If I have a hyperlink in an excel cell that reads "Alex Rodriguez" how do I convert it to read the actual email address in that cell that the hyperlink sends me to which is arod@yankees.net.

Further, if i have a list of 100 such hyperlinks, how can I convert them all in one swoop?

Thanks in advance.

 

[JimB]

Welcome to the Board!

Try this code to have hyperlink cells display the underlying url or email address.
Just adjust the cell references and loop as necessary to fit your needs.

Sub showHyperlink()

Range("A1").Activate

    Do Until IsEmpty(ActiveCell)
        If ActiveCell.Hyperlinks.Count <> 0 Then
            ActiveCell.Value = Replace(ActiveCell.Hyperlinks.Item(1).Address, "mailto:", "")
        End If
        ActiveCell.Offset(1, 0).Activate
    Loop

End Sub

 

[srsport1]

Thanks JimB...but

Hi Jim,

Thanks for the reply. But, unfortunately you are dealing with a tech moron. How do I apply this code? I'm guessing I highlight the appropriate cells and then....?

 

[JimB]

Sorry I left you hanging....

How is the data you want converted organized on your spreadsheet? All in one row or column? In various worksheet cells? In more than one sheet?

 

[srsport1]

Hi Jim,

Thanks for coming back The data is easiest for me to organize into a single column.

Steve

 

[JimB]

Great. If you put it all in one column - with no empty cells mixed in - you can use the code I provided without too many, if any, changes.

Now we have to add the code to your workbook.

Press Alt + F11 to open the Visual Basic Editor, and then from the VBE window, click Insert > Module. Then paste the code into the module and save. You may have to adjust the cell reference in the code where it says Range("A1").Activate to align with the first cell of your data (if it does not begin in A1).

Go back to Excel and click Tools > Macro > Macros. The macro "showHyperlink" should be highlighted. Click run. Your cells should now display the underlying url or email address.

 

[srsport1]

Worked like a charm! You are awesome...thanks so much!!!!