sort and filter

[JoAnn]

Hello,

I have a coworker who did a filter on her spreadsheet (so the blanks were
not visable) and then did a sort ascending by the sequence number. What she
got was a sort that was like this: 1, 10,100,1000, 491, 3, and so on. What
the heck is going on?!?

 

[Pete_UK]

I suspect those "numbers" are actually text values, so they are being sorted
in a different way than based on the numerical value, rather like b, ba,
baa, baaa, ejb, d (though I don't know why your "3" comes after "491".

You could insert leading zeroes in front of the number (still as text), or
convert the values to actual numbers, if you want a numeric sort.

Hope this helps.

Pete

 

[Jack Gopher]

That is because your numbers are defined as texts, so it sorts them
"alphabetically".

A trick to convert "text numbers" to numbers:

1. Write in some cell the value 1.

2. Copy that cell (press on it Ctrl+C).

3. Select all your numbers that are mistakenly considered as texts.

4. Go into "Paste special" (if it is in older versions of excel it is
through the "Edit" menu. In Excel 2007 it is through the small arrow under
the "Paste" button).

4. Select the "Multiply", and click "OK".

5. Thats it! All the numbers, are now converted to become real numbers.

Good luck!

http://www.free-training-tutorial.com

 

[Mike H]

Hi,

It looks to me as if your co-worker has numbers and text that look like
numbers in this column.

Put a 1 in a spare cell and copy it. Remove the filter and select the entire
data range and then

Edit|Paste Special - Select Multiply and click OK
Delete the 1 you put in the cell, apply the filter and have another go at
the sort.

Mike