Forum Discussion

Copper Contributor

Feb 08, 2018

Solved

Need an Excel Formula giving True or False based on the last word in the cell

Checking online I've found these two formulas: =IF(COUNT(SEARCH({"Avenue","Road"},D2)),"TRUE","FALSE") - This returns TRUE or FALSE if either word is anywhere in the cell. =RIGHT(D2,LEN(D2)-FIND...

Formulas & Functions

Need Help

Willy Lau
Feb 09, 2018
I tried another way. The following formula do not need CSE too.

=IF($D2="","",SUMPRODUCT(N(RIGHT($D2,LEN(Sheet2!$A$1:$A$5))=Sheet2!$A$1:$A$5))>0)

Willy Lau

Iron Contributor

Feb 08, 2018

Thanks, Detlef Lewin. I learn something new.

Then, I tried

=OR(RIGHT($D2,LEN({" Avenue"," Road"}))={" Avenue"," Road"})

This works too. This is not an array formula. However, if I want to make the search with dynamic keywords in cells, I still need to make it an array formula.

Patrick Silverwise

Copper Contributor

Feb 08, 2018

Willy Lau,

Thanks for the quick response. This is how I used your formula:

=IF(D2="","",OR(RIGHT($D2,LEN({" Avenue"," Road"}))={" Avenue"," Road"}))

The result was what I was looking for:

Address1	Willy	Desired
123 Avenue	TRUE	TRUE
123 Road	TRUE	TRUE
123 Avenue Rd	FALSE	FALSE
123 Road Ave	FALSE	FALSE
123 Avenue Road	TRUE	TRUE
123 Road Avenue	TRUE	TRUE

Your mention of an array was insightful. I actually have 200 different street names to identify (and eventually abbreviate), e,g Circle, Trail, etc. The first step was to isolate the offenders. :)

What are your thoughts on what an array formula would look like?

Thanking you in advance. Patrick