Order in Data Filter list (Excel 2003 SP3)

[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?

[rory]

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

[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]

Thank you both

Both easy answers that I should have thought of, I think the old age is creeping up on me too fast :-)

[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".

[steveh]

Cheers Don

I might master this Excel Malarkey by the time I am ninety :-)