Function Problems

How to Use RIGHT and IF to Check Multiple Ending Characters in Excel

Ayan MasoodAyan Masood Nov 11, 2026 869 views

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.

How to Use RIGHT and IF to Check Multiple Ending Characters in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the extracted character to appear (for example, G1).

2
Input the combination formula

Type the formula: =IF(OR(RIGHT(A1,1)="L",RIGHT(A1,1)="D"),RIGHT(A1,1),"")

3
Apply to the column

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 standard IF, OR, and RIGHT functions
Formula Breakdown: This formula checks if the rightmost character is 'L' or 'D'. If the OR condition evaluates to TRUE, it extracts the character; if FALSE, it outputs a blank cell ("").
Powerful Spreadsheet Capabilities

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. 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
  2. 2. Open your workbook: Launch WPS Spreadsheet and open the file containing your text data.
  3. 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. 4. Calculate your data: Press Enter and drag the fill handle downward. WPS Spreadsheet processes text and logical functions instantly without compatibility issues.
100% compatibility with Microsoft Excel formulas and file formats (.xlsx)Free and lightweight alternative to heavy office suitesBuilt-in data validation and formula error-checking toolsFamiliar tabbed interface making navigation effortless
microsoft office alternative - wps office

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","").