Highlight cells that ends with

Highlight cell that ends with

Highlight cell that ends with specific text function of excel enables any user to highlight cells that have definite common letters at their end. The formula for this function is based on COUNTIF function.

Formula

=COUNTIF(A1,"*text")

Explanation

COUNTIF function counts the number of cells that have a similar pattern at their end which we want to highlight. Certain text to be highlighted is inserted in “*text”. The function of a wild card is to match any order of letters.

Example

In the example given below range B4:B12 contains the name of different countries. We have highlighted the countries that end with “land” using the formula: (See screenshot)

=COUNTIF(B4:B13,"*land")

Figure 1. Example of Highlight cells that begin with “land”

Notes

The same formula can be made more versatile by designating any input cell as a named range. Then that name is mentioned in the formula.

=COUNTIF(B4,"*"&input)

While using excel, if we want to verify case as well we will need a more complex formula that works on RIGHT and EXACT functions collectively.

=EXACT(RIGHT(A1,LEN(substring)),substring)

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar