How do I sort letters before numbers in Excel?
 

I want to sort contract numbers in Excel the same way we do in our filing
system at work. Our contracts contain both numbers and letters, but excel
won't let me sort the contracts with the letters first. For example:

Excel sorts our contract numbers like this:
123ABC123
ABC123123
ABCABC123 .... and so on

I want to sort them like this:
ABCABC123
ABC123123
123ABC123 .... etc.

Is there any way to do this in excel? I've tried creating new lists, but
they don't seem to make a difference, particularly with the numbers. The
default in excel is set to sort numbers before letters and I don't want it to
just do a reverse order, I want to put the letters before the numbers...

Thank you for your help!

~ RiverGirl

Hi RiverGirl,

You need to create a custom sort list. Go to Tools - Options - Custom
Lists tab.
In the List Entries box enter:
A
B
C
etc all the way down to 0 (or 9 if that is your last number)

Then press OK. This will create the sort list.

Then select your column and select Data - Sort and press the Options button.

Under First key sort order, click the sort list you created. This should do
it.

Thanks

"RiverGirl" wrote:

I want to sort contract numbers in Excel the same way we do in our filing
system at work. Our contracts contain both numbers and letters, but excel
won't let me sort the contracts with the letters first. For example:

Excel sorts our contract numbers like this:
123ABC123
ABC123123
ABCABC123 .... and so on

I want to sort them like this:
ABCABC123
ABC123123
123ABC123 .... etc.

Is there any way to do this in excel? I've tried creating new lists, but
they don't seem to make a difference, particularly with the numbers. The
default in excel is set to sort numbers before letters and I don't want it to
just do a reverse order, I want to put the letters before the numbers...

Thank you for your help!

~ RiverGirl

Hi Michael,

I tried creating a list of my own exactly as you said and for some reason it
didn't work. It also didn't count the numbers at first because they were not
"simple text". When I tried converting the numbers to text, and then sorting
them according to the new list, Excel still puts the numbers before the
letters, even when I format the cells as text. I don't think I did anything
wrong or if I need to do some sort of formatting.... It feels like I've tried
every combination there is...

Thanks for your help!
~RiverGirl


"Michael Gill" wrote:

Hi RiverGirl,

You need to create a custom sort list. Go to Tools - Options - Custom
Lists tab.
In the List Entries box enter:
A
B
C
etc all the way down to 0 (or 9 if that is your last number)

Then press OK. This will create the sort list.

Then select your column and select Data - Sort and press the Options button.

Under First key sort order, click the sort list you created. This should do
it.

Thanks

"RiverGirl" wrote:

I want to sort contract numbers in Excel the same way we do in our filing
system at work. Our contracts contain both numbers and letters, but excel
won't let me sort the contracts with the letters first. For example:

Excel sorts our contract numbers like this:
123ABC123
ABC123123
ABCABC123 .... and so on

I want to sort them like this:
ABCABC123
ABC123123
123ABC123 .... etc.

Is there any way to do this in excel? I've tried creating new lists, but
they don't seem to make a difference, particularly with the numbers. The
default in excel is set to sort numbers before letters and I don't want it to
just do a reverse order, I want to put the letters before the numbers...

Thank you for your help!

~ RiverGirl

This seemed to work on the small sample you provided, but I don't know
how it will work on the entire set. Give it a try and let me know.

Insert a "helper" column to the right of your Contract Number column.
Let's say that your Contract Number column is column A, then insert a
new column B.

Let's also say that A1 contains the heading "Contract Number", then
enter something like "SortCode" as a heading in B1.

Using the above examples, then your first contact number will be in A2.
Then enter this formula in B2. =IF(ISNUMBER(VALUE(LEFT(A2,1))),2,1)

Now copy the formula down all the cells til you reach the bottom of
your contract numbers. This obviously results in a 2 next to each
contract number that begins with a number and a 1 next to each contract
number that begins with a letter.

The key now is sorting the data to get the result you want. Both the
order of sort and proper use of ascending and descending as noted below
is crucial. Here's how:

DataSort
Sort by SortCode Ascending
Then by Contract Number Descending

Once sorted, you can delete the "helper" column B if you like. If
you're proficient at writing VBA code, you can also try your hand at
automating this.

As I said, this worked on the small sample you provided. Let me know
whether or not it gives you the results you want on the entire set.

Hi rivergirl,
Expect you want to to sort like in the sort order that you would
see on mainframes which use EBCDIC instead of ASCII
http://www.mvps.org/dmcritchie/excel/sorting.htm#ebcdic
and are talking strictly about text cell values. The macro
populates a helper column for you to sort.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm