Forum Discussion
Patrick Silverwise
Feb 08, 2018Copper Contributor
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("*",SUBSTITUTE(D2," ","*",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))))) - This returns the value of the last word in the cell.
I need a formula that combines the second with the first to yield the following TRUE / FALSE results:
123 Avenue = True
123 Road = True
123 Avenue Rd = False
123 Road Ave = False
Any suggestions are greatly appreciated! :)
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 LauSteel Contributor
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
- Use Name Manager to create a name, theValue
- Detlef_LewinSilver Contributor
Patrick,
{=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}
- Patrick SilverwiseCopper Contributor
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?
- Patrick SilverwiseCopper Contributor
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 - Willy LauSteel Contributor
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"}))
- Willy LauSteel Contributor
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.
- Detlef_LewinSilver Contributor
Willy, that's even better because CSE is not required. And it uses less functions and less operations.