Forum Discussion
Excel not sorting numbers correctly
I have what I hope is a simple problem - all that I need is for excel to sort numbers numerically. Instead, it is sorting them 1, 10, 11 ... 19, 2, 20, etc.
I've tried pasting my raw data as values, I've tried converting to number, I've tried =CLEAN, =TRIM, =SUBSTITUTE, I've tried pasting just the numbers as values, none of it is working. When I do =ISNUMBER, it keeps coming back false no matter what I try.
My function for Stop (clean) is =CLEAN(TRIM(SUBSTITUTE(C2,CHAR(160),"")))
Does the following work:
Change the formula to
=SUBSTITUTE(SUBSTITUTE(C2, CHAR(160), ""), " ", "")+0
4 Replies
Does the following work:
Change the formula to
=SUBSTITUTE(SUBSTITUTE(C2, CHAR(160), ""), " ", "")+0
Hello Hans
I have a similar issue, but do not want to use a formulae. To sort a simple column of alphanumeric items, e.g. T1 T2 T3 .... T30, it sorts all the 1's first, then all the 2's as per above i.e.
T1
T10
T11
.
.
T2
T20
T21
.
.
T30
I would really like the result to be:
T1
T2
T3
.
.
T10
T11
T12
.
.
T20
T21
T22
.
.
T30
My data typically has other columns of co-ordinates, but I like to sort all the data using the first column order.
Is there any standard function that can do this?
I normally pad with zeros after the "T", but it is tedious, e.g.
T01
T02
T03
.
.
T10
Any help will be appreciated.
Regards
I think you do need a helper column with formulas if you want to sort the way you want.
Why do you want to avoid formulas?
- YES thank you!
