Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Sorting while Ignoring Leading Characters.
Ritesh has a huge amount of data that he needs to process in Excel. Each line in a worksheet includes a part number, in column A, by which he needs to sort the records. The problem is that Ritesh cannot do a straight sort; the first four characters of the part numbers need to be ignored. Thus, the sorting actually needs to start with the fifth character of the part number.
The easiest way to handle this situation is to create a new column in Excel that consists entirely of the information you want to use in the sort. In this case, you could add a new, blank column to the right of column A, and then enter this formula in the first cell of the column (assumed to be cell B2):
=RIGHT(A2,LEN(A2)-4)
You'll notice that cell B2 now contains the part number, beginning with the fifth character. Copy this formula down as many cells as necessary, and then perform a standard sort based on the contents of column B. You end up with the records in the desired order. You can then either delete column B, or you can hide it if you may need it later but don't want it to show up.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3132) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Sorting while Ignoring Leading Characters.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If you need to know how a range of data is sorted, the task is not as easy as you might at first think. This tip examines ...
Discover MoreGot a huge amount of data you need to sort in a worksheet, but Excel doesn't seem to be sorting it correctly? Here's some ...
Discover MoreUsing the sorting tools, on the toolbar, may result in some unwanted results, such as jumbled data. If this happens to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-08-25 11:07:42
Joe M
Thank you, Thank you, Thank you.
This approach for sorting a cell containing letters and numbers of differing lengths
AABBB123
AABBB12345
AABBB123CC
Worked perfectly, saved me tons of time, and made me look like a hero!
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: 
Comments