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:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Comments for this tip:

Willy Vanhaelen    11 Jan 2015, 07:58
Oeps -:) the result is:
10934-2.2-2E
10934-4.2-35E
10934-4.2-39E
10934-4.2-47E
10934-4.2-48E
10934-4.3-28E
10934-4.3-31E
10934-10-1PP
10934-10.1-8E
10934-11-2PP
10934-11.2-13E
Willy Vanhaelen    11 Jan 2015, 07:54
@Kartik
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:
10934-10.1-8E
10934-10-1PP
10934-11.2-13E
10934-11-2PP
10934-2.2-2E
10934-4.2-35E
10934-4.2-39E
10934-4.2-47E
10934-4.2-48E
10934-4.3-28E
10934-4.3-31E

Delete columns B-D if you like
karthik    10 Jan 2015, 04:54
how to sort numbers like as follow.
10934-10.1-8E
10934-10-1PP
10934-11.2-13E
10934-11-2PP
10934-2.2-2E
10934-4.2-35E
10934-4.2-39E
10934-4.2-47E
10934-4.2-48E
10934-4.3-28E
10934-4.3-31E
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 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

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 © 2015 Sharon Parq Associates, Inc.