Wrong result from formula - please evaluate

[waldetta]

PLEASE EXCUSE MY NOT FOLLOWING PROPER PROTOCOL FOR SHOWING MY EXAMPLE, I AM
IN A HURRY AND IT IS MORE LOGICAL TO ME THIS WAY:

=IF(LEN(D1855)<=17,IF(LEN(B1855<=7),IF(FIND("STE",B1855,1),CONCATENATE(D1855," ",B1855))),"")

Column A contains LEN(B1855) (EXCEL returned value 8)
Column C contains LEN(D1855) (EXCEL returned value 17)
Column B and D are simple data (text)

A B C D E

8 STE 1234 17 123 S CENTRAL AVE 123 S CENTRAL AVE STE 1234

I am tracking field length as I revise a huge database (30k rows of customer
data) and only have 25 CHAR fields to work with when I upload back to our
Company Database program

PROBLEM: I should be getting a FALSE result, because the LEN of Column B IS
NOT <=7, it is 8. Yet, the formula continues to the end. When it Concats, I
end up with 26 characters in Column E.

Thank you! Melinda

 

[Bernie Deitrick]

One problem that is immediately apparent:

IF(LEN(B1855<=7),

should be

IF(LEN(B1855)<=7,

and you should also set the returns for FALSE:

=IF(LEN(D1855)<=17,IF(LEN(B1855)<=7,IF(FIND("STE",B1855,1),CONCATENATE(D1855," ",B1855),"STE not
found in B"),"B > 7"),"D > 17")

HTH,
Bernie
MS Excel MVP

 

[Spiky]

PLEASE EXCUSE MY NOT FOLLOWING PROPER PROTOCOL FOR SHOWING MY EXAMPLE, I AM
IN A HURRY AND IT IS MORE LOGICAL TO ME THIS WAY:

=IF(LEN(D1855)<=17,IF(LEN(B1855<=7),IF(FIND("STE",B1855,1),CONCATENATE(D1855," ",B1855))),"")

Column A contains LEN(B1855) (EXCEL returned value 8)
Column C contains LEN(D1855) (EXCEL returned value 17)
Column B and D are simple data (text)

A B C D E

8 STE 1234 17 123 S CENTRAL AVE 123 S CENTRAL AVE STE 1234

I am tracking field length as I revise a huge database (30k rows of customer
data) and only have 25 CHAR fields to work with when I upload back to our
Company Database program

PROBLEM: I should be getting a FALSE result, because the LEN of Column B IS
NOT <=7, it is 8. Yet, the formula continues to the end. When it Concats, I
end up with 26 characters in Column E.

Thank you! Melinda

You have a parenthesis in the wrong place in the 2nd LEN:
=IF(LEN(D1855)<=17,IF(LEN(B1855)<=7,IF(FIND("STE",B1855,1),CONCATENATE(D1855,"
",B1855))),"")

 

[waldetta]

Bernie, Bernie, Bernie....

I will tell the MS EXCEL world I am an idiot! I have looked at this formula
(and about 25 others, each doing a different part of my SEARCH CLEAN and
DESTROY that I simply missed it.

BUT! After writing this, I realized that the actual goal is to not exceed
25 CHARS, so I modified to take a total of the two fields, using and IF(AND).
Now it is better.

Thank you!

=IF(LEN(I15)<=17,IF(LEN(G15)<=7,IF(FIND("STE",G15,1),CONCATENATE(I15,"
",G15),"")))

Thank you!

 

[waldetta]

Thak you Spiky.. I am bleary-eyed from writing these ridiculous formulas, and
I just didn't see it. You can see that I did not forget it in the first IF,
I just missed it the second time.

 

[John C]

You could also try the following formula. This has some error checking as well:

=IF(AND(LEN(D1855)<=17,LEN(B1855<=7),ISERROR(FIND("STE",B1855,1))=0),D1855&"
"&B1855,"")