**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)**

## Leave a Comment