28 January 2018

Bookmarking PDF documents in the Chrome browser

  1. Bookmark a page by appending "#page=n" to the book's URL. E.g. "http://blah.pdf#page=23". The number, n, seems to be the page number from the first page (usually displayed as "n of m"). Useful for documents without a table of contents.
  2. Set the zoom level by appending "zoom=x" to the document's URL. E.g. "http://blah.pdf#zoom=150". The number is a percentage, so "150" = "150%".
  3. Combine the extensions using "&". E.g. "http://blah.pdf#page=23&zoom=150".

The parameters as the same as the command line switches for Adobe Acrobat (see below) but I was only interested page number and zoom: Parameters for Opening PDF Files

19 December 2016

Internet Explorer 11 Compatibility View

Internet Explorer 11 has a "compatibility view" setting so that you can open web sites or applications that rely on quirks in older versions of the browser. However, you can only specify compatibility view for a top-level domain (i.e. test.com), not a sub-domain (i.e. xyz.test.com) or even a URL (i.e. http://test.com/app), so it's useless if your organization has quirks-mode and standard-mode applications. An alternative is to create a compatibility list in a group policy. We are transitioning away from quirks-mode but the interim is a PIA for users and administrators (who have to explain to users how MSIE works).

12 June 2016

Formulas for Library Catalogue Sort Order

If you use library catalogues, you may notice that book or film titles in English are sorted without the leading article, "A", "An" and "The". For example, this list of titles ...

A Kind of Intimacy
An Awfully Big Adventure
The Girl In The Polka-Dot Dress

... is sorted like this in a library catalogue ...

An Awfully Big Adventure
The Girl In The Polka-Dot Dress
A Kind of Intimacy

To reproduce this sort order in a spreadsheet, create an additional column containing titles without the leading article then sort this column.

Kind of Intimacy
Awfully Big Adventure
Girl In The Polka-Dot Dress

If the title is in cell A1, the formula to transform a title in Excel is this nested IF() formula below, which tests for each possible article at the start of a string and returns the title without the article:

=if(left(A1,2)="A ",mid(A1,3,100),if(left(A1,3)="An ",mid(A1,4,100),if(left(A1,4)="The ",mid(A1,5,100),$B33)))

The equivalent formula is much shorter in Google Sheets because it has regular expressions formulas. The one below simply replaces the leading article in a title with an empty string:

=REGEXREPLACE(A1, "^(A|An|The) ", "")

31 May 2016

Formula to Convert mm/dd/yyyy String to Date

Some data I receive has the date in a string in mm/dd/yyyy format, which is easier to sort or filter in Excel when converted to a date number. If the data is in cell A2 then the formulas for splitting the date string into substrings and creating a date value are:

  • Year: =RIGHT(A2,4). The year is always the last four digits.
  • Month: =LEFT(A2,FIND("/",A2)-1). The month is the one or two digits before a forward slash.
  • Day: =MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1). The day is the digits between the first and second forward slash.
  • Date: =DATE(B2,C2,D2).

Probably the non-obvious bit is finding the second forward slash. The FIND() formula takes three arguments: find_text, within_text, start_num, where start_num is the position to start the search for find_text. Since the month and day numbers are always one or two digits, the second forward slash must be in position 4 ("m/d/yyyy"), 5 ("mm/d/yyyy", "m/dd/yyyy") or 6 ("mm/dd/yyyy"), so starting the search from position 4 will always find the position of the second forward slash.

Later ... I could just use Data, Text To Columns and split the date string into three columns using the forward slash as the delimiter.