Sort list

  • Thread starter Thread starter janey
  • Start date Start date
J

janey

I have a table which I need to sort, e.g.:

2000
2039B
2026
2167
2239
2242

When I run Sort, I get
2000
2026
2167
2239
2242
2039B

Why does 2039B come at the bottom rather than between 2026 and 2167?
 
janey said:
I have a table which I need to sort, e.g.:

2000
2039B
2026
2167
2239
2242

When I run Sort, I get
2000
2026
2167
2239
2242
2039B

Why does 2039B come at the bottom rather than between 2026 and 2167?
Click to expand...

Because numbers are numbers and are sorted numerical. Other data is alpha
numeric which are sorted in order but after numerical data. You could try
adding a column for the alphabetical element and sort on the numerical data
(but include the alpha numerical date in the sort range) and that will be
OK.

Bill R
 
"2039B" is text, and any text is treated by Excel as being greater than the
largest real number, that's why. To sort these like real numbers, use a
helper col,
eg in B1, copied down: =IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1,4)+0))
Then sort both cols A and B by col B, ascending, and you should get the
desired sort in col A. Success? hit YES below
 
Back
Top