Excel: Sort on One Portion of an Account ID

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: My company assigns an account ID to every customer. One portion of the account ID contains useful information, such as a parent company code. The first three digits of the account are used to identify an office. How can I sort on the basis of a portion of the account ID?

  1. Bad design, part of column A indicates an office.

    Strategy: You can insert a new column and use the LEFT function to isolate the necessary digits from the Account field. Here's how:

    1. In the blank column, such as G, enter a heading such as Key.
    2. In cell G2, enter the formula =LEFT(A2,3). This indicates that the new field should contain just the three leftmost characters in the Account field. Note that Excel also offers the RIGHT function to isolate the rightmost characters.

  2. Get the first three characters.
    1. Double-click the fill handle in cell G2 to copy the formula down to all the rows in your data set. (The fill handle is the black square dot in the lower-right corner of the cell pointer.)

    Results: A certain portion of the Account field is now available in a new column. You can now use data tools, such as Sort, Filter, or Subtotal, to isolate certain offices.

    When you need to isolate a portion of the characters in another column, you can do so by creating a temporary column.