Excel Formula to Return TRUE or YES If Text Ends With Specific Characters
Question details
The user needs an Excel formula to check if the text in a specific cell ends with a certain string (like 'og'). If it does, the formula should return TRUE or YES; if not, the cell should remain blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering or categorizing data by identifying whether cell values end with a specific suffix or sequence of characters.
- Observed behavior
- The target cell displays TRUE or YES when the reference cell's value ends with the specified text, and displays nothing (remains blank) when there is no match.
Ensure that the cells you are referencing contain text data rather than numbers. Verify there are no hidden trailing spaces at the end of your text, as extra spaces will prevent the formula from recognizing the exact ending.
Use IF and COUNTIF Functions with a Wildcard
This is the most flexible and highly recommended method. It uses the asterisk (*) wildcard to match any sequence of characters that precede your target text.
By combining the IF function with the COUNTIF function, you can evaluate whether a text condition is met. The asterisk (*) wildcard represents any number of characters, so searching for '*og' specifically targets cells where 'og' is at the very end.
Click on the cell where you want the TRUE or YES result to appear.
Type the following formula: =IF(COUNTIF(AT2,"*og")=1,"TRUE",""). Replace 'AT2' with the reference cell you are evaluating.
If you prefer the result to display YES instead of TRUE, change the formula to =IF(COUNTIF(AT2,"*og")=1,"YES",""). Press Enter to apply.
Use the IF and RIGHT Functions
This alternative method extracts the exact number of characters from the end of a string and checks if they match your target text.
Easily Manage Text Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions, wildcards, and text manipulation tools. You can seamlessly write, troubleshoot, and execute complex formulas like IF and COUNTIF to process your datasets efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you want to analyze.
- 2. Select the formula cell: Click on the empty cell where you want the TRUE, YES, or blank output to appear.
- 3. Input the wildcard formula: Type =IF(COUNTIF(A2,"*og")=1,"TRUE","") into the formula bar at the top of the workspace.
- 4. Apply to multiple cells: Press Enter to view the result, then click and drag the fill handle down to apply this logic to the entire column.
Frequently Asked Questions
Why is my COUNTIF wildcard formula not matching the text?
This usually happens due to hidden trailing spaces at the end of the text in your reference cell. If a cell contains 'dog ' (with a space), a formula searching for '*og' will not recognize it. Use the TRIM function, like TRIM(AT2), to remove extra spaces before applying your condition.
How can I check if a cell contains specific text anywhere, not just at the end?
You can change the placement of the wildcard. By putting an asterisk on both sides of the target text (e.g., "*og*"), the COUNTIF function will return a match if 'og' appears at the beginning, middle, or end of the cell's contents.
Is the COUNTIF function case-sensitive?
No, COUNTIF is not case-sensitive. It treats 'og', 'OG', and 'Og' as identical matches. If you require a strict case-sensitive search, you will need to use a combination of the IF, EXACT, and RIGHT functions instead.
