I have a list of department codes that I need to sort. They are in the
format number, letter, number, number. All end in zero; for example 2C00,
5D00, 2E10. In most cases the last two numbers are zeros. The problem is
with the codes that contain the letter 'E'. I was originally having the
problem of Excel seeing all codes containing the letter 'E' as numbers and
putting them into scientific notation. I subsequently formatted them as text
which fixed the display problem, but now when I sort, all the department
codes that contain 'E' are sorting at the top. For example instead of
getting something like 1A00, 1B00, 1C00, etc., I'm getting 1E00, 1A00, 1B00,
etc. I've tried formatting as both 'text' and 'general', but no luck.

I'm using Excel 2007, but saving file as 2003 since not everyone where I
work has been migrated to 2007.

Set format as text first, then place the data in the cells.

LyleB_Austin wrote:
I have a list of department codes that I need to sort. They are in the
format number, letter, number, number. All end in zero; for example 2C00,
5D00, 2E10. In most cases the last two numbers are zeros. The problem is
with the codes that contain the letter 'E'. I was originally having the
problem of Excel seeing all codes containing the letter 'E' as numbers and
putting them into scientific notation. I subsequently formatted them as text
which fixed the display problem, but now when I sort, all the department
codes that contain 'E' are sorting at the top. For example instead of
getting something like 1A00, 1B00, 1C00, etc., I'm getting 1E00, 1A00, 1B00,
etc. I've tried formatting as both 'text' and 'general', but no luck.

I'm using Excel 2007, but saving file as 2003 since not everyone where I
work has been migrated to 2007.

You could use datafilterautofilter and filter on "contains E" or a helper
column with a formula to split out the text and use that for sorting.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LyleB_Austin" wrote in message
...
I have a list of department codes that I need to sort. They are in the
format number, letter, number, number. All end in zero; for example 2C00,
5D00, 2E10. In most cases the last two numbers are zeros. The problem is
with the codes that contain the letter 'E'. I was originally having the
problem of Excel seeing all codes containing the letter 'E' as numbers and
putting them into scientific notation. I subsequently formatted them as
text
which fixed the display problem, but now when I sort, all the department
codes that contain 'E' are sorting at the top. For example instead of
getting something like 1A00, 1B00, 1C00, etc., I'm getting 1E00, 1A00,
1B00,
etc. I've tried formatting as both 'text' and 'general', but no luck.

I'm using Excel 2007, but saving file as 2003 since not everyone where I
work has been migrated to 2007.

Even with the cell formatting, XL is trying to be *helpful* and sort what it
believes is a number as a number. We can "cheat" with a helper column. Insert
a column, and use this formula, copied down:
=LEFT(A2,4)&"Z"

Now XL is forced to treat our values as text. Sort on this helper column,
and you should get your desired results.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LyleB_Austin" wrote:

I have a list of department codes that I need to sort. They are in the
format number, letter, number, number. All end in zero; for example 2C00,
5D00, 2E10. In most cases the last two numbers are zeros. The problem is
with the codes that contain the letter 'E'. I was originally having the
problem of Excel seeing all codes containing the letter 'E' as numbers and
putting them into scientific notation. I subsequently formatted them as text
which fixed the display problem, but now when I sort, all the department
codes that contain 'E' are sorting at the top. For example instead of
getting something like 1A00, 1B00, 1C00, etc., I'm getting 1E00, 1A00, 1B00,
etc. I've tried formatting as both 'text' and 'general', but no luck.

I'm using Excel 2007, but saving file as 2003 since not everyone where I
work has been migrated to 2007.