Nested IF to evaluate street addresses

M

ManosS

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")))
 
M

ManosS

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")))
 
B

Bernard Liengme

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
 
R

Ragdyer

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")))
 
H

Harlan Grove

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")))
Click to expand...
....

You could shrink this to the following array formula.

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

Bernard Liengme

True but I wanted a more flexible formula in case OP needed more than just
Yea or Nay
 
M

ManosS

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")))
 
Top