Sorting data with the letter 'E' included

[LyleB_Austin]

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.

 

[Bob I]

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

 

[Don Guillett]

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

 

[Luke M]

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.