# Thread: Sorting odd and even (2000)

1. ## Sorting odd and even (2000)

Hi,
I have an Excel list with names, street numbers, cities, etc. I'd like to sort the list by street numbers, with all the odd numbers first followed by all the even numbers. Can anyone suggest a way to do this? Thanks.

Wyley

2. ## Re: Sorting odd and even (2000)

Create a new column in whicjh you can place an indicator for Odd/Even. You can use a number of functions to help such as ISEVEN(), ISODD() or MOD().

For example if your street numbers are in column A enter the following formula in a new column :

<big>=ISEVEN(A2)</big>

which should place a TRUE or FALSE in the Cell. As FALSE sorts before TRUE you can use the new column as your primary sort key and the street number column (A in this case) as the secondary sort key

Andrew C.

3. ## Re: Sorting odd and even (2000)

Thanks very much, Andrew. I used the ISEVEN() function and it worked fine. My street numbers are in a separate column. Just out of curiosity, do you know a way to sort odd/even when the street number and street name are in the same cell?

Wyley

4. ## Re: Sorting odd and even (2000)

The same approcah could be used.

Assuming that the street number comes first and has a following space the following should work :

=ISEVEN(LEFT(A2,FIND(" ",A2)-1))

or if the number has a following comma then

=ISEVEN(LEFT(A2,FIND("<font color=red>,</font color=red>",A2)-1))

The result should give TRUE or FALSE to provide the primary sort key.

Andrew C

5. ## Re: Sorting odd and even (2000)

Hi Folks

Observation.... If these were home addresses, you may need to cater for folks who have no house numbers but instead house names.

Regards
Peter

6. ## Re: Sorting odd and even (2000)

Wyley,

On second thoughts you would need 2 columns, 1 for the odd/even indicator and one to render the number part of the sreet address as a numeric, so that it sorts correctly. 121 would come before 21 if both were text.

Create a column to get the number with the formula =VALUE(LEFT(A2,FIND(" ",A2))), and use the previous approach in another column for the Odd/Even indicator. Then sort with the latter as primary sort key and the numeric value as the second.

Andrew

7. ## Re: Sorting odd and even (2000)

Peter, a sensible observation, but names cannot be sorted on an Odd or Even basis.

Some test could be used to check for the absence of a number, and in that case use a value of zero (even) used.

Andrew C

8. ## Re: Sorting odd and even (2000)

Thanks for these suggestions. Just to keep matters simple, however, I think I'll put the street numbers and street names in separate columns!

Wyley

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•