This is a quick article on how to convert some cells in Microsoft Excel to number values...

OMG. Seriously Microsoft! I have spent an hour trying to convert a column of currency values to a number using Microsoft Excel 2010. Since when did MS Excel stop understanding what a NUMBER was?

I have a column full of currency values which I want to convert, specifically Philippine pesos to British pounds (sterling). When I multiply the Philippine peso by the conversion rate, it returns #VALUE!

The problem is that I have a column which includes the currency symbol as per the following image:

A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new...

Consider the 3 following columns in an Excel spreadsheet:

Old       New       Exists in Old?
--------- --------- --------------
123456    234567
234567    345678
345678    456789
567890    597890
I want the third column to say whether this is new or not.


A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.


  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    End Sub
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)

A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:

A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work