Excel.Tips.Net ExcelTips (Menu Interface)

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.

Related Tips:

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!


Comments for this tip:

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.
Best regards.
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:

=YEAR(ref)&" "&TEXT(MONTH(ref),"00")

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:


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.