The article looks at how to quickly remove part of text from Excel cells using formulas and inbuilt features.
In this tutorial, we will look at the most common cases of removing characters in Excel. Want to delete specific text from multiple cells? Or maybe strip the first or last character in a string? Or perhaps remove only a specific occurrence of a given character? Whatever your task is, you will find more than one solution for it!
If your goal is to eradicate a certain character from Excel cells, there are two easy ways to do it - the Find & Replace tool and a formula.
Bearing in mind that removing a character is nothing else than replacing it with nothing, you can leverage Excel's Find and Replace feature to accomplish the task.
As an example, here's how you can delete the # symbol from cells A2 through A6.
As the result, the hash symbol is removed from all of the selected cells at once, and a pop-up dialog informs you how many replacements have been made:
Tips and notes:
To eliminate a specific character from any position is a string, use this generic SUBSTITUTE formula:
In our case, the formula takes this form:
=SUBSTITUTE(A2, "#", "")
Basically, what the formula does is that it processes the string in A2 and replaces each hash symbol (#) with an empty string ("").
Enter the above formula in B2, copy it down through B6, and you will get this result:
Please pay attention that SUBSTITUTE always returns a text string, even if the result contains only numbers like in cells B2 and B3 (notice the default left alignment typical for text values).
If you want the result to be a number, then wrap the above formula in the VALUE function like this:
=VALUE(SUBSTITUTE(A2, "#", ""))
Or you can perform some math operation that does not change the original value, say add 0 or multiply by 1:
=SUBSTITUTE(A2, "#", "")*1
To remove multiple characters with one formula, simply nest SUBSTITUTE functions one into another.
For example, to get rid of a hash symbol (#), forward slash (/) and backslash (\), here's the formula to use:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#",""), "/", ""), "\", "")
Tips and notes:
The two methods we used for removing a single character can handle a sequence of characters equally well.
To remove specific text from each cell in a selected range, press Ctrl + H to display the Find and Replace dialog, and then:
Clicking the Replace All button will do all the replacements in one go:
To remove part of a text string, you again use the SUBSTITUTE function in its basic form:
For example, to delete the substring "mailto:" from cell A2, the formula is:
=SUBSTITUTE(A2, "mailto:", "")
This formula goes to B2, and then you drag it down across as many rows as needed:
In situation when you want to delete a certain occurrence of a particular character, define the last optional argument of the SUBSTITUTE function. In the generic formula below, instance_num determines which instance of the specified character should be replaced with an empty string:
To eradicate the 1st slash in A2, your formula is:
=SUBSTITUTE(A2, "/", "", 1)
To strip the 2nd slash character, the formula is:
=SUBSTITUTE(A2, "/", "", 2)
To remove the first character from the left side of a string, you can use one of the following formulas. Both do the same thing, but in different ways.
Translated into a human language, the formula says: in the specified cell, take 1 character (num_chars) from the 1st position (start_num), and replace it with an empty string ("").
For instance, to remove the first character from A2, the formulas go as follows:
=REPLACE(A2, 1, 1, "")
=RIGHT(A2, LEN(A2) - 1)
The screenshot below shows the REPLACE formula. The RIGHT LEN formula will deliver exactly the same results.
To delete any n characters from the beginning of a string, please see How to remove characters from left in Excel.
To strip off the last character from the end of a string, the formula is:
The logic is similar to the RIGHT LEN formula from the previous example:
You subtract 1 from the total cell length and serve the difference to the LEFT function, so it can pull that many characters from the beginning of the string.
For instance, you can remove the last character from A2 using this formula:
=LEFT(A2, LEN(A2) - 1)
To delete any n characters from the end of a string, please see How to remove characters from right in Excel.
To delete everything after a given character, the generic formula is:
The logic is quite simple: the SEARCH function calculates the position of the specified character and passes it over to the LEFT function, which brings the corresponding number of characters from the beginning. Not to output the delimiter itself, we subtract 1 from the SEARCH result.
For example, to remove text after a colon (:), the formula in B2 is:
=LEFT(A2, SEARCH(":", A2) -1)
For more formula examples, please see Delete text before or after a certain character.
In text processors such as Microsoft Word, a whitespace before text is sometimes added intentionally to create a balanced and elegant flow for the reader's eye. In spreadsheets programs, leading and trailing spaces may creep unnoticed and cause a lot of problems. Luckily, Microsoft Excel has a special function, named TRIM, to delete extra spaces.
The formula to remove excess spaces from cells is as simple as this:
Where A2 is your original text string.
As you can see in the image below, it deletes all spaces before text, after text and between words/substrings except for a single space character.
If this simple formula does not work for you, then most likely there are some non-breaking spaces or non-printing characters in your worksheet.
To get rid of them, convert non-breaking spaces into regular spaces with the help of SUBSTITUTE:
SUBSTITUTE(A2, CHAR(160), " ")
Where 160 is the code number of a non-breaking space character ( ).
Additionally, use the CLEAN function to eliminate non-printable characters:
CLEAN(SUBSTITUTE(A2, CHAR(160), " "))
Nest the above construction in the TRIM function, and you'll get a perfect formula to remove spaces before/after text as well as non-breaking spaces and non-printing characters:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
For more information, please see How to remove spaces in Excel.
In simple scenarios, Excel's Flash Fill can do you a favor and remove characters or part of text automatically based on the pattern that you provide.
Let's say you have a name and email address in one cell separated by a comma. You want to remove everything after the comma (including the comma itself). To have it done, carry out these steps:
Note. If Excel is unable to recognize a pattern in your data, fill in a couple more cells manually to provide more examples. Also, make sure Flash Fill is enabled in your Excel. If it still does not work, then you'll have to resort to some other method.
This final section presents our own solutions for removing text from Excel cells. If you love finding simple ways to handle complex challenges, you'll enjoy the handy tools included with Ultimate Suite.
On the Ablebits Data tab, in the Text group, there are three options for removing characters from Excel cells:
To delete a specific character or substring from selected cells, proceed in this way:
Below are a few examples that cover the most typical scenarios.
To remove a particular character(s) from multiple cells at once, select Remove custom characters.
As an example, we are deleting all occurrences of the uppercase letters A and B from the range A2:A4:
To remove a certain set of characters, select Remove character sets, and then choose one of the following options:
To delete part of a string, choose the Remove a substring option.
For example, to extract usernames from Gmail addresses, we are removing the "@gmail.com" substring:
That's how to remove text and characters from Excel cells. I thank you for reading and look forward to seeing you on our blog next week!
Table of contents