Forum Discussion
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...
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)
Patrick,
{=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}
Detlef,
Thanks for the quick response. Here is how I used your suggestion:
=IF(D2="","",OR(IFERROR(LEN(D2)-SEARCH({"Avenue";"Road"},D2)+1={6;4},FALSE)))
Here are the results. It can't seem to find Road. Thoughts?
| Address1 | Detlef | Desired |
| 123 Avenue | TRUE | TRUE |
| 123 Road | FALSE | TRUE |
| 123 Avenue Rd | FALSE | FALSE |
| 123 Road Ave | FALSE | FALSE |
| 123 Avenue Road | FALSE | TRUE |
| 123 Road Avenue | TRUE | TRUE |
Detlef Lewin's solution is an array formula. After you type the formula, press Ctrl + Shift + Enter. It works perfectly.
If you do not want to use array formula, then, try
=IF(LEN($D2)=0,"",OR(RIGHT($D2, LEN({" Avenue"," Road"}))={" Avenue"," Road"}))My sincere apologies to Detlef Lewin. I'm a newbie with Excel and didn't realize I needed to use the Ctrl + Shift + Enter for his formula to work. My bad. Please forgive me.
Thanks for the heads up Willy Lau, and the non-array format to use with the IF statement.
Patrick
My approach is based on Detlef Lewin's solution, and if you want to cater Circle, Trail, etc. my approach still need to use array formula.
Says, currently in Sheet1, you can create Sheet2. Type, Circle, Avenue, Road, Trail, etc, in A1 To A4 (or more as you wish), Then, in sheet1 D2
{=IF($D2="","",OR(RIGHT($D2,LEN(Sheet2!$A$1:$A$4))=Sheet2!$A$1:$A$4))}
