In the recent couple of articles, weve looked at different ways to remove characters from strings in Excel. Today, well investigate one more use case – how to delete everything before or after a specific character.
When working with data in Excel, you may need to remove certain characters or text strings from cells. For example, you might want to delete dashes, parentheses, dollar signs, or other arbitrary text from your entries.
Fortunately, Excel provides a couple quick and easy ways to remove specific text from cells. In this step-by-step guide, I’ll cover the key methods you can use:
- Find and Replace
- The SUBSTITUTE function
- The TRIM function
Whether you need to clean up all cells in a column or target just a few specific cells, these techniques will help you delete text efficiently. Let’s dive in!
Using Find and Replace to Remove Text
The Find and Replace tool allows you to search for text across your sheet and replace it with something else. But you can also use it to simply remove text without replacing it.
Here are the steps
-
Select the range of cells where you want to delete text. You can select a single cell, a column, multiple columns, or the entire sheet.
-
Press Ctrl + H to open the Find and Replace dialog box.
-
In the Find what field, enter the text string you want to remove. For example, if you wanted to delete all dashes, you would type a dash character here.
-
Leave the Replace with field empty. Don’t enter anything here.
-
Click the Replace All button.
This will search through all the selected cells, find the specified text, and replace it with nothing—effectively deleting the text!
The key is leaving the Replace with field blank. This tells Excel to replace the found text with literally nothing.
You can also click the Replace button to remove text instance-by-instance rather than replacing all at once.
Tips for Find and Replace
- Use wildcard characters like asterisks (*) if you need more flexible search criteria
- Click Options to do case-sensitive finds or entire cell matches only
- Undo any changes with Ctrl + Z if you make a mistake
Find and Replace provides a quick, efficient way to delete text from a large number of cells. Next let’s look at how to remove text with functions.
The SUBSTITUTE Function
Excel has a function called SUBSTITUTE that replaces existing text in a cell with new text that you specify. But again, we can misuse it to simply remove text by not specifying replacement text.
Here is the syntax:
=SUBSTITUTE(text, old_text, new_text)
Where:
- text = the cell containing the text you want to modify
- old_text = the text string you want to remove
- new_text = leave this blank to delete the old_text
For example, let’s say cell A2 contains the text “Sales-Jan” and you want to remove “-Jan”.
You would use this formula:
=SUBSTITUTE(A2, “-Jan”, “”)
By leaving new_text blank, it will remove “-Jan” rather than replacing it.
The SUBSTITUTE function is versatile because you can nest it to remove multiple text strings from the same cell:
=SUBSTITUTE(SUBSTITUTE(A2, “-Jan”, “”), “Sales”, “”)
This would first remove “-Jan”, then in the result of that remove “Sales” as well.
SUBSTITUTE Tips
- It’s case-sensitive, so use UPPER or LOWER to ignore case
- Remove multiple instances by adding a 4th parameter of how many replacements to make
- Use “&”” to replace text with a blank character rather than empty text
SUBSTITUTE is handy for precisely replacing or deleting text from individual cells. But for removing text from entire columns, TRIM may work better.
The TRIM Function
TRIM is designed to remove leading, trailing, and excess spaces from text. But we can also use it to delete other specified characters.
For example, to remove dashes from a column of cells, use this formula:
=TRIM(A2, “-“)
Where A2 is a cell in the column you want to modify.
TRIM will now remove all dashes from anywhere in the cell, not just leading/trailing.
To remove multiple characters, just list them all:
=TRIM(A2, “-“, “_”)
This will strip both dashes and underscores from the cells.
The TRIM formula is convenient because you can apply it to an entire column without copying it down. With SUBSTITUTE, you’d need to copy the formula to each row.
TRIM Tips
- Remove spaces and specific characters at the same time
- Nest it for multi-step text removal: =TRIM(TRIM(A2, “-“), “.”)
- Use it with other functions like UPPER: =UPPER(TRIM(A2, “-“))
TRIM provides a quick way to clean up text across an entire column.
A Few Final Tips
Here are some additional pointers for removing text in Excel:
-
To remove leading/trailing spaces only, just use the TRIM function by itself with no arguments.
-
To delete text within formulas, use the REPLACE function. This substitutes part of a text string in a formula.
-
If you just want to delete the contents of cells entirely, clear the cells or use the DELETE function.
-
For more complex text parsing, check out the Text to Columns feature or Power Query.
-
Whenever removing text, inspect a few cells first to preview the changes. Undo with Ctrl + Z if you make mistakes.
And that covers the main methods for removing specific text, characters, and symbols from cells in Excel. Whether using Find and Replace across a range or formulas like SUBSTITUTE and TRIM for precision removal, you should now have the tools to clean up your text strings.
How to remove everything before a specific character
To delete part of a text string before a certain character, the generic formula is: RIGHT(cell, LEN(cell) – SEARCH(“char”, cell))
Here, we again calculate the position of the target character with the help of SEARCH, subtract it from the total string length returned by LEN, and pass the difference to the RIGHT function, so it pulls that many characters from the end of the string.
For example, to remove text before a comma, the formula is:
=RIGHT(A2, LEN(A2) - SEARCH(",", A2))
In our case, the comma is followed by a space character. To avoid leading spaces in the results, we wrap the core formula in the TRIM function:
=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2))) 
Notes:
- Both of the above examples assume that there is only one instance of the delimiter in the original string. If there are multiple occurrences, text will be removed before/after the first instance.
- The SEARCH function is not case-sensitive, meaning it makes no difference between lowercase and uppercase characters. If your specific character is a letter and you want to distinguish the letter case, then use the case-sensitive FIND function instead of SEARCH.
How to remove everything after a specific character
To delete text after a particular character, the generic formula is: LEFT(cell, SEARCH(“char”, cell) -1)
Here, we use the SEARCH function to get the position of the character and pass it to the LEFT function, so it extracts the corresponding number of characters from the start of the string. One character is subtracted from the number returned by SEARCH to exclude the delimiter from the results.
For example, to remove part of a string after a comma, you enter the below formula in B2 and drag it down through B7:
=LEFT(A2, SEARCH(",", A2) -1) 
Excel Remove Specific Text From Cell | specific string | Specific Characters
How do I remove specific text from a cell in Excel?
To remove specific text from each cell in a selected range, press Ctrl + H to display the Find and Replace dialog, and then: Enter the unwanted text in the Find what box. Leave the Replace with box blank. Clicking the Replace All button will do all the replacements in one go:
How to remove text after a character in Excel?
To remove text after a character, you need to combine the above two functions into the following formula: Let us see step by step how you can use this formula to remove text after the ‘@’ symbol in our example: Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
How to delete a string in Excel?
For data manipulations in multiple cells, Find and Replace is the right tool. To remove part of a string preceding or following a specific character, these are the steps to perform: Select all the cells where you want to delete text. Press Ctrl + H to open the Find and Replace dialog.
How to remove specific text from a cell in Excel & Google Sheets?
This tutorial will demonstrate how to remove specific text from a cell in Excel & Google Sheets. To find and remove specific text in Excel, we can use the SUBSTITUTE function. Let’s say we have hyphen signs (“-“) in a text and we want to remove it. We can do this with SUBSTITUTE function by using the following formula:
