Excel Formula to Extract Trailing Letters from Text Strings
Question details
The user needs to extract only the trailing letters from an alphanumeric text value in an Excel cell, ensuring that leading letters and numbers are ignored.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and standardizing data where item codes or location values contain trailing letter identifiers (e.g., extracting 'A' from 'B9A' or 'G' from '100G1').
- Observed behavior
- Standard text extraction functions often pull all non-numeric characters, which unintentionally includes leading letters. A more targeted formula is required to isolate the end characters.
Verify whether you are using a version of Excel that supports Dynamic Array formulas (like Microsoft 365), as this determines which specific functions (such as SEQUENCE) you can utilize.
Extract Specific Trailing Characters Using MID and IFERROR
This solution is highly recommended if you only want to extract letters from the final few positions of a string, ignoring any leading letters at the beginning of the text.
This formula checks the last three positions of a text string individually. It verifies if the character is a letter by attempting to convert it to a value. If it is text, it extracts it; otherwise, it returns a blank.
Click on the empty cell where you want the extracted trailing letters to appear.
Type the following formula into the formula bar: =IFERROR(IF(ISNUMBER(VALUE(MID(A1,LEN(A1)-2,1))),"",MID(A1,LEN(A1)-2,1)),"")&IFERROR(IF(ISNUMBER(VALUE(MID(A1,LEN(A1)-1,1))),"",MID(A1,LEN(A1)-1,1)),"")&IFERROR(IF(ISNUMBER(VALUE(MID(A1,LEN(A1),1))),"",MID(A1,LEN(A1),1)),"")
Press Enter to evaluate the formula. Then, drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this formula to the rest of your data.
Extract All Non-Numeric Characters Using Dynamic Arrays
Use this approach if you need to extract all letters from an alphanumeric string and do not have leading letters that need to be excluded.
Use WPS Spreadsheet for Complex Data Extraction
WPS Spreadsheet fully supports complex array formulas, standard text extraction functions, and error handling methods. It handles Microsoft Excel formulas seamlessly, making advanced data manipulation straightforward and efficient.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the alphanumeric data.
- 2. Apply the extraction formula: Select an empty cell next to your data and paste the targeted MID and IFERROR formula.
- 3. Drag to fill: Click the bottom-right corner of the active cell and drag downward to extract trailing letters for the entire column.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the SEQUENCE formula?
A #SPILL! error occurs when a dynamic array formula attempts to output multiple results into adjacent cells, but those cells are already occupied by other data. Ensure the adjacent cells are empty, or wrap the formula in a function like CONCAT to collapse the array into a single cell.
How can I extract only the numbers instead of the letters?
To extract only numbers, you can invert the logic in the IF statement. Instead of returning a blank when ISNUMBER is true, return the character itself, and return a blank when it evaluates to false.
Does WPS Office support dynamic array formulas like SEQUENCE?
Yes, newer versions of WPS Office fully support dynamic array functions like SEQUENCE, UNIQUE, and FILTER, allowing complete compatibility with modern Excel spreadsheets.
