If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
odd and even addresses
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? |
#2
|
|||
|
|||
odd and even addresses
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? |
#3
|
|||
|
|||
odd and even addresses
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? |
#4
|
|||
|
|||
odd and even addresses
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? |
Thread Tools | |
Display Modes | |
|
|