If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting data with the letter 'E' included
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. |
#2
|
|||
|
|||
Sorting data with the letter 'E' included
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. |
#4
|
|||
|
|||
Sorting data with the letter 'E' included
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. |
Thread Tools | |
Display Modes | |
|
|