turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 149K Members
- 4,283 Online
- 36.5K Conversations

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Need an Excel Formula giving True or False based on the last word in the cell

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## Need an Excel Formula giving True or False based on the last word in the cell

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Need an Excel Formula giving True or False based on the last word in the cell

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 03:29 PM - edited 02-08-2018 03:32 PM

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

Labels:

18 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 03:57 PM

Patrick,

{=OR(IFERROR(LEN(A1)-SEARCH({"Avenue";"Road"},A1)+1={6;4},FALSE))}

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 04:18 PM - edited 02-08-2018 04:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 05:09 PM - edited 02-08-2018 05:31 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 05:32 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 05:45 PM

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"}))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 05:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 05:55 PM

My sincere apologies to Detlef Lewin. I'm a newbie with Excel and didn't realize I needed to use the Ctrl + Shift + Enter for his formula to work. My bad. Please forgive me.

Thanks for the heads up Willy Lau, and the non-array format to use with the IF statement.

Patrick

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 06:17 PM

__My approach is based on Detlef Lewin's solution,__ and if you want to cater Circle, Trail, etc. my approach still need to use array formula.

Says, currently in Sheet1, you can create Sheet2. Type, Circle, Avenue, Road, Trail, etc, in A1 To A4 (or more as you wish), Then, in sheet1 D2

{=IF($D2="","",OR(RIGHT($D2,LEN(Sheet2!$A$1:$A$4))=Sheet2!$A$1:$A$4))}

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 06:29 PM - edited 02-08-2018 06:30 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 06:42 PM

Will it work if the array is in rows instead of columns?

For example:

{=IF($D2="","",OR(RIGHT($D2,LEN(Sheet2!$A$1:$A$200))=Sheet2!$A$1:$A$200))}

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 06:50 PM

As what you type, it works on D3, D4, D5...etc, but not A2, B2, O2 or other columns in row 2 because $D is using.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 07:10 PM

One more thing that I forgot to mention, my approach is case-sensitive, but Detlef Lewin's solution is not, which is much better to be used on your case.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 08:12 PM

Thanks for the heads-up on the case. I'll run a =PROPER( ) to standardize it.

Your formula:

{=IF($A2="","",OR(RIGHT($A2,LEN(Sheet2!$A$1:$A$5))=Sheet2!$A$1:$A$5))}

Using an array in Sheet2 seems the best solution.

My list has thousands of rows. Is there an easy way to copy array formulas across multiple cells?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 08:35 PM

Willy, that's even better because CSE is not required. And it uses less functions and less operations.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 08:40 PM

"Is there an easy way to copy array formulas across multiple cells?"

Enter the formula in the first cell with CSE and then copy it across.

If you have to edit the formula later then you have to enter it with CSE again.

CSE = CTRL-SHIFT-ENTER

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 09:50 PM

I found it. Create the first array cell. Hold down right mouse button and drag down column until desired cell. Release and do a Ctrl D (not a Ctrl V to paste). Or, with the first cell of array selected, scroll down to the last cell needed, do a Shift+Right Mouse to select the range, then Ctrl D.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2018 10:03 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Best Response confirmed by Patrick Silverwise (Occasional Contributor)

Related Conversations

Keep number from changing after setting cell back to zero

hopr37 hotmail
in
Formulas and Functions
on
03-27-2018
527
Views

0 Likes

16 Replies

Power Query - Combine Data from Folder with Relative Paths

Justin Schmidt
in
Get and Transform Data
on
10-24-2017
3,557
Views

0 Likes

2 Replies

sum by color when colors are set by conditional formatting

matt nipper
in
Macros and VBA
on
12-20-2016
16K
Views

0 Likes

51 Replies

Search for one value in column A and list all belonging values from column B

Beatrix und Andreas
in
Formulas and Functions
on
02-14-2018
204
Views

0 Likes

9 Replies

LEFT formula based on adjacent cell text and pertaining value

Dan Kish
in
Formulas and Functions
on
03-08-2018
192
Views

0 Likes

7 Replies

Transfer data from one workbook to another

Mohamed Hasan Marie
in
Macros and VBA
on
03-07-2018
172
Views

0 Likes

10 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft