Solved

Changing hyperlinks to plain text

Posted on 2010-09-19
27
445 Views
Last Modified: 2012-05-10
When I copy/paste from a webpage with links, those links remain as links when pasted into Excel or MSWord.  Once I've done that, how can I select the entire document or worksheet and convert all links to plain text at once?
0
Comment
Question by:yodercm
  • 11
  • 5
  • 5
  • +2
27 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33713636
In Excel

- Press ALt & F11 to go to the VBE
- View  ..... Immediate Window
- in the Immediate window copy and paste in the code below and hit Enter

Cheers

Dave

ActiveDocument.Hyperlinks.Delete

Open in new window

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33713638
And for Word from http://www.vbaexpress.com/kb/getarticle.php?kb_id=748

Regards

Dave

ption Explicit 
 
Sub KillTheHyperlinks() 
     '   -----------------------------------------------
     '   Removes all hyperlinks from the document:
     '   Text to display is left intact
     '   -----------------------------------------------
    With ThisDocument 
         
         '       Loop while there are hyperlinks afoot!
        While .Hyperlinks.Count > 0 
            .Hyperlinks(1).Delete 
        Wend 
         
    End With 
     
     '   Shut this off, don't need anymore popping up
    Application.Options.AutoFormatAsYouTypeReplaceHyperlinks = False 
End Sub 
 
 
Sub KillTheHyperlinksInAllOpenDocuments() 
     '   -----------------------------------------------
     '   Removes all hyperlinks from any open documents
     '   Text to display is left intact
     '   -----------------------------------------------
    Dim doc As Document 
    Dim szOpenDocName As String 
     
     
     '   Loop through all open documents:
    For Each doc In Application.Documents 
         
         '       Store the document name
        szOpenDocName = doc.Name 
         
         '       Remove the hyperlinks from that document
        With Documents(szOpenDocName) 
             
             '           Loop while there are hyperlinks afoot!
            While .Hyperlinks.Count > 0 
                .Hyperlinks(1).Delete 
            Wend 
             
        End With 
         
         '       Shut this off, don't need anymore popping up
        Application.Options.AutoFormatAsYouTypeReplaceHyperlinks = False 
         
    Next doc 
     
End Sub

Open in new window

0
 
LVL 5

Accepted Solution

by:
ydsonline earned 300 total points
ID: 33713743

Just as an alternative to coding you delete the hyperlink style for that document:
You can do this in the following way:

For Word 2007:
  1. Click on the Home Ribbon Tab
  2. Click the little arrow in the bottom right corner of the Styles Section
  3. The Styles List will appear on the right hand side
  4. scroll down until you find the "Hyperlink" style
  5. Click the arrow on the right of the Hyperlink Style and select: "Delete Style"
For Word 2003 :
  1. Click on the "Format" Menu > "Styles and Formatting"
  2. The Styles and Formatting list will appear on the right
  3. Find the "Hyperlink" Style
  4. Click the arrow on the right of the Hyperlink Style and select: "Delete Style"
The above steps will delete all hyperlinks for that document.
The only problem is that if you type or a new hyperlink and press enter, the hyperlink style will reappear. You can then just delete the hyperlink style again. This problem does not seem to occur when pasting hyperlinks after the hyperlink style has been deleted.
A similar procedure can be followed for Excel as well.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33713916
@ydsonline:  

That worked just fine for Word 2007, although I had to Clear All before the Hyperlink Style would appear in the list.  

But when I tried to do something similar for Excel, it didn't work.  I selected the cells, clicked Cell Style, then tried to delete the Hyperlink style.  It changed the appearance, but the text in the cell was still a link.  I also tried to change the style to Normal, same thing, the appearance changed to plain text, but the text was still a link.   Can you be more explicit about the Excel method?
0
 
LVL 12

Assisted Solution

by:tilsant
tilsant earned 200 total points
ID: 33713947
In Excel,

You can simply copy the entire sheet and then go to Edit >> Paste Special >> Values


HTH
Tils.
0
 
LVL 5

Expert Comment

by:ydsonline
ID: 33713970
My Apologies yodercm, I can see what you're saying about Excel. Tilsant's suggestion 'Pasting Values' is probably a better bet for excel.
Just regarding word, you should not have to clear all. I'm not sure what you mean be clear all, do you mean clear all Styles? As long as their is one hyperlink in the document, the hyperlink style should show up, most likely right at the bottom of the list.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33713972
@tilsant:

I had to create a second worksheet, it wouldn't paste onto itself, but I can live with that.  It does mean that I can't have formulas on the same sheet, though.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33713995
@ydsonline:

Yes, I have attached a pair of screenshots, before and after clicking the "Clear All" at the top of the list.
Word-Styles.jpg
0
 
LVL 5

Expert Comment

by:ydsonline
ID: 33713998
Looks like you can, just past as Formulas rather than values, and it removes the hyperlink,
0
 
LVL 5

Expert Comment

by:ydsonline
ID: 33714001
Sorry my above comment was in relation to excel
0
 
LVL 5

Expert Comment

by:ydsonline
ID: 33714004
regarding word, try not to highlight everything, just click somewhere in a blank spot and then see if the hyperlink style shows up
0
 
LVL 12

Expert Comment

by:tilsant
ID: 33714008
yodercm, aren't you copying data from a webpage? So, how can you have formulas?!

You must be using some formula after that, i suppose! In that case, once you have copied the data from webpage to the excel sheet. Do copy-pastespecial at this stage and then use formulas.


Tils.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33714012
Pasting Formulas works, so that will be fine.  In Word, it doesn't make any difference whether I highlight or not, same thing happens.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:tilsant
ID: 33714017
Btw... ydsonline's suggestion of doing PasteSpecial >> Formula works as well.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33714022
@tilsant:  Yes, that works, and usually it's just the webpage, but I just commented that if I did have formulas, I'd have to worry about them.  Pasting Formulas solves both.
0
 
LVL 27

Author Comment

by:yodercm
ID: 33714027
OK, I seem to have ways to do this for both Excel and Word, although I have to say to MS what a nightmare that you can't just click a "remove hyperlinks" button.

Thank you all for the excellent suggestions!
0
 
LVL 27

Author Comment

by:yodercm
ID: 33714029
@brettdj:

Your suggestions seem way beyond what I wanted to do, and too complicated to even think about :)  But I do thank you for trying to help me :)
0
 
LVL 12

Expert Comment

by:tilsant
ID: 33714036
Also regarding pasting on the same sheet, you will face trouble if your sheet has some merged cells. So, best bet would be to paste it onto a different sheet.
0
 
LVL 12

Expert Comment

by:tilsant
ID: 33714041
<<although I have to say to MS what a nightmare that you can't just click a "remove hyperlinks" button>>


You can create a customized button for yourself to remove hyperlinks with the help of suggestions given by Brettdj :)
0
 
LVL 27

Author Comment

by:yodercm
ID: 33714046
Now that's a thought!  Maybe MS should read what Brettdj wrote and follow his suggestion.  :)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33714184
> Your suggestions seem way beyond what I wanted to do, and too  complicated to even think about :)

Well they automate a manual process - which in particular is what 33713947 does (although the code just removes hyperlinks, the comment you accepted will kill all formulas). It's much quicker to run the code on say 50 sheets then to copy and paste them

While I can appreciate you may have found the code solutions more difficult (although instructions were provided) please be mindful of earlier contibutions in the future.  I will let this rest as is, as the best answer is the one that helped you, but I note that you didn't post any follow-up notes to my first two posts till the close-out. So the first two answers that would have worked have gone unrewarded given you didn't try them, or let me know that you found them difficult

next time :)

Regards

Dae  
0
 
LVL 27

Author Comment

by:yodercm
ID: 33715332
Dae, I was not unappreciative of your solution, and I made a point to say so.  It was not what I was looking for, and still I seriously considered giving you some points for it.  I know how annoying it is to post an excellent answer and miss out, I've been there many times myself.  

The reason I didn't was simply because the other two people understood what I was asking, my level of understanding to have to ask in the first place, and followed up with help to make it work, so I felt giving points for an answer I didn't use would short them.  

There may come a time when I find the motivation, interest, and need to learn to do the code method.  Just so you don't think I ignored you, I did in fact try the Immediate Window solution you gave for Excel.  It gave me an error, see attached screenshot.  

Since there were simpler alternate solutions posted, I tried them.  The followup questions were easier for me to ask, and the answers didn't require me to deal with unknown errors or read articles or try to understand code in a language I don't know (it looks like javascript which I despise, that didn't help much either :) ).

If you wish to use the Object button and request some of the points, and the monitors agree you deserve some, I won't argue with it.

Thank you again for trying to help.

Cheers,
Cornelia
ImmediateWindowError.jpg
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33715699
For Excel that should actually have been:
Activesheet.Hyperlinks.Delete


the ActiveDocument version works in Word. :)
0
 
LVL 27

Author Comment

by:yodercm
ID: 33715927
Thank you, rorya, at least that clears that part up.  :)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33716822
> Activesheet.Hyperlinks.Delete

Goddam

There are my poor copy and paste skills on display!

>  the ActiveDocument version works in Word. :)

Actually no it doesn't. That is my failed Word test, which is why I went with my other link to vbax with longer code.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33717057
Thanks for the follow-up Cornelia :)

I'm fine as is given you as Asker are best placed to know what worked for you. It was meant to be a gentle reminder for early feedback for different routes thats all

fwiw I couldnt get the Paste Special .. Formulas path to work in either xl2003 or xl2007 to remove links. This more detailed approach using the Paste Special Formulas Multiply trick didnt work for me either, the web links proved resilient
   http://excel.tips.net/Pages/T002373_Removing_Hyperlinks_without_a_Macro.html

Cheers

Dave
0
 
LVL 27

Author Comment

by:yodercm
ID: 33717176
I got the Paste Special Formulas to work just fine as long as I pasted into a new worksheet.  The links were changed to plain text and the formulas preserved.  I have Excel 2007.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now