cell format weirdness in excel:mac 2008

[lanvendula]

I have a large spreadsheet with many columns and many rows with
various formatting. One column in the spreadsheet is used
specifically for sorting- it is number only (all four digit numbers).
When I sort the spreadsheet, I sort by this column. The last 10 or so
rows I have added to the spreadsheet though- will not sort correctly.
Obviously, they should be in numeric order-- but these rows are coming
up at the top and not in the correct order (i.e. 4407, 4448, 5118,
5127, 4472). I created a new column and copied my number column and
did a paste special- value. Turns out these have some sort of date
formatting on them (although they only show up as dates when I do the
paste special- otherwise they just look like the 4 digit numbers I
enter). I have tried EVERYTHING I can think of to get rid of the date
formatting. I've reformatted the entire column. Reformatted the
affected rows (and then had to go back and adjust the column
formatting). I've even deleted these rows entirely and reentered the
data. But they still have this mysterious hidden date formatting.
Any suggestions?

 

[Bob Greenblatt]

I have a large spreadsheet with many columns and many rows with
various formatting. One column in the spreadsheet is used
specifically for sorting- it is number only (all four digit numbers).
When I sort the spreadsheet, I sort by this column. The last 10 or so
rows I have added to the spreadsheet though- will not sort correctly.
Obviously, they should be in numeric order-- but these rows are coming
up at the top and not in the correct order (i.e. 4407, 4448, 5118,
5127, 4472). I created a new column and copied my number column and
did a paste special- value. Turns out these have some sort of date
formatting on them (although they only show up as dates when I do the
paste special- otherwise they just look like the 4 digit numbers I
enter). I have tried EVERYTHING I can think of to get rid of the date
formatting. I've reformatted the entire column. Reformatted the
affected rows (and then had to go back and adjust the column
formatting). I've even deleted these rows entirely and reentered the
data. But they still have this mysterious hidden date formatting.
Any suggestions?

My guess is that these numbers are being interpreted as text by Excel. What
I suggest you do is 1)Format the entire column you are sorting on as General
or "0000" for 4 digit numbers. Then 2) enter a 1 in an empty cell somewhere.
Copy this cell. Select all the data in the sort key column. Then Go to Edit
Paste Special, and select Multiply. This will convert any text values to
numbers. Then it should sort properly.