How can I convert every email hyperlink icon in a '.docx' file to simple text?

VuduBalls

Estimable
Oct 30, 2015
5
0
4,510
0


Currently, each email address in the document is behind a hyperlink that's represented by a small white envelope icon (I've attached a picture). I need a contact information sheet that I can print; I need to keep the same text and formatting, and replace the icon with the email address in plain text. I can right-click and choose 'edit hyperlink', copy the email address that's shown, then highlight and paste over its icon, but there are over 1600 of these hyperlink icons...

I'm actually moving all of this information over to an excel file so if anyone knows how to do this via excel or word, either would work, and it would save me DAYS of mind-numbing, carpal-tunnel-inducing data migration.

Thanks!
 

iceblitzed

Estimable
May 12, 2014
694
0
5,960
138
I found a solution. According to this website (I posted the short version): http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlinked-image-graphic-or-icon-on-excel

Extracting a URL from a hyperlinked image, graphic, or icon on Excel is easy!
Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below:

Sub ConvertHLShapes()
Dim shp As Shape
Dim sTemp As String

For Each shp In ActiveSheet.Shapes
sTemp = ""
On Error Resume Next 'go to next shape if no hyperlink
sTemp = shp.Hyperlink.Address
On Error GoTo 0
If sTemp <> "" Then
shp.TopLeftCell.Value = sTemp
shp.Delete
End If
Next
End Sub

Press F5 and click “Run”
Get out of VBA (Press Alt+Q)

This should be the result: http://s24.postimg.org/euk2u7m91/result2.png

A simple find and replace should remove the mailto
 

VuduBalls

Estimable
Oct 30, 2015
5
0
4,510
0


Yeah I think I had a bad URL or something, first thing when I get to the office tomorrow I'll upload the pic to a different host and post the link. But for now here's a link to a yahoo answers post that I made where you can see the pic:

https://answers.yahoo.com/question/index?qid=20151130092441AArE607

Excuse my incompetence but how can I upload a sample document?
 

iceblitzed

Estimable
May 12, 2014
694
0
5,960
138


To upload a sample document, use a file hosting website like www.mega.nz or something like google drive, onedrive, etc. and post the links here. For some reason, yahoo answers doesn't want to work today. I keep getting "Connection Timed Out" Errors.

 

VuduBalls

Estimable
Oct 30, 2015
5
0
4,510
0


Here's a link to the sample document that I just put together demonstrating exactly what icons I'm trying to get rid of. Please take a look and let me know if you find a way to translate them to plain text.

Whether or not you have any luck, I really appreciate the help. Thanks!

https://wlanjvinc-my.sharepoint.com/personal/m_dickey_wlanjv_net/_layouts/15/guestaccess.aspx?guestaccesstoken=eaGVdgaiTllKzM9D%2fdXtcvUX%2bAq8ic1N1F80M%2b59j5c%3d&docid=07ac7d05bb89b44ca863a750453d83612&expiration=12%2f15%2f2015+7%3a00%3a05+PM
 

iceblitzed

Estimable
May 12, 2014
694
0
5,960
138


Is there any way/link that I can edit/download the document.
 

VuduBalls

Estimable
Oct 30, 2015
5
0
4,510
0


Wooow so sorry, I assumed it would let you. Here's a mega upload link:

https://mega.nz/#!wsFHGJzC!OWSngK1UBA_2unrPwKZLnKAhZsZhljwzgFUgTJlNxMg
 

iceblitzed

Estimable
May 12, 2014
694
0
5,960
138
I found a solution. According to this website (I posted the short version): http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlinked-image-graphic-or-icon-on-excel

Extracting a URL from a hyperlinked image, graphic, or icon on Excel is easy!
Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below:

Sub ConvertHLShapes()
Dim shp As Shape
Dim sTemp As String

For Each shp In ActiveSheet.Shapes
sTemp = ""
On Error Resume Next 'go to next shape if no hyperlink
sTemp = shp.Hyperlink.Address
On Error GoTo 0
If sTemp <> "" Then
shp.TopLeftCell.Value = sTemp
shp.Delete
End If
Next
End Sub

Press F5 and click “Run”
Get out of VBA (Press Alt+Q)

This should be the result: http://s24.postimg.org/euk2u7m91/result2.png

A simple find and replace should remove the mailto
 

VuduBalls

Estimable
Oct 30, 2015
5
0
4,510
0


THANK YOU SO MUCH! Flawless victory.
 
Thread starter Similar threads Forum Replies Date
M Apps General Discussion 5
M Apps General Discussion 3
P Apps General Discussion 1
A Apps General Discussion 2
The Paladin Apps General Discussion 4
P Apps General Discussion 2
N Apps General Discussion 1
T Apps General Discussion 31
D Apps General Discussion 1
S Apps General Discussion 1
W Apps General Discussion 1
T Apps General Discussion 1
G Apps General Discussion 1
M Apps General Discussion 2
Z Apps General Discussion 5
velocityg4 Apps General Discussion 8
D Apps General Discussion 3
Reggod Apps General Discussion 1
J Apps General Discussion 1
J Apps General Discussion 3

ASK THE COMMUNITY