Need Help Converting hyperlink to text

[jspinx]

I have a sample spreadsheet (attached). This spreadsheet has a picture that is hyperlinked to an email address. The real spreadsheet has thousands of entries.

I need to be able to extract the email addresses out of the hyperlinked picture to the next row over. I also need to be able to automate this as doing these one by one will take way to long

Thanks for your help

[JBeaucaire]

This code is designed to extract the email address from each shape in your book. It will put them in Column A of the activesheet, so edit that if that's not where you want it.

Sub ShapeEmailAddresses()
Set MyDocument = Activesheet
    For Each sh In MyDocument.Shapes
        x = sh.Hyperlink.Address
        x = WorksheetFunction.Substitute(x, "mailto:", "")
        Range("A65536").End(xlUp).Offset(1, 0).Value = x
    Next sh
End Sub

[jspinx]

thank you but there isnt a way to do a mass change? I have to copy and paste this in each row one by one?

[JBeaucaire]

??? Did you run the macro? It pulls ALL the email addresses out of ALL the shapes and puts them in a column. Isn't that what you asked for?

If not, please restate your complete need...or post your sheet again showing what your looking for your final result to be.