Loading
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:

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books. Check out ExcelTips Premium 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:
=LEFT(C1,1)&TEXT(MID(C1,2,3),"000")

Regards!

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (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

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

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.