Excel won't sort my data correctly

I have 1 column with "item numbers" Which can be both numbers and
letters.

I wan't to sort them as numbers so that all numbers would come 1st and
then item numbers like KK1992991. The thing is that excel seems to
treat all cells like text fields, even thought I have formatted them as
number fields.

Now excel i.e. sorts 101 before numbers 11-99. I you get what I mean
:P.

Excel would sort cells ,1, 2, 10, 101, 111, 121, 399 like:
1
10
101
111
121
2
399


So how can I make excel understand I want it sort them like numbers :o

-Sirritys

Hi Sirritys,

Here's my standard reply for this and similar cases:

=================================================================

Your Numbers don't behave (like numbers)

Niek Otten, May 11, 2006



Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In
short:



Your Numbers look like Numbers, but they really are Text.

Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!



Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!



· Format an empty cell as Number. Enter the number 1 in it. Edit>Copy.
Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your cells are "real" Numbers now

· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of
characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function
to remove them

· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to
remove most of them

· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly aquired from Web Pages). Use David
McRitchie's TRIMALL() function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall



Please mail me any comments, additions or corrections: [email protected]



=================================================================




--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Sirritys" <[email protected]> wrote in message news:[email protected]...
|I have 1 column with "item numbers" Which can be both numbers and
| letters.
|
| I wan't to sort them as numbers so that all numbers would come 1st and
| then item numbers like KK1992991. The thing is that excel seems to
| treat all cells like text fields, even thought I have formatted them as
| number fields.
|
| Now excel i.e. sorts 101 before numbers 11-99. I you get what I mean
| :P.
|
| Excel would sort cells ,1, 2, 10, 101, 111, 121, 399 like:
| 1
| 10
| 101
| 111
| 121
| 2
| 399
|
|
| So how can I make excel understand I want it sort them like numbers :o
|
| -Sirritys
|

Thank you, solved my problem.

Now I have only 1 question: "how in the earth am I supposed to realize
something like that myself?" =)

I'm really thankfull for internet ;-)