Share via

Highlight partial text match with conditional formatting

Anonymous
2010-07-12T15:53:23+00:00

I have cells with initials and some cells have multiple intials, i.e. cw/kk/rb.  I want to find every instance of 'kk'.  Can I use conditional formatting to highlight a partial text match in a cell?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2010-07-12T16:22:49+00:00

    I have cells with initials and some cells have multiple intials, i.e. cw/kk/rb.  I want to find every instance of 'kk'.  Can I use conditional formatting to highlight a partial text match in a cell?

    Try this...

    Let's assume the range in question is A1:A10.

    Select the *entire* range A1:A10 starting from cell A1. Cell A1 will be the

    active cell. The active cell is the one cell in the selected range that is

    not shaded. The formula will be relative to the active cell.

    Goto the menu Format>Conditional Formatting

    Select the Formula Is option

    Enter this formula in the box on the right:

    =SEARCH("kk",A1)

    Click the Format button

    Select the Patterns tab

    Select the desired fill color

    OK out

    --

    Biff

    Microsoft Excel MVP

    7 people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-04-24T00:25:51+00:00

    Hi,

    You may also try this

    1. Select the range of cells and press Ctrl+F
    2. In the Find what box, type *kk*
    3. Press Find all
    4. Now press Ctrl+A

    5, Close the Find box

    1. Now select any colour.

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2010-07-12T16:22:36+00:00

    You can use conditional formatting, but you can't use it to highlight just the

    kk substring within the text.

    =countif(a1,"*kk*")>0

    or

    =isnumber(search("kk",a1))

    or if upper/lower case is important:

    =isnumber(find("kk",a1))

    (both =countif() and =search() are non-case sensitive.)

    kking7 wrote:

    I have cells with initials and some cells have multiple intials, i.e. cw/kk/rb.  I want to find every instance of 'kk'.  Can I use conditional formatting to highlight a partial text match in a cell?

    --

    Dave Peterson

    0 comments
  3. Anonymous
    2010-07-12T16:17:45+00:00

    Unfortunately, the initials sometimes appear alone or in a different location in the sequence.  I am looking to find a specific pair of initial that could appear by itself, the beginning, middle or end of a group.

    Thanks kk

    0 comments
  4. Anonymous
    2010-07-12T16:03:49+00:00

    Usin your example, see if you can work with this, enter formula in Conditional Formatting: =MID(A1,4,2)="kk"

    0 comments