Sorting Letters and Numbers
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 Letters and Numbers.
Let's say that you have a worksheet in which a particular column contains entries such as F1, F2, F3, etc., all the way up to F149. If you need to sort the data in the worksheet based on the contents of this column, the results may disappoint you. Because the first character in each cell is a letter, Excel sorts the column as text.
The upshot is that the cells are sorted in the order F1, F10, F100, F101, F102, etc. In this arrangement, F2 doesn't show up until the sixty-second entry in the sorted list. The reason this happens is because the cells are treated as text. As text, all the cells starting with F1 (there are 61 of them) come before the cells starting with F2.
The only way around this situation is to make sure that the numbers in the cells are front-padded with zeros. In other words, you shouldn't use F1, but F001. You can use the following formula to convert the old format numbers to the new format:
=LEFT(C1,1) & RIGHT("000" & RIGHT(C1,LEN(C1)-1),3)
Now, when you sort by the newly formatted entries, you get the desired results: F001, F002, F003, etc.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2609) 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 Letters and Numbers.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Leave your own comment:
Comments for this tip:
Mangala 10 Jun 2015, 02:09
I am not able to sort alphanumberic number after PW100/13. It shows after PW100/13, PW1000/13, PW1001/13........
It continues the same even in PW101/13, PW1010/13,PW1011/13............
How to sort this?
Willy Vanhaelen 11 Jan 2015, 07:58
Oeps -:) the result is:
Willy Vanhaelen 11 Jan 2015, 07:54
You can try this:
- assuming your numbers are in column A
- insert 3 empty columns: B-D
- use text to column with the hyphen as dilimiter and column B as destination
- now you have 3 additional columns filled with the splitted data
- sort the entire table on 2 levels column C and then column D
- the result is:
Delete columns B-D if you like
karthik 10 Jan 2015, 04:54
how to sort numbers like as follow.
Francesco Piccoli 02 Jan 2013, 06:08
If you want to sort the data of a worksheet based on numerical part of a code, with a constant literal part (at the beginning, as in F1..F149; at the end, as in 1F..149F, or even in the middle, try it), you can remove the literal part of the entry (in any quick way) and then apply again this part as a customized cell's format:
F149 -> 149 -> "F"Standard -> F149
At the end, you will have a column with the same appearance as before (without added zeroes or columns), but you can sort these entries as numbers.
Thomas Papavasiliou 18 Aug 2012, 07:31
A similar approach is also useful if you have dates that you want to sort in year month order.
The formula to use is like:
where (ref) is the reference to the cell containing the date. You can also define any other than the space separator.
Warwwick 16 Aug 2012, 08:26
The column can be left in its original form (more aesthetically readable) by applying the formula to corresponding elements in a new hidden column and sorting on that. e.g., in Z1 put =LEFT(C1,1) & RIGHT("000" & RIGHT(C1,LEN(C1)-1),3)
and copy Z1 down to the rest of column z, hiding column z and sorting all data on column Z, not column C.
Ricardo 16 Aug 2012, 07:20
The same can be done with: