I was able to look at the workbook you posted.
The following macro will take the existing information in column D and display the email address and the (non-visible) address will start with 'mailto:'
I noticed that cell D1 had mailto twice. Having corrected that the macro appears to work OK.
For example cell D3 will display:
and in the hyperlink, the email address will be:
Here it is:
Dim e_address As Range
Dim h As Hyperlink
For Each e_address In Range("D1:D4000)
For Each h In Worksheets("Sheet1").Hyperlinks
If h.Range.Address = e_address.Address Then
ActiveSheet.Hyperlinks.Add Anchor:=e_address, _
TextToDisplay:=Right(h.Address, Len(h.Address) - 7)
I am sure that you are testing options on backups of your data, as changes made by macros cannot be undone with the Undo function.