Nested IF to evaluate street addresses

I am not sure how to finish this nested if

I have 3 columns of data D1, E1, F1 e.g.
I want to check the first character only to see if it is a number.
However, the data is probably stored as text 123 MAIN ST

If the first character of D1 is not a number, then I want to check
first character of E1; if first character of E1 is not a number, then
check first char of F1.

Any ideas on this?

=IF(OR(D1, 1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(E1,
1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(F1, 1,1,2,3,4,5,6,7,8,9),"YES","NO")))

Correction -- what I currently have is

=IF(OR(D1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(E1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(F1,1,2,3,4,5,6,7,8,9),"YES","NO")))

Try this
=IF(AND(CODE(LEFT(D1))>=49,CODE(LEFT(D1))<=57),"yes",IF(AND(CODE(LEFT(E1))>=49,CODE(LEFT(E1))<=57),"yes",IF(AND(CODE(LEFT(F1))>=49,CODE(LEFT(F1))<=57),"yes","NO")))
If there is a chance that the entries in D1, E1, F1 could have leading
spaces, then replace D1 by TRIM(D1), etc.
Or
try this one
=IF(ISNUMBER(--LEFT(D6)),"yes",IF(ISNUMBER(--LEFT(E6)),"yes",IF(ISNUMBER(--LEFT(F6)),"yes","NO")))

the double negation converts text to number
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ManosS" <[email protected]> wrote in message
news:[email protected]...
>I am not sure how to finish this nested if
>
> I have 3 columns of data D1, E1, F1 e.g.
> I want to check the first character only to see if it is a number.
> However, the data is probably stored as text 123 MAIN ST
>
> If the first character of D1 is not a number, then I want to check
> first character of E1; if first character of E1 is not a number, then
> check first char of F1.
>
> Any ideas on this?
>
> =IF(OR(D1, 1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(E1,
> 1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(F1, 1,1,2,3,4,5,6,7,8,9),"YES","NO")))
>

Try this:

=IF(ISNUMBER(--LEFT(D1)),"formula for D1 is
number",IF(ISNUMBER(--LEFT(E1)),"formula for E1 is
number",IF(ISNUMBER(--LEFT(F1)),"formula for F1 is number","Formula for no
number anywhere")))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"ManosS" <[email protected]> wrote in message
news:[email protected]...
>I am not sure how to finish this nested if
>
> I have 3 columns of data D1, E1, F1 e.g.
> I want to check the first character only to see if it is a number.
> However, the data is probably stored as text 123 MAIN ST
>
> If the first character of D1 is not a number, then I want to check
> first character of E1; if first character of E1 is not a number, then
> check first char of F1.
>
> Any ideas on this?
>
> =IF(OR(D1, 1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(E1,
> 1,1,2,3,4,5,6,7,8,9),"YES",IF(OR(F1, 1,1,2,3,4,5,6,7,8,9),"YES","NO")))
>

Bernard Liengme wrote...
>Try this
>=IF(AND(CODE(LEFT(D1))>=49,CODE(LEFT(D1))<=57),"yes",
>IF(AND(CODE(LEFT(E1))>=49,CODE(LEFT(E1))<=57),"yes",
>IF(AND(CODE(LEFT(F1))>=49,CODE(LEFT(F1))<=57),"yes","NO")))
....

You could shrink this to the following array formula.

=IF(COUNT(-LEFT(D1:F1)),"yes","no")

True but I wanted a more flexible formula in case OP needed more than just
Yea or Nay
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Bernard Liengme wrote...
>>Try this
>>=IF(AND(CODE(LEFT(D1))>=49,CODE(LEFT(D1))<=57),"yes",
>>IF(AND(CODE(LEFT(E1))>=49,CODE(LEFT(E1))<=57),"yes",
>>IF(AND(CODE(LEFT(F1))>=49,CODE(LEFT(F1))<=57),"yes","NO")))
> ...
>
> You could shrink this to the following array formula.
>
> =IF(COUNT(-LEFT(D1:F1)),"yes","no")
>

Thank you both - this worked for me
=IF(ISNUMBER(--LEFT(D2,1)),"yes - address
1",IF(ISNUMBER(--LEFT(E2)),"yes - address
2",IF(ISNUMBER(--LEFT(F2)),"yes - address 3","no")))