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 09, 2018

Hi, Patrick Silverwise.

Use Name Manager to create a name, theValue
```
=$D2
```

Use Name Manager to create a name, LastWord

=RIGHT(theValue,LEN(theValue)-FIND("|*|",SUBSTITUTE(theValue," ","|*|",LEN(theValue)-LEN(SUBSTITUTE(theValue," ","")))))

Use Name Manager to create a name, KeywordFound
```
=COUNT(SEARCH({"Avenue","Road"},LastWord))>0
```
Your formula
```
=KeywordFound
```