Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
RiverGirl
 
Posts: n/a
Default 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
  #2   Report Post  
Michael Gill
 
Posts: n/a
Default

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

  #3   Report Post  
RiverGirl
 
Posts: n/a
Default

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

  #4   Report Post  
Paul D. Simon
 
Posts: n/a
Default

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.

  #5   Report Post  
David McRitchie
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you sort words in Excel by the number of letters in a word Kinger New Users to Excel 2 May 2nd 05 11:42 PM
How to convert Excel imported numbers from text to numbers? Alden Excel Discussion (Misc queries) 9 April 1st 05 09:51 PM
How do I sort for the maximum values in each year in excel? The Wrightster Excel Discussion (Misc queries) 3 February 24th 05 06:43 PM
Columns in Excel are numbers instead of letters, how do I change . barnes76 Excel Discussion (Misc queries) 5 February 14th 05 02:07 AM
Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . Dan W Excel Worksheet Functions 0 December 1st 04 04:53 PM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"