Forum Discussion
Need an Excel Formula giving True or False based on the last word in the cell
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))}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.
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
Willy, that's even better because CSE is not required. And it uses less functions and less operations.
