Forum Discussion

jai_v327's avatar
jai_v327
Copper Contributor
Dec 05, 2025

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

  • SnowMan55's avatar
    SnowMan55
    Bronze 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.)

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    If to slightly modify previous ones

    =SUM( COUNTIFS(C1, "* " & $G$2:$G$5 & "*" ) )
    =REGEXTEST($C1,"(?<=\s)(CORPORATION|LLC|INC|ENTERPRISES)")
  • 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

     

Resources