Sorting Problems

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

Patrick Simonds

I am trying to sort numerically a column with a mix of numbers and text:

1
2
3 (36 hrs)
4
5 (19 hrs)
12
13 (6 hrs)

but this is what I end up with:

1
2
4
12
13 (6 hrs)
3 (36 hrs)
5 (19 hrs)


Any help?
 
If you split the data into two columns--one column for the number portion and
one column for the parenthetical portion, you can sort by that first column. In
fact, you don't even need that second column!

One way to do that is to insert a column to the right of the original column.

Then select that column
Data|Text to columns
delimited by space
and drop it in the adjacent column.

Now, select both columns and sort by this extra column.
 
And use Skip (Do not import) for the remaining columns in the data|Text to
columns wizard.
 
well, if you'd like to try a formulas approach as well ..

Assuming source data is in col A, row1 down

Put

in B1:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1,SEARCH("(",A1)-1)+0))

in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

in D1:
=IF(ISERROR(LARGE(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(LARGE(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:D1, copy down until say, A100
to cover the max expected data in col A

Col C will return the ascending sort of col A,
while col D returns the descending sort
 
Back
Top