Vinai Prakash

What do you make of 12CNGY561RR9806?

Chances are that you’d be thinking this some kind of a joke… Well, it is a code, a serial number. If you open your Toaster, Phone, TV, Laptop or Tablet, you’d find a similar looking, equally difficult to read serial number.

You may be wondering – Why do they make it so difficult? And what is the purpose?

Well, these are serial numbers. Not for humans, more for computers and geeks. If you happen to take your product  to the service centre, the bar code scanner can make sense of this gobbledegook right away!

To make sense, you need to be able to extract certain bits and pieces from this serial number, and analyze them separately.

For example, the first  two characters (12) may mean the year of manufacture – 2012.

The next 2 characters (CN) may mean the country of manufacture- China.

The next 4 characters (CNGY) may mean the product code. Similarly, the last 4 digits (9806) may be the runing serial number of the product.

From such numbers, it is easy to find out the country, batch, make, product, and date of manufacture quite quickly.

But imagine staring a such numbers in an Excel file, and be able to quickly filter, find and select numbers belonging to a specific year, country or Product family.

Well, it can easily be done, using special in built functions in Excel – be it Microsoft Excel 2003, 2007 or Excel 2010 or even Microsoft Excel 2013.

See this Example Excel file for trying it yourself.

To solve this problem, we will use Excel’s inbuilt Text Functions – Left, Right and Mid.

The Left function will extract any characters from the left of a string.  LEFT(‘ABCDEF’, 2) will extract AB.

The Right function will extract any number of characters you need, from the right of a string. RIGHT(‘ABCDEF’, 3) will extract DEF.

And the Mid function will extract any characters from the middle of a string. You just have to specify the starting number, and the number of digits required.

So, MID(‘ABCDEFGH’, 3, 2) begins to extract from the Third character, and extracts 2 characters. Therefore, the characters, “CD” get extracted easily.

Using these 3 simple functions, we can extract any digit, or character from any string in Excel. And this feature has been in Excel since ages…

Enjoy, and all the best!

Do post some comments if this article helped you!

Additional Resources:

• ExcelChamp Video Tutorials on YouTube
• 2 Axis Charts in Excel
• How to Analyze Data Using Pivot Tables in Excel

Cheers,
Vinai Prakash
Founder & Editor, ExcelChamp.Net