[EXCEL] Removing multiple hyperlinks

Removing a bunch of hyperlinks can be tedious. Imagine the time you have to spend right-clicking on each of those links and selecting ‘remove hyperlink’ on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 hyperlinks? I’m sure that would be enough to scrape out the paint of your right-mouse button.

Below are some simple techniques you can use in order to remove single to multiple hyperlinks on a page:
Removing just one hyperlink
1. Right-Click on the cell and select Remove Hyperlink from the context menu.

Removing hyperlinks using the cell format
1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.
2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove Excel hyperlinks on selected cells the better way
1. Type in any text or number in a blank cell
2. Right-click and select Copy on the context menu.
3. While pressing CTRL, select each cell with the hyperlink you wish to be removed
4. On the Edit menu, select Paste Special.
5. Under Operation, click Multiply and then click OK.
6. If you are using Office 2007, you may need to click or change the cell format to Normal found at the Styles Group Toolbar.

Removing Excel hyperlinks using a macro
Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.
1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:


Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

4. Save your work.
5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros
6. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.

Removing the Default Hyperlink Option
The most efficient way to remove them is by actually avoiding them in the first place. Now you don’t need to follow those hard and confusing steps above anymore. You can prevent Excel from formatting URLs and network links by removing its AutoFormat function at the AutoCorrect dialog box. I am using Office 2007, see the image below:

Unfortunately, if you are using Office 2003 or lower, i can’t give you the exact steps but the AutoCorrect is at Tools on the main menu bar. I just don’t remember where to go from there. If you are using Office 2000, there is no way we could prevent URLs from being formatted.

[EXCEL] Removing All Macros

Is there a way to get rid of all the macros in an Excel workbook, without the need to individually delete them? There are two ways you can accomplish this task. The first approach is used if you don't want to mess with the macros at all. Just follow these steps:

1. Unhide any worksheets that may be hidden.
2. Select all the worksheets in the workbook. (Click on the first worksheet tab, then hold down Shift as you click on the last worksheet tab.)
3. Right click on one of the worksheet tabs. Excel displays a Context menu.
4. Choose Move or Copy from the Context menu. Excel displays the Move or Copy dialog box. (See Figure 1.)

Figure 1. The Move or Copy dialog box.
5. Using the To Book drop-down list, choose (new book).
6. Make sure the Create Copy check box is not selected.
7. Click on OK.
8. Rehide any worksheets you unhid in step 1.

Your worksheets have now been moved to a new workbook—one that does not have any macros attached to it.
The second approach is to simply work with the existing workbook, and is a viable choice if you feel comfortable with macros in the first place. Follow these steps:

1. Press Alt+F11 to display the VBA Editor.
2. In the Project Explorer (upper-left corner of the Editor), right-click on a module that you want to delete. (Remember that macros are stored in modules, and that you should only right-click on a module that is associated with the workbook that you want to cleanse.) Excel displays a Context menu.
3. Choose the Remove option from the Context menu. The actual wording of the option will include the name of the module you want to remove, such as Remove Module1.
4. When asked if you want to export the module before removing it, click on No.
5. Repeat steps 2 through 4 for any other modules you want to remove.
6. Close the VBA Editor.

[mssql] How to get length of Text, NText and Image columns in SQL Server

Problem
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database.  You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

SolutionIn addition to the LEN() function, SQL Server also has a DATALENGTH() function.  This function can be used on all data types in your table.

Here is an example of running these commands on an IMAGE data type using the LEN function:

SELECT name, LEN(packagedata)
FROM dbo.sysdtspackages

query results


Here is an example of running these commands on an IMAGE data type using the DATALENGTH function:

SELECT name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages

query results



If you wanted to find out the maximum length used for all of your records you could issue a command such as the following:

SELECT TOP 1 name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages
ORDER BY 2 DESC



That is all there is to it.  Another little function that you probably won't use often, but this is helpful to know it is there when you do need to use it.

[mssql] ^ (Cap) is not the same with SQUARE()

Never use the ^ (cap sign) to replace the SQUARE() function.

SELECT 1024 ^ 2

result: 1026

SELECT SQUARE(1024)

result: 1048576

You see the difference!