number digits 1-6 not sorting properly

M

m

I have a list w/ numbers, dates and description; attempting to sort the list
in number order but it is not recognizing the difference between e.g., 1400
and 14000 or 1623 and 16233 so it's sorty them together. Any help would be
appreciated. I've changed the format to number, text but still not correct
results.
 
S

Sean Timmons

Sounds like you need to force the cells to number type.

Enter 1 in a blank cell and copy the cell.

Highlight your set of numbers, paste special/multiply.

This will force them to number, and should allow a correct sort.
 
G

Gord Dibben

Changing the format is not sufficient if Excel sees your numbers as text.

You must coerce Excel to see them as numbers.

Format all to General.

Copy a blank cell.

Select the range to change and Edit>Paste Special>Add>OK>Esc

They should now sort as numbers.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Sorting 2
6 digits Unique Number 6
how to sort number properly in excel? 5
excel 2007 sorting issue with numbering scheme 1
Apostrophes and Sorting 3
Sorting Groups of Rows 1
validation and sorting 3
If number in cell does not equal 6 digits, add zeros to the left 2

Top