Table of Contents
Excel is a powerful tool that can be used to efficiently manage and analyze data. One of its useful features is the ability to check if a cell ends with specific characters. This can be done by using the “RIGHT” function in Excel, which allows the user to extract the last characters from a cell. By combining this function with an IF statement and the “LEN” function, the user can create a formula that will return a true or false value based on whether the cell ends with the specified characters. This can be extremely helpful for data validation and filtering purposes, making Excel a valuable tool for data management.
Excel: Check if Cell Ends With Specific Characters
You can use the following formulas in Excel to check if a cell ends with specific characters:
Formula 1: Check if Cell Ends with Specific Set of Characters
=IF(RIGHT(A2,2)="AB","Yes","No")
This particular formula checks if the last two characters in cell A2 are “AB” and returns Yes or No accordingly.
Formula 2: Check if Cell Ends with One of Several Characters
=IF(OR(RIGHT(A2,1)="A", RIGHT(A2,1)="C"),"Yes","No")
This particular formula checks if the last character in cell A2 is either “A” or “C” and returns Yes or No accordingly.
Formula 3: Check if Cell Ends with Number
=IF(ISNUMBER(VALUE(RIGHT(A2,1))), "Yes","No")
This particular formula checks if the last character in cell A2 is a number and returns Yes or No accordingly.
The following examples show how to use each formula in practice with the following list of product ID’s in Excel:
Let’s jump in!
Example 1: Check if Cell Ends with Specific Set of Characters
We can use the following formula to check if the last two characters of each cell in the Product column are equal to “AB” or not:
=IF(RIGHT(A2,2)="AB","Yes","No")
We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:
Column B now returns “Yes” or “No” in each row to indicate if the Product ID in the corresponding cell in column A ends with “AB” or not.
Example 2: Check if Cell Ends with One of Several Characters
We can use the following formula to check if the last character of each cell in the Product column is equal to either “A” or “C”:
=IF(OR(RIGHT(A2,1)="A", RIGHT(A2,1)="C"),"Yes","No")
We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:
Column B now returns “Yes” or “No” in each row to indicate if the Product ID in the corresponding cell in column A ends with either “A” or “C.”
Example 3: Check if Cell Ends with Number
We can use the following formula to check if the last character of each cell in the Product column is a number or not:
=IF(ISNUMBER(VALUE(RIGHT(A2,1))), "Yes","No")
We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:
Column B now returns “Yes” or “No” in each row to indicate if the last character of the Product ID in the corresponding cell in column A is a number or not.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: