How to Remove Trailing Hyphens from Text Values in Excel
Question details
The user needs a formula to selectively remove a trailing hyphen from text strings without altering numeric values or text strings that do not end with a hyphen.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up text data or imported values where some string entries incorrectly end with a hyphen, while mixed data types exist in the same column.
- Observed behavior
- The correct formula should leave numbers completely unchanged, strip the final hyphen from text strings ending with one, and keep all other text completely intact.
Ensure that your data does not contain invisible trailing spaces after the hyphen, as this can cause the formula to miss the trailing character. You may want to use the TRIM function first if your dataset is messy.
Use the IF, ISNUMBER, RIGHT, and LEFT Functions
This combination of functions checks the data type and evaluates the last character of the string to conditionally remove the hyphen.
This approach evaluates whether the cell contains a number first. If the cell contains text, it checks if the very last character is a hyphen. When a hyphen is identified at the end, it extracts everything except that final character, leaving the rest of the string perfectly intact.
Click on an empty cell next to your target data where you want the cleaned results to appear (for example, cell B2).
Type the following formula exactly: =IF(ISNUMBER(A2),A2,IF(RIGHT(A2,1)="-",LEFT(A2,LEN(A2)-1),A2))
Press Enter to execute the formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this logic to the rest of the rows in your column.
Clean Up Data Easily with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced text and logical functions like IF, LEFT, RIGHT, and LEN, allowing you to clean up trailing hyphens seamlessly. It provides a highly compatible, fast, and completely free environment to manage your daily data processing.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the file containing the messy data.
- 2. Input the cleanup formula: Select a blank cell adjacent to your data and paste the formula: =IF(ISNUMBER(A2),A2,IF(RIGHT(A2,1)="-",LEFT(A2,LEN(A2)-1),A2)).
- 3. Fill the formula down: Press Enter and double-click or drag the fill handle to apply the formula effortlessly across all the text values you need to clean.
Frequently Asked Questions
How do I remove hyphens from the beginning of a text string instead?
You can modify the formula to check the first character using the LEFT function. Use =IF(LEFT(A2,1)="-",RIGHT(A2,LEN(A2)-1),A2) to conditionally remove a leading hyphen.
Why isn't the formula removing the trailing hyphen in my data?
Your text might have invisible trailing spaces after the hyphen. Try wrapping the cell reference with the TRIM function in your RIGHT logic, like this: RIGHT(TRIM(A2),1).
Can I remove all hyphens from a cell, regardless of their position?
Yes, if you want to eliminate every hyphen within the cell instead of just the trailing one, you can use the SUBSTITUTE function: =SUBSTITUTE(A2, "-", "").
Will this formula work if there are multiple trailing hyphens?
The provided formula only removes a single trailing hyphen. If you have multiple hyphens clustered at the end, you would need nested IF functions, a recursive LAMBDA (in modern spreadsheet versions), or a more advanced array formula to strip them all.
