removing mailto link to display the needed data

[traygiddens]

i have a excel sheet that has various fields on it but i need to modify one Column..

it has a hyperling that say's "click to email" then the source underneath it has "mailto:jack@wherever.com" what i want to do is remove the "click to email" display and remove the "mailto:" part of the source so that i'm left with just the email address "jack@wherever.com" in that column....

How do i accomplish this... it's probably simple , but i don't really use exel too much and i'm stymied...

Thanks...

Tray

 

[traygiddens]

900 times

oh, by the way, i've got to do this 900 times so if there is an automated way to do this it would be preferable

 

[asylum]

bump

 

[Andrew Poulsom]

Try this macro:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim HL As Hyperlink
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    MsgBox Rng.Hyperlinks.Count
    MsgBox Rng.Rows.Count
    For Each HL In Rng.Hyperlinks
        HL.TextToDisplay = Replace(HL.Address, "mailto:", "")
        HL.Delete
    Next HL
End Sub

 

[Charlize]

Another possible solution.

Sub convert_mail_to_text()
Dim rng As Range
Dim cell As Range
'names in column A starting at row 2
Set rng = Worksheets(1).Range("A2:A" & _
          Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In rng
    With cell
        .Hyperlinks.Delete
        .Font.Underline = False
        .Font.Color = vbBlack
    End With
Next cell
End Sub