Forum Discussion

Patrick Silverwise's avatar
Patrick Silverwise
Copper Contributor
Feb 08, 2018

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! :)

 

Formulas & Functions
Need Help
  • Willy Lau's avatar
    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)
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Feb 08, 2018

    Patrick,

     

    {=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}
    • Willy Lau's avatar
      Willy Lau
      Steel Contributor
      Feb 09, 2018

      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.

      • Patrick Silverwise's avatar
        Patrick Silverwise
        Copper Contributor
        Feb 09, 2018

        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

    • Patrick Silverwise's avatar
      Patrick Silverwise
      Copper Contributor
      Feb 09, 2018

      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 Lau's avatar
        Willy Lau
        Steel Contributor
        Feb 09, 2018

        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"}))
    • Patrick Silverwise's avatar
      Patrick Silverwise
      Copper Contributor
      Feb 09, 2018

      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?

       

       

       

       

       

       

       

  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 09, 2018

    Hi, Patrick Silverwise.  

     

    1. Use Name Manager to create a name, theValue 
      =$D2
    2. Use Name Manager to create a name, LastWord 
      =RIGHT(theValue,LEN(theValue)-FIND("|*|",SUBSTITUTE(theValue," ","|*|",LEN(theValue)-LEN(SUBSTITUTE(theValue," ",""))))) 
    3. Use Name Manager to create a name, KeywordFound 
      =COUNT(SEARCH({"Avenue","Road"},LastWord))>0
    4. Your formula 
      =KeywordFound

Resources