Pages

Tuesday, November 24, 2015

Prevent Your Excel File by Automatic Creation of Hyperlinks

A hyperlink is important but in few cases it becomes tedious. Automatic creation of hyperlink is like a speed breaker in the document creation process. It creates with the following prefixes:

  • http:// 
  • ftp://
  • file:
  • www.
  • mailto:
  • news:
  • \\

Suppose, you are working with large the number of excel files and each file consists of thousands of hyperlinks then what will do? I think you will try to remove manually but here, we are talking about thousands of hyperlinks. In this case, try these options:

Apostrophe (‘) at the starting of the cell entry: To understand this option. Let’s take an example. Suppose you are trying to write www.google.com in the cell. When you write it then, it becomes hyperlink automatically. In this case, write an apostrophe (‘) at the beginning of the cell entry.
‘www.google.com 
It won’t become hyperlink. This is the first way to prevention of automatic hyperlink. The second option is more interesting which is given below:

Using autocorrect options: Simply follow these simple steps:

  • Go to the File-> Options -> Proofing -> Auto-Correct Options.
  • Click on the AutoFormat As You Type.
  • Uncheck the option “Internet and network paths with hyperlinks”


Using Macro: If you have the coding skills then you can easily try this method and if you are doing this first time then follow these steps:
  • Open the excel file and press Alt+F11 to start the visual basic editor.
  • In the left hand pane, you can see the worksheets of the excel files.



  • If you want to remove the hyperlinks of Sheet1 then double click on it.
  • Now, write the following code using project explorer:
  • Sub RemoveHyperlinks()
    ActiveSheet.Hyperlinks.Delete
    End Sub 


  • Save the file.
  • Go to the excel sheet, Press Alt+F8 to run the macro. Click on the run button.

  • All the hyperlinks will be deleted easily.


Using paste special option: Paste special option is also used to remove the hyperlinks. Follow these steps:
  • Write 1 in a blank cell.
  • Copy the cell.
  • Select the cells with hyperlinks.
  • Now select the Paste Special.
  • Choose the multiply option.


  • Click OK.

Now the hyperlinks will be deleted off the selected range.
Note: This option will not work MS Excel 2007 and the previous versions. 

Final words: MS Excel does not provide any built-in method to fix this problem. In this article we have seen four methods to remove the automatic hyperlinks.   

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...