How To Sort A List Of Vehicle Number Plates In Year Order In Excel 2010

Sun 21st November 2010

Want to sort an Excel 2010 list by extracted data? Find out how to do this by reading this easy to follow article using an example vehicle license plates list and just two functions LEFT and RIGHT

You may think this is a peculiar topic, but it's an interesting one if you're a license plate dealer, or you keep lists of vehicles and you want to list the vehicles by licence plate date. As the UK license system changed in Sept 2001 we'll only look at sorting license plates lists issued after this date. License numbers since then take the form AB53 CRD or LN04 ABC where the first two letters indicate registration location and the following two numbers are the year identifiers. The final part consists of three random letters. We'll exclude personalised number plates which do not conform to these rules. So for these two examples the year identifiers are 53 and 04.

The second of the year identifier numbers identifies the base year. The first number of the year identifier tells you whether the vehicle was registered before or after September of that year. If there is a 0 in the year identifier, the vehicle was registered between March and the following September, and if there is a 5 in the year identifier, the vehicle was registered between September and the following March.

Although this system may seem a little confusing - you may realise that 53 means the registration date was between September 2003 and March 2004 - all we need to know whether the first number is a 0 or a 5. The key point is that for any given year, the identifier 5 is later than the identifier 0. By the way a 6 in the year identifier indicates the vehicle was registered after 2010.

In order to sort the license numbers by year we need to extract the two identifier numbers into separate cells. So, for example, from AB53 CRD we'll extract the identifier 5 to one cell and the base year 3 to another cell. Then we sort the overall license plates list using the base year as the first sort criteria, and the year identifier as the second sort criteria. We'll then see the list sorted in correct year order.

We'll now have a look at how we can extract the two year identifier numbers using LEFT and RIGHT functions and then set the sort order for the entire table use these items.

We'll use the LEFT function to extract the first four characters of each license plate, from the left hand side, and place these values in a new cell. So in the above examples we'll extract AB53 and LN04.

Then we'll use the RIGHT function to extract the identifier and base year numbers, so we'll get the numbers 53 and 04 from these examples. Then we'll again use the LEFT function to extract the year identifier and place this is a new cell, and use the RIGHT function to extract the base year into a another new cell. So for the first example from our data we'll extract in successive cells across from the original data, the values AB53, then 53, then 5, then 3.

Suppose we have these four license plates in separate cells in a column in Excel 2010 starting at cell D4; AB04 UKP, MN56 ABB, GK03 OBB; GG06 RDF. For neatness we can add these labels for the table, so in cell D3 type License, in cell E3 type Extracted, in cell F3 type Identifiers, in cell G3 type Base Year and in cell H3 type Year identifier.

Firstly we'll use the LEFT function to extract from the left, the first four characters of each license details. So in cell E4, which is immediately to the right of the first licence plate, type the following formula =LEFT(D4,4) and press enter. E4 should now show AB04, the extracted first four characters from the left. You can then fill the formula down the other three cells, showing the first four characters from all four number plates.

Then move one further column to the right of the first number plate, to cell F4 and type this formula =RIGHT(E4,2)and press enter. You should now see in cell F4 the year for the first number plate 04. You can again fill this formula down all the license plates and now you'll see you've extracted the years identifiers for each license plate.

Now we use the RIGHT function extract the base year. So in cell G4 type this formula =RIGHT(F4,1) and press Enter. You should now see the single base year number. Fill this formula down the columns so all rows now show the base year for all the license plates.

Lastly we'll extract the year identifier, so select cell H4 and type this formula =LEFT(F4,1) and press Enter. Now you should see the year identifier. Fill the formula down the column as before, and now you'll see all the year identifiers for all the license plates.

Now to complete the task we need to sort the license plates table using base year as the first sort criteria and year identifier as the second.

To do this first select the entire table including all the new headings and all data. So we select D3 to H7. Then in the Data tab click the Sort button. In the Sort panel tick the checkbox My Data Had Headers. Then click the down arrow in the Sort By box and select Base Year. Leave the other two boxes at Values and A to Z. We've now set the first sort criteria as Base Year. For all the common base years we want to sort by the Year identifier. So still in the Sort panel, click Add Level to configure a second level sort. Choose Base Year for this second sort level, leave the other boxes at Value and A to Z and click OK to finish. In the Sort Warning panel choose Sort anything that looks like a number. You then need to click OK twice, as there are two sort criteria. Now your license plates table will sort in the correct year order.

Once you get the hang of this, you'll find that you can combine functions, and you can extract the two numbers into separate cells directly. You may have noticed that the extracted numbers are formatted as text, but the sort still works correctly. You can use the VALUE function to change the numbers to number format if you wish.

Hopefully this article has given you a glimpse into the power of Excel 2010 functions. A really effective way to learn much more about Excel 2010 is to attend a training course and really boost your Excel 2010 skills.

Author is a freelance copywriter. For more information on excel training course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1265-sort-list-vehicle-number-plates-year-order-excel-2010.html