Sort doesn't know that 10 is greater than 2

[mscoxoh]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

In my worksheet, I have a column of numbers in the format of #/##. For instance,

1/1
1/2
2/1
2/2
etc.
2/10
2/11
etc.
2/20

When I sort, 2/10 through 2/19 will come before 2/2. Then 2/20 - 29 comes before 2/3.

One resolution is to change all the x/x to x/0x (i.e. 2/2 to 2/02) but I can't find an effective search/replace to do this. I also tried replacing the '/' with '.' but this makes all the '10' numbers '1' (i.e. 2/10 becomes 2/1) even though I format the cells as text first.

Any ideas, either getting the sort right, or changing the contents to work? BTW, there are never 3-digits after the slash (i.e. 2/100); just one and two digits.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

In my worksheet, I have a column of numbers in the format of #/##. For
instance,

1/1
1/2
2/1
2/2
etc.
2/10
2/11
etc.
2/20

When I sort, 2/10 through 2/19 will come before 2/2. Then 2/20 - 29 comes
before 2/3.

One resolution is to change all the x/x to x/0x (i.e. 2/2 to 2/02) but I can't
find an effective search/replace to do this. I also tried replacing the '/'
with '.' but this makes all the '10' numbers '1' (i.e. 2/10 becomes 2/1) even
though I format the cells as text first.

Any ideas, either getting the sort right, or changing the contents to work?
BTW, there are never 3-digits after the slash (i.e. 2/100); just one and two
digits.

10 in text is NOT greater than 2 in text. As long as the results are text,
the sort is correct. You will have to arrange your data so that it sorts
properly. One way might be to separate the stuff to the left of the slash
from the stuff to the right into 2 columns, Then precede the data with
appropriate number of spaces or zeros and then concatenate the results and
sort that.

 

[mscoxoh]

Thanks for the reply. Yes, I understand the text vs number dynamic; I was looking for a way to do just what you're saying; changing the slash to a dot, or search/replace, but haven't come up with anything. Search/replace with regular expressions would work, but I don't think its supported. We're talking about thousands of lines, so manually changing them all isn't really an option.

 

[Laroche J]

Thanks for the reply. Yes, I understand the text vs number dynamic; I was
looking for a way to do just what you're saying; changing the slash to a dot,
or search/replace, but haven't come up with anything. Search/replace with
regular expressions would work, but I don't think its supported. We're talking
about thousands of lines, so manually changing them all isn't really an
option.

As you've discovered the slash makes it a text value, so sorting is done
according to text rules. You have to separate both parts of the text into
two numbers, to be able to sort according to numbers rules.

Let's say your data is in column A. In two free columns (let's use B and C
here), you'd enter, repectively, in the first row:
=left(A1,find("/",A1)-1)+0
=right(A1,len(A1)-find("/",A1))+0

Copy down the two formulas to the last line of data, then sort by B and C.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[mscoxoh]

=left(A1,find("/",A1)-1)+0

=right(A1,len(A1)-find("/",A1))+0

Very nice. Thanks.