I saw a previous post about sorting odd and even numbers, and the formula
generates an "E" or an "O". I need to sort odd and even street addresses. I
could extract the number out of the address and do it that way, but if I just
want the formula to examine the first 1 thru 5 characters in the street
address string and generate the E or O, how would I do that? Is there a way
to examine just numbers and not other text, since I may have house numbers
from 1 to 5 digits?

Hi Dino

What separates Number from the rest of the address?
If it is a Space, then
=IF(A1="","",IF(MOD(--(LEFT(A1,FIND(" ",A1)-1)),2)=1,"O","E"))

If it is a comma, then
=IF(A1="","",IF(MOD(--(LEFT(A1,FIND(",",A1)-1)),2)=1,"O","E"))
--
Regards
Roger Govier

"Dino" wrote in message
...
I saw a previous post about sorting odd and even numbers, and the formula
generates an "E" or an "O". I need to sort odd and even street addresses.
I
could extract the number out of the address and do it that way, but if I
just
want the formula to examine the first 1 thru 5 characters in the street
address string and generate the E or O, how would I do that? Is there a
way
to examine just numbers and not other text, since I may have house numbers
from 1 to 5 digits?

You don't say what the delimeter is so this works for any

=IF(MOD(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$5)))),2)=0,"E","O")

Works for up to 5 leading numbers.

Mike

"Dino" wrote:

I saw a previous post about sorting odd and even numbers, and the formula
generates an "E" or an "O". I need to sort odd and even street addresses. I
could extract the number out of the address and do it that way, but if I just
want the formula to examine the first 1 thru 5 characters in the street
address string and generate the E or O, how would I do that? Is there a way
to examine just numbers and not other text, since I may have house numbers
from 1 to 5 digits?

thanks to both of you for the solutions, the formulas worked great. The
spreadsheet I was referring to has spaces separating the address items, but I
get data from various sources and some do separate by commas.

Dino


"Mike H" wrote:

You don't say what the delimeter is so this works for any

=IF(MOD(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$5)))),2)=0,"E","O")

Works for up to 5 leading numbers.

Mike

"Dino" wrote:

I saw a previous post about sorting odd and even numbers, and the formula
generates an "E" or an "O". I need to sort odd and even street addresses. I
could extract the number out of the address and do it that way, but if I just
want the formula to examine the first 1 thru 5 characters in the street
address string and generate the E or O, how would I do that? Is there a way
to examine just numbers and not other text, since I may have house numbers
from 1 to 5 digits?