Forum Discussion
Need help with conditional fomatting formula based in text on cells
Hi everyone,
I have a list of owners' names in column C.
C2 contains 1+1 CAR CORPORATION
C3 contains 8-J ENTERPRISES, LLC
C4 contains LOYD RENTALS LLC
C5 contains SMITH PAUL & PAULA
C6 contains DOE JHON & JANE
C7 contains MENENDEZ JAMES
C8 contains BROWN INVESTMENTS, INC.
And so on
I'd like to highlight the whole row for the cells that have CORPORATION, LLC, INC, ENTERPRISES in their names.
Hope this makes sense and someone can help me.
Thank you.
4 Replies
- SnowMan55Bronze Contributor
While others have posted basic formulas, it occurs to me that they may result in "false positives". I immediately thought of the INC in LINCOLN (whether a given name such as Lincoln Chafee or a surname). Other people who would be incorrectly highlighted:
- Prince R. Nelson (yes, "the" Prince)
- Linda Prince (film producer and director in independent cinema)
- anyone with a surname of Finch
I don't know if you are attempting to select all businesses (and even all organizations) in your data. These would be highlighted:
- Incarnate Word Church (not their exact title, but you get the idea)
- Incredible Weddings
- Agriculture & Clean Energy Incubator (Texas)
- Inca Design (UK)
And other hypothetical businesses also (I made up these; no guarantee that they do not exist):
- Principal Realtors
- It's A Cinch (rope store)
- Cincinnati Grocery
- Provincial Apartments
- Carpet Cleaners of Distinction
- Invincible Law Office
- Ye Olde Incense Shoppe
- Fragrances, Charms & Incantations
- Springfield Incineration Facility
- The Income Builders (financial advisors)
- Inchworm Babysitters
- Incomparable Cleaners
- Incalculable Wealth Advisors
- "Inconceivable!" Assassins of Sicily (Wallace Shawn, Proprietor)
And "LLC" has a similar, but smaller problem:
- Hillcrest Manor (Texas)
- Hellcat Firefighters
- Ecclesiastical Skullcaps of New York
You can exclude many of those names (if desired) by adding a leading space before INC and LLC. (A period after INC would be good, but I don't know how "clean" your data is.)
- SergeiBaklanDiamond Contributor
If to slightly modify previous ones
=SUM( COUNTIFS(C1, "* " & $G$2:$G$5 & "*" ) )=REGEXTEST($C1,"(?<=\s)(CORPORATION|LLC|INC|ENTERPRISES)") - Harun24HRBronze Contributor
Try the following formula in custom conditional formula rule.
=OR(REGEXTEST(TEXTSPLIT($C1," "),"CORPORATION|LLC|INC|ENTERPRISES")) - OliverScheurichGold Contributor
This is the rule for conditional formatting in my sample file.
=SUM(N(ISNUMBER(SEARCH($G$2:$G$5,C2))))This is the "applies to" range in the sample.
=$C$2:$C$8