Search code examples
excelmatchpartialconditional-formattingformulas

Format if cell partially matches another

I have two columns with telephone numbers in them, I want cells B2 and B3 to change colour depending on whether they match the last 4 digits in the column next to them:

SO20748315 question example

B2 turns green as it matches the end of A2 and B3 turns red as it does not match the last four digits of A3.

How would I go about doing this?

Solution

  • I suggest you just use one rule, say format all red and use the CF to change that to green only when there is a match, say with: 

    =B1=VALUE(RIGHT(A1,4))

    in B1 and applied to B:B.