What?
This is a quick reminder for myself on opening a CSV in MS Excel but without converting long numbers into exponential notations (which are incorrectly rounded anyway).

My Data:

Account ID,Name,Phone,Record ID
1457102000000135533,Joel,00441234567890,1457102000000165989
1457102000000135566,Stanley,01709123456,1457102000000167543
1457102000000135608,David,00331234567890,1457102000000166795

My Excel:

Account ID	Name	Phone	Record ID
1.4571E+18	Joel	4.41235E+11	1.4571E+18
1.4571E+18	Stanley	1709123456	1.4571E+18
1.4571E+18	David	3.31235E+11	1.4571E+18

How?
Firstly, save the CSV file as text file (*.TXT).

What?
This is an article to remind me how to search a column in an Excel file for values found in another column (in this example, on another worksheet in the same workbook).

How?
So for demonstration purposes, I'm using a new Excel file with two worksheets called "Sheet1" and "Sheet2" respectively.

What?
This article serves to explain how to split a spreadsheet consisting of multiple sheets into separate files per sheet.

Why?
The Excel file in question was about 36000 rows and had a file size of about 11Mb. In order for an import process to work, the import would only accept XLS files no greater than 1Mb. So our script has to split a single spreadsheet into multiple worksheets of 3000 rows each, and then output each sheet to a separate file that is formatted as XLS (Not *.xlsx).

How?
In summary, we are going to move the Excel file to a folder of its own. We're going to run two VBScripts in two stages, firstly to split the specified rows into sheets, then each sheet into a file each. And we want all the files generated to be created in the same folder.

What I have:
A CSV file exported from Excel along with double-quotes

label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects

var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
];

What I want again:

• Read a CSV file already uploaded with JavaScript
• Populate a JS array with each row
• Account for strings containing double-quotes (and commas to ignore)
• Sort the resulting object array

How?

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

How?
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

What?
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.

How?

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
   Next
   End Sub

5. Press F5 and click “Run”
6. Get out of VBA (Press Alt+Q)

---

What?
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...

Why?
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.

How?

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

Why?
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!

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