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 Lewen
Thanks for the quick reply. I used your formula as an array and it worked perfectly. I entered
=IF(D2="","",OR(IFERROR(LEN(D2)-SEARCH({"Avenue";"Road"},D2)+1={6;4},FALSE)))
then Cntl+Shift+Enter and received the desired result:
| Address1 | Detlef | 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 |
While it let me copy and paste the formula, it appears that I must click each cell and Cntl+Alt+Enter to make it an array? Since I have a lot of rows is there a quicker way?