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?
Order in Data Filter list (Excel 2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Order in Data Filter list (Excel 2003 SP3)
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
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
-
- 5StarLounger
- Posts: 826
- Joined: 24 Jan 2010, 15:56
Re: Order in Data Filter list (Excel 2003 SP3)
Prefix the rest of the numbers with an apostrophe too. Then they will all be text, and list correctly.
Regards,
Rory
Rory
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Order in Data Filter list (Excel 2003 SP3)
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.
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.
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Order in Data Filter list (Excel 2003 SP3)
Thank you both
Both easy answers that I should have thought of, I think the old age is creeping up on me too fast :-)
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
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
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Order in Data Filter list (Excel 2003 SP3)
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
Don
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Order in Data Filter list (Excel 2003 SP3)
Cheers Don
I might master this Excel Malarkey by the time I am ninety :-)
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
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