Order in Data Filter list (Excel 2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Order in Data Filter list (Excel 2003 SP3)

Post by steveh »

Good morning

I have a list of airline 3 letter prefix's which start at 001 and go through to 999, for the numbers from 001 to 099 I have had to prefix with a ' to retain the leading 0's. When I apply a data filter though the list starts at 100 and then after 999 I get the numbers '001 to '099, although it looks a bit odd I tried to use OO1 (O's instead of zeros) but they still end up at the bottom.

Does anybody have a solution I could use please?
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
rory
5StarLounger
Posts: 826
Joined: 24 Jan 2010, 15:56

Re: Order in Data Filter list (Excel 2003 SP3)

Post by rory »

Prefix the rest of the numbers with an apostrophe too. Then they will all be text, and list correctly.
Regards,
Rory

matthewR
5StarLounger
Posts: 627
Joined: 03 Feb 2010, 15:02

Re: Order in Data Filter list (Excel 2003 SP3)

Post by matthewR »

Instead of using a ', do the following.

Enter the numbers as usual - they will end up without leading zeros but that's oK.
In the column next to the numbers put: =Text(A1,"000") - I used A1 but use the column where your numbers are.
Copy this formula down.
Then highlight the column with the formula and do - edit, copy, edit paste special and choose values.
Sort on that column and it should sort the way you want.

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Order in Data Filter list (Excel 2003 SP3)

Post by steveh »

Thank you both

Both easy answers that I should have thought of, I think the old age is creeping up on me too fast :-)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Order in Data Filter list (Excel 2003 SP3)

Post by Don Wells »

A speedier implementation of Rory's solution:
  • Select the column
    Pull down Format > Cells (Ctrl + 1)
    Select the Number tab
    Select "Text" from the List
    Click "OK".
Regards
Don

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Order in Data Filter list (Excel 2003 SP3)

Post by steveh »

Cheers Don

I might master this Excel Malarkey by the time I am ninety :-)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin