How to Use RIGHT and IF to Check Multiple Ending Characters in Excel
Question details
The user wants to check whether the last character in a cell matches specific letters (such as D or L) and return that character to another column without encountering #VALUE! errors.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting or validating specific trailing characters from text strings conditionally based on multiple criteria.
- Observed behavior
- Looking for a working formula that accurately returns the matching character if found and leaves the cell blank if there is no match, bypassing common formula errors.
Ensure that the text data you are checking does not have hidden trailing spaces, as the RIGHT function will count a space as the last character. If your data is inconsistent, consider using the TRIM function first.
Use standard IF, OR, and RIGHT functions
This is the most universally compatible method for checking and extracting specific ending characters across all spreadsheet software versions.
By combining the IF, OR, and RIGHT functions, you can tell the spreadsheet to extract the rightmost character only if it meets one of your defined conditions. If the condition is not met, it returns an empty string.
Click on the cell where you want the extracted character to appear (for example, G1).
Type the formula: =IF(OR(RIGHT(A1,1)="L",RIGHT(A1,1)="D"),RIGHT(A1,1),"")
Press Enter to see the result. Then, click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to the rest of your data.

Use the LET function for newer spreadsheet versions
If you are using a modern version of Excel or WPS Office, the LET function simplifies the formula, making it easier to read and improving calculation performance.
Use WPS Spreadsheet for Advanced Data Extraction
WPS Office Spreadsheet fully supports complex logical and text functions like IF, OR, RIGHT, and LET. It offers a seamless, fast, and familiar experience for data cleaning and formula manipulation.
- 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open your workbook: Launch WPS Spreadsheet and open the file containing your text data.
- 3. Enter the formula: Select an empty cell and enter your preferred formula, such as =IF(OR(RIGHT(A1,1)="L",RIGHT(A1,1)="D"),RIGHT(A1,1),"").
- 4. Calculate your data: Press Enter and drag the fill handle downward. WPS Spreadsheet processes text and logical functions instantly without compatibility issues.

Frequently Asked Questions
Why is the RIGHT function returning an empty space instead of a letter?
This usually happens when there are hidden trailing spaces at the end of your text. To fix this, wrap your cell reference inside the TRIM function to remove extra spaces: RIGHT(TRIM(A1), 1).
Can I check for more than two ending characters using this formula?
Yes, you can add as many conditions as you need inside the OR function. For example, to check for D, L, or X, you can write: OR(RIGHT(A1,1)="D", RIGHT(A1,1)="L", RIGHT(A1,1)="X"). Alternatively, if you use the LET function with array constants, write: OR(letter={"D","L","X"}).
How do I make the character check case-insensitive?
The standard equal sign (=) used in the IF and OR functions is already case-insensitive in both Excel and WPS Spreadsheet. This means "d" and "D" will automatically both trigger a match without needing any extra formula adjustments.
How can I return a custom text word instead of the extracted character?
You can change the "value_if_true" part of the IF function. To return the word "Found" instead of the letter, use: =IF(OR(RIGHT(A1,1)="L",RIGHT(A1,1)="D"),"Found","").




