Articles

Add "Command prompt from here" to right-click context menu

: Category: Windows OS; 27 January 2010; Hits: 29431

Applies to:

Microsoft Windows XP

How?
I keep having to do this every year as I reghost all my machines (=restore to sorta factory settings), so instead of googling it and going through other websites that work and don't; I thought I might as well post the way I do it:

Open up windows explorer
Go to Tools -> Folder Options
Click on the File Types tab
Scroll down and select (NONE) Folder as file type
Click on Advanced
Click on New...
For the Action type what ever you want the context menu to display, I use Command prompt from here
For the Application value type c:\windows\system32\cmd.exe (this will be different on winnt, browse if unsure to your cmd.exe program)
Click on OK > OK > Close

Notes:

Added by default since Microsoft Windows 7

Making a CD copy with only one drive

: Category: Windows OS; 23 September 2009; Hits: 28142

This is in the case that you have only one optical drive which is a minimum of a CD Writer and you want to make a copy of a CD.

Insert original CD in the drive
In the CD Drive dialog box, click CANCEL
On the desktop, double-click on MY COMPUTER
Right-click on the CD drive and select OPEN
Go to EDIT > SELECT ALL > EDIT > COPY
PASTE to a temporary folder
Select all the files, right-click on them and select SEND TO > DIRECTCD

: Category: Excel; 23 July 2019; Hits: 14684

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).

: Category: Excel; 14 February 2019; Hits: 27374

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.

: Category: Excel; 11 February 2019; Hits: 35919

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.

: Category: Excel; 05 October 2015; Hits: 28961

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?

: Category: Excel; 06 November 2014; Hits: 21312

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

: Category: Excel; 06 November 2014; Hits: 10951

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?

Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)

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

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

Articles

Subcategories