Category Archives: Excel

How to find a question mark (?) or other wildcard symbol when searching in Excel

Failed solution:

You use Find & Replace to try and find the question mark (or other symbol like percentage % or asterix *) only to realise that Excel considers it to be a wildcard character (i.e. like a blank tile in scrabble, it can mean absolutely anything) and so every single letter in every cell gets highlighted.

Actual solution:

You need to ‘escape’ the character and to have Excel treat it like a real symbol not a wildcard by placing a ’tilde’ symbol (~) in front. As an example with the question mark, the picture below shows its use:

Excel find and replace wildcard with tilde

Bulk Extract Hyperlink from Excel Cells

Alas there is no pre-defined function for extracting a hyperlink from an Excel cell. Therefore the easiest way to accomplish this is to create our own function using a macro.

Within Excel, open up the VBA Macro Interface:
Alt + F11

From the VBA interface, goto the menu and select:
Insert -> Module

On the module screen that opens up, paste in the following text:

Function HyperExtract(content As Range) As String
    On Error Resume Next
    HyperExtract = content.Hyperlinks(1).Address
End Function

Save your spreadsheet (you’ll now need to save it as a *.xlsxm format) and close the VBA screen.

Voila!

How to remove a formula but keep the value in an Excel spreadsheet

When you delete a cell in an Excel spreadsheet, both the formula and value are deleted. If you want to keep the value, then you can delete the formula only.

Procedure:

  1. Select the cell or group of cells you wish to remove the formulae from.
  2. Select the ‘Home’ tab from the menu bar.
  3. Click ‘Copy’. (You can alternatively do this using the keyboard shortcut: Ctrl+C.)
  4. Press the down arrow under ‘Paste’ and click ‘Paste Values’. (You can alternatively do this using the keyboard shortcut: Ctrl+V instead, then a small icon should appear in the corner of the pasted area with ‘Paste Values’ available within a context menu.)