Regular Expressions for Shipment Tracking Code Verification

I set up a tracking page recently for orders and realized that since we ship via 3 carriers (UPS, UPSMI, and USPS) I needed to sort out which API to call based on the tracking number (I didn’t want to force the user to remember the tracking method themselves when I could figure it out for them). Using some tracking number regular expressions I found at StackOverflow I compiled the following list. I’ll add more as I find them. I’ve tested all of them and they all seem to work for what I’ve needed.


UPS (1Z numbers)
\b(1Z ?[0-9A-Z]{3} ?[0-9A-Z]{3} ?[0-9A-Z]{2} ?[0-9A-Z]{4} ?[0-9A-Z]{3} ?[0-9A-Z]|[\dT]\d\d\d ?\d\d\d\d ?\d\d\d)\b

UPS Mail Innovations (UPSMI)
MI[0-9]{6}(ABC[0-9]{7}|XYZ[0-9]{4})

USPS (4 Types)
(\b\d{30}\b)|(\b91\d+\b)|(\b\d{20}\b)
^E\D{1}\d{9}\D{2}$|^9\d{15,21}$
^91[0-9]+$
^[A-Za-z]{2}[0-9]+US$

Notes:

  1. UPSMI is a bit strange in the way tracking numbers are set up. The tracking number starts with “MI” followed by your 6 character numeric UPSMI account number (this is different than your UPS account number which normally ends with “TT”). After that, it’s up to you to determine your format for your tracking number. We’re using one of two 3-character alphabetical codes followed by either a 4 or 7 character numeric code (that’s the “(ABC[0-9]{7}|XYZ[0-9]{4})” you’ll see in the regex). While the “MI[0-9]{6}” part is standard, I recommend writing and testing your own suffix with RegExr.

UPS Mail Innovations Sorting Facilities Codes

UPS Mail Innovations (UPSMI) is a partnership between UPS and the USPS where UPS picks up from the shipper, sorts the packages at one of a number of sorting centers throughout the US, ships them to another sorting center closest to the package’s destination, then hands it off to the USPS at one of USPS’ BMC (Bulk Mail Centers, now called NDCs or Network Distribution Centers) or SCF (Sectional Center Facilities). USPS then handles delivery from there. The advantage is that for mid-sized to larger shippers, rates are equal to USPS Media Mail but delivery times are equal to First Class plus one day. I’ll likely write more about UPSMI integration with WorldShip and other applications later.

I recently set up a connection to UPS’ tracking API which works for UPSMI packages as well now but ran into an issue with the information being returned. For standard small-package tracking, UPS’ tracking API returns the city and state for each activity (pickup, sorting, destination, etc.) but for UPSMI I was simply receiving facility codes instead of city and state. It took a while to track down packages which went through each of UPSMI’s facilities, but I eventually did and managed to put together the following PHP array which can be used to convert the facility code to the correct city and state.

USPS BMCs and SCFs don’t return the facility code, so you’re stuck either simply saying “Bulk Mail Center” or “Sectional Center Facility”, or setting up another API to get tracking information from the USPS once the package leaves the UPSMI system.

  $miFacilities = Array(
“BMC” => “Bulk Mail Center”,
“SCF” => “Sectional Center Facility”,
“OHGRV” => “Urbancrest, OH”,
“GATLA” => “Atlanta, GA”,
“TNLVR” => “La Vergne, TN”,
“CAFNN” => “Fontana, CA”,
“NJLOG” => “Logan Township, NJ”,
“WAABU” => “Auburn, WA”,
“MNMEN” => “Mendota Heights, MN”,
“ILCST” => “Carol Stream, IL”,
“UTWVY” => “West Valley City, UT”,
“TXOLL” => “Coppell, TX”,
“NCDHM” => “Durham, NC”,
“CALEA” => “San Leandro, CA”,
“MOKCY” => “Kansas City, MO”,
“AZTOL” => “Tolleson, AZ”,
“CTWDS” => “Windsor, CT”,
“FLORO” => “Orlando, FL”,
“NYEDG” => “Edgewood, NY”
);

I hope it saves someone else out there some time!

Using Conditional Formatting for Zebra Striping in Microsoft Excel

Zebra Striping in Excel is a useful way to make it easier to differentiate between long (page-wide) rows of data especially when each column stores data which looks very similar. Simply put, you use alternating colors to define rows. I’ve found this to be most useful when printing picklists or other lists for use in our warehouse.

Achieving this effect is simple in Microsoft Excel with Excel’s Conditional Formatting tool. In any version of Office after 2003, click on “Conditional Formatting” in the “Home” ribbon and create a new rule. Here the instructions diverge slightly: In Office:Mac choose “Classic” style then “Use a formula to determine which cells to format”, in Office for Windows choose the “Use a formula to determine which cells to format” Rule Type.

At this point the instructions converge again: type in the following formula into the formula box:

=MOD(ROW(),2)

After this, in Office for Windows click on “Format…” to define the format you want to use for every other row. In Office:Mac click on the “Format With” drop down and choose “Custom Format” to define your format.

The formula above uses Modular Arithmetic (the MOD() function) with the row number (the ROW() function gets the current row) as the input and 2 as the divisor. If the result is greater than 0 (any uneven row since uneven numbers can’t divide cleanly by two) the row will be formatted with your defined format, otherwise the result is 0 and the row is left with its current format.

 

Generating EAN/ISBN-13 Check Digits in Excel

When you’re dealing with ISBN-13’s you’ll sometimes need to generate a check digit, either to verify  a number your employees are inputting during inventory, or scanning off a book or product, or possibly you’re receiving input from a publisher which uses the antiquated ISBN-10 standard and you need to convert it to ISBN-13’s.

ISBN-10 Check Digits

First, what is a check digit? On most barcodes or standardized numbers the last character is what’s called a check digit. This number is generated by using a formula on all the other numbers. In ISBN-10’s this number is generated by multiplying each of the first 9 digits by decreasing numbers starting with 10. For example, the number 0330258648 is a valid ISBN-10. To generate the check digit (which in this example is the 8 at the end of the ISBN-10) we use the following formula:

((0*10)+(3*9)+(3*8)+(0*7)+(2*6)+(5*5)+(8*4)+(6*3)+(4*2)) mod 11 = 3 11-3 = 8

What’s going on here is that each of the first 9 digits of the ISBN-10 are being multiplied by a descending sequence of numbers from 10 to 2. The sum of these multiplications are then taken and divided by 11 with the remainder being take (using modular arthimatic here). The remainder plus the check digit must equal 11 to be valid so to find the check digit subtract the remainder from 11. If the result is 11 (11-0=11), the check digit is written as an upper-case “X”.

ISBN-13 Check Digits

Check digit calculation with ISBN-13s is somewhat different. To find a check digit for a certain ISBN-13, for example 9780395489314, you can use the following formula:


((9*1)+(7*3)+(8*1)+(0*3)+(3*1)+(9*3)+(5*1)+(4*3)+(8*1)+(9*3)+(3*1)+(1*3)) mod 10 = 6

10-6 = 4

This works very similarly except that the initial sum is developed by multiplying the first 12 digits of the ISBN-13 by 1 and 3 alternately and then using mod 10 instead of mod 11 do find the remainder of that sum. After that, again, the check digit + the remainder = 10 so 10 – the remainder = the check digit. In this case 4.

Performing the calculations in Excel

To do this, you’ll need to use the following formula:


=B2&(ROUNDUP(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1)),-1)-(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1))))

You’ll notice that this formula refers to cell B2. In cell B2 you’ll need to have one of the following:

  1. The first 12 digits of an ISBN-13 (A useful function here is =LEFT(B1,12) which will simply take the first 12 characters of cell B1).
  2. The first 9 digits of an ISBN-10 with “978” concatenated to the front of it (e.g. 978033025864)

And that’s how to generate a ISBN-13 check digit in Excel!

Notes

  1. Remember that an ISBN-13 is the same as an ISBN-10 with “978” prepended and a different check digit.
  2. If you’re validating an ISBN-13 which has all 13 digits, don’t forget to remove the checkdigit before passing it through the Excel formula, otherwise you’ll end up with an ISBN-14, which doesn’t exist and is definitely not valid.

Kiosk/Silent Printing in Google Chrome

As a web developer, the easiest way for me to solve a problem is normally to develop a web app that takes care of the issue. There are certain advantages to this approach: scalability in that multiple users can access the app simultaneously, accessibility in that it can be accessed from anywhere, and hardware independence meaning if the end user’s computer goes down, all I have to do as IT support is replace their computer. At times, however, there are limitations to this “when all you’ve got is a hammer everything looks like a nail” solution. Most often for me these issues are related to functionality which would be extremely simple in a native application being difficult to achieve from a browser. Thankfully, when I encounter these issues I’m also in control of the end user’s browser and computer.

Most recently, I ran into a situation where I needed a web app to print a packing slip for an online order once that order had been confirmed by the picker and packer. The system I’m using gives the packer easy access to only the computer’s number pad as it was much simpler to repurpose hotkeys–”*”, “/”, etc.–from the number pad than try to find space for a mouse on our packing line. Because of this lack of mouse access, however, I needed the system to be able to print without requiring a click on “Print” in Chrome’s print preview dialogue or the system print dialogue. What I discovered thanks to this page on Vend’s website is that Chrome has a little-known kiosk mode with the option for “silent” printing. That is, printing which doesn’t require the user to confirm the printing.

I have Chrome launching on startup for our packers (on a Windows 7 Starter netbook) and all I had to do was right click on the Chrome shortcut in the startup folder, click “Properties” and add the following flags to the end of the “Target:” field:

–kiosk –kiosk-printing

This means that when Chrome launches it’ll launch in full-screen mode (without that annoying “Press F11 to exit full-screen” message everytime your cursor is near the top of the screen) and when you press CTRL-P or call Javascript’s “window.print();” function it will print the page without needing your user to confirm that print action.

As the name of flag suggests, this would also be an extremely useful solution in a case where Chrome were running in a Kiosk computer.

Notes:

A few things to note if you do use this solution to silent printing in Chrome:

  1. “Print Preview” must be enabled in Chrome. Check “about:flags” and make sure.
  2. Slightly annoyingly, Chrome will still show the print preview for about a second before going ahead and printing so it’s not quite silent printing, more like muted printing, but it does not require user confirmation, which was my goal.
  3. This is not a way to auto-print on a users’s computer if you don’t have access to the machine yourself since it requires modifying Chrome’s launch flags. This is a good thing! Imagine visiting an unscrupulous webpage and having it auto-print itself wasting your ink and paper!
  4. Make sure you have the correct printer selected as the default since that’s the one Chrome will print to.
  5. Thanks to Matthew McIntyre for pointing out in a comment here that you can omit the -kiosk flag if you want to use silent printing but don’t want Chrome to open full-screen. Different scenario to mine, but useful to note.

Jumping Pictures in Microsoft Word

I normally use Adobe InDesign (after abandoning PageMaker a while ago, thankfully!) for print layouts but I recently ended up needing to do a relatively simple, plain-background layout in Microsoft Word (I did this on Microsoft Word:Mac 2011 on a MacBook Pro running Mac OS X, but it should be the same or similar for all versions of Word at least from 2007 on).

In doing this layout I used both the “Behind Text” and “In Front of Text” options from the “Wrap Text” option in the “Format Picture” ribbon. However, whenever I would try to resize or move a picture it kept jumping around! I could go from 2″ wide to 2.15″ wide but not from 2.15″ to 2.25″… it jumped to 2.5″ and moving the images was just as painful.

It turns out that Word has an invisible grid on by default and never having used Word for any significant or even relatively complicated layouts previously I had no idea that the grid even existed! I found this solution from “Rohn007” on this page in Microsoft’s Community site.

All you have to do to solve this issue is go to the “Layout” ribbon (where you would change your page orientation, size, margins, etc.) and under “Grid” uncheck the “Snap Objects to Grid” checkbox. After that, everything worked as expected!

Formula to Convert Fractions to Decimals in Excel

I’ve been working to get a product information file into a format that would allow me to import it into my database and one of the issues I’ve encountered is that this file contains three columns for dimensions: height, width, length. These three contain fractional values in the format “6 1/16” which Excel doesn’t translate well. So, I put together this formula to deal with that column:


=IF(LEN(TRIM(A2)) >0,IF(TRIM(A2)-INT(TRIM(A2)) > 0,INT(LEFT(A2,FIND(” “,A2)))+(INT(MID(A2,FIND(” “,A2),FIND(“/”,A2)-FIND(” “,A2)))/INT(MID(A2,FIND(“/”,A2)+1,LEN(TRIM(A2))-FIND(“/”,A2)+1))),TRIM(A2)),””)

You’ll note that the value I’m looking for is in column A, row 2 and that if the value of A2 is blank I’m letting the result be blank. If you’d prefer the result to be 0 if there is no value in the fractions column, you can change the “” to 0.