Remove all hyperlinks within Excel

-

Ever copy a webpage of data into Excel, and it brings along a bunch of hyperlinks? Of course, it isn't practical to remove each hyperlink manually. Fortunately, there are a couple of options to perform a bulk removal of hyperlinks.
Note that I'm using Excel 2007 in my examples.

OPTION 1

  1. Select the cells that contain hyperlinks.
  2. On the Home tab, in the Editing section, click the Clear drop-down arrow.
  3. Click Clear Hyperlinks.
I have found in some cases that the Clear Hyperlinks option isn't available. In those cases, use option 2 below.

OPTION 2

Use the following macro:
Sub NoMoreHyperlinks()

ActiveSheet.Hyperlinks.Delete
End Sub
For those of us that need a reminder on how to create macros, here's a more detailed process.
  1. Press Alt-F8 to display the Macro dialog box
  2. Enter a new macro name (such as "NoMoreHyperlinks") in the text box just below "Macro name:"
    - This will change the Create button from an inactive to active state
    - If you already have one or more macro, the name of one may already be in the "Macro name:" text box. That's ok, just type over it and Excel will realize you're wanting to create a new macro.
  3. Click Create -- this opens the Visual Basic Editor
  4. Copy the lines above and paste into the edit window
    - Note if you didn't use "NoMoreHyperlinks" as a name, you will need to make sure you update the macro code accordingly
  5. Close the editor window -- you should be back on your worksheet
  6. Press Alt-F8 again
  7. Select your new macro and click Run -- all your objects should now be deleted from your worksheet
 That's it!

Comments

Post a Comment

Popular posts from this blog

Digital Signatures in PDF Do Not Print

-
Image
Trouble getting the digital signature in a PDF to print? The solution is actually simpler than you might think. With your document open in Adobe Acrobat Reader, click File >> Print. When the Print dialog box opens, look for the "Comments and Forms:" drop-down below the Properties button, located in the upper-right. Click the drop-down and select "Document and Markups." Now print. That's all there is to it.
Read more

Referencing the value of a cell, not its formula

-
Image
In Excel, I will use formulas quite frequently for my analysis. At times I want to reference the resulting value of the formula for comparison or to feed another formula. Unfortunately, if you are doing a comparison, Excel will try to compare the formula instead of the formulas results. Well, turns out there is an easy way to correct this. If you want your formula result to be a number, then use the VALUE function. If you want the formula result to be text, use the TEXT function. Here is the syntax for each: VALUE(text) TEXT(value,format_text) -- The format_text value can be any format from the Category box on the Number tab (in the Format Cells dialog box) except General. Here is an example of each: =VALUE(MID(A2,46,FIND("&",A2)-46)) -- this actually finds a number beginning with the 46th character of a string, and continues until it finds the ampersand character. By wrapping the formula with VALUE, I can now compare the number to other numbers in my looku...
Read more

CorelDRAW X4 Crash on Startup

-
I had a fresh install of CorelDRAW X4, and each time I tried to start it, it would crash. After some searching through Google, I found the following fix. Open the folder C:\Program Files\Corel\CorelDRAW Graphics Suite X4\Programs\UIConfig\CorelDRAW Edit DrawUI.xml Find the line <dockpage guidref="bc1e2f70-3b58-41cd-8406-aaa550482972" visible="true" selected="true"> Change visible="false" and remove selected="true" <dockpage guidref="bc1e2f70-3b58-41cd-8406-aaa550482972" visible="false"> Save and close DrawUI.xml Fold down F8 and restart CorelDRAW X4 When prompted to update settings, select OK That did the trick for me. Apparently it is caused by a conflict with MFC dlls that are installed (version 1833) with SQL2008.
Read more