converting a column from email link to email address

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Mar 10, 2010

• #2

Hi

Assuming you have no other hyperlinks on this sheet, try running the following code:

Code:

Sub ConvHyperlinks()
Dim i As Long
With Sheets("Sheet1")   'amend as appropriate
    For i = 1 To .Hyperlinks.Count
        .Hyperlinks(1).TextToDisplay = .Hyperlinks(1).Address
    Next i
End With
End Sub

 

Upvote 0

M

mmg0924

New Member

Joined

Mar 10, 2010

Messages

6

• Mar 10, 2010

• #3

I appreciate this reply, but I am a novice .xls user and don't know how to run code. Is there a way to explain? Much appreciated.

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Mar 10, 2010

• #4

Sure:

1. With this workbook open and the sheet in question visible on the screen, open up the Visual Basic Editor by Alt+F11 (ie hold down Alt key and tap F11 key).
2. In the VBE, insert a module by Insert>Module
3. This will open up the code area on the right of the VBE (big white space). Paste the code below into this area:

Code:

Sub ConvHyperlinks()
Dim i As Long
With Activesheet  
    For i = 1 To .Hyperlinks.Count
        .Hyperlinks(1).TextToDisplay = .Hyperlinks(1).Address
    Next i
End With
End Sub

4. Place the cursor somewhere in this code (doesn't matter where) and then tap the F5 key - this will run the macro and will convert your hyperlinks (well, that's the theory!).

 

Upvote 0

M

mmg0924

New Member

Joined

Mar 10, 2010

Messages

6

• Mar 10, 2010

• #5

well thanks! I followed instructions as posted. It converted the 5H cell, which is the first row of the email column (H) but it left the remaining inn the column as hyperlinks showing us as Email
I also tried highlighting the email column (H) and running the code but it wasn't successful. Any other suggestions?

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Mar 10, 2010

• #6

Try this amended code (run after you have selected the cells that contain these emails):

Code:

Sub ConvHyperlinks()
Dim cell As Range
    On Error Resume Next
    For Each cell In Selection
        cell.Hyperlinks(1).TextToDisplay = cell.Hyperlinks(1).Address
    Next 

End Sub

 

Upvote 0

M

mmg0924

New Member

Joined

Mar 10, 2010

Messages

6

• Mar 11, 2010

• #7

Well Thanks!!! That amended code did it! Have a great day

 

Upvote 0

M

mmg0924

New Member

Joined

Mar 10, 2010

Messages

6

• Mar 11, 2010

• #8

I have the email address column converted from the link now (thanks to you!) But here is the next minor issue.

Now the column reads as mailto:mmg0924@gmail.com

Is there a quick fix to remove the characters "mailto:" from the beginning of each cell in this column? This way I would be left with only the email address.

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Mar 11, 2010

• #9

Select the column and go Ctrl+H and in the Find box type "mailto:" (without quotes) and leave Replace box blank and hit Replace All.

 

Upvote 0

M

mmg0924

New Member

Joined

Mar 10, 2010

Messages

6

• Mar 11, 2010

• #10

it worked perfectly - thanks again!

 

Upvote 0