We’ve all been at that point where we need to make uniform or non-uniform adjustments to a set of cells in excel.
Perhaps you need to edit an already existing template to suit a new requirement, or you made an error and need to remove characters or spaces from a group of cells.
Of course, effecting this kind of change manually can be such an uphill task, especially when dealing with a large number of cells; none of us would be happy to spend valuable time doing something that can easily be automated.
Thankfully, there are formulas and in-built features in excel that can help us do these things with minimal time and effort.
This article will take us through how we can easily remove portions of a text or string in excel.
We may not exhaust all possible scenarios but we will at least examine some of the most common ones, and see different ways of handling them.
Here’s an outline of the things we will cover:
- Remove specific characters in MS Excel
- Using Find & Replace
- Using the SUBSTITUTE formula
- Remove different characters at the same time
- Remove a string of characters from a text in an Excel cell
- Using Find & Replace
- Using the SUBSTITUTE formula
- Remove The Nth Occurrence of a Particular Character in Excel
- Remove the first N characters of a string in Excel
- Remove the last N characters of a string in Excel
- Remove all text following a specified character in Excel
- Trim Leading and Trailing Spaces in Excel String
- Remove characters in Excel with Flash Fill
Remove specific characters in MS Excel
If you would like to eliminate a specific character from a group of cells in excel, you can achieve this in two ways – the in-built “Find & Replace” feature, or using a formula.
Using Find and Replace
Getting rid of a specific character from a string is essentially the same thing as replacing the character with nothing (or an empty string).
With this in mind, you can simply use Excel’s Find & Replace function to replace the specific character with nothing.
Let’s illustrate this with an example. Given a range of cells containing item tags, we will use the Excel Find & Replace feature to remove the asterisk (*) character from all tags by going through a few steps:
- Select the range of cells containing the tags and press CTRL + H to open the Find & Replace dialog.
- In the textbox labeled Find what, insert the character you want to remove.
- The Replace with textbox should be left blank
- Click the Replace All button.
The Result:
Notice that the ‘/’ character is deleted from each of the cells irrespective of the position of occurrence – start, middle, or end.
Excel also tells us how many occurrences of the specified character were replaced.
Of course, you can always undo your replacement by pressing CTRL + Z, if you want to recover your original, raw data.
Also, in a situation where you’re dealing with alphabets and you wish to replace both capital and small letters, you can toggle open the additional “Options” and check Match Case to activate case-sensitive search and replacement.
Using the SUBSTITUTE formula
The Find & Replace feature in excel does the replacement or removal of characters directly on the original data.
This is fine if that’s the end game, but in some cases, you want to make replacements and still retain the original data, perhaps for the purpose of comparison.
To do this, you’ll need the “SUBSTITUTE” formula in Excel.
The image below shows how it works:
Just as shown in the image, you are required to specify the text in which you need to find the string or character to be replaced, the old text (character or string you’re replacing), and new text (what you’re replacing the old text with).
In this case, the old text is in cell C3, the character to be replaced is “/”, and we’re replacing it with nothing (an empty string).
Afterwards, drag down the formula across the table to get the modified version of each tag.
The image below shows the final result.
Note that the resultant value from using the SUBSTITUTE formula is usually a string.
If you’d like it to return a numeric value as the result, use the VALUE function to convert it.
You can either inline it with the SUBSTITUTE function by doing “=VALUE(SUBSTITUTE(X,Y,Z))”, or you can have it on a different column if you may.
Remove Different Characters At The Same Time:
To get rid of multiple distinct characters simultaneously, all you need do is to use a nested SUBSTITUTE.
Your formula will look like this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3, "/",""), "%", ""), ".", "")
Let’s modify the tags in our original example to contain multiple characters that need to be removed:
The resultant values, when we drag this formula all the way down the table, are shown below:
With the nested substitution, Excel replaces or removes characters one after the other iteratively, with the result of the inner substitution serving as the input for the outer one, until it successfully substitutes all the required characters.
Note that just like the find & replace feature is case sensitive by default, Excel SUBSTITUTE function also is case sensitive so you might want to keep this in mind when working with alphabets.
Also, when you need to replace multiple characters, you can consider using a customized lambda function as it is more convenient.
Remove A String of Characters From Text in an Excel Cell
Much the same way as we remove one character from a string in excel, we can also apply a similar procedure when removing a string of characters, whether we’re using the Find & Replace function or we’re using the SUBSTITUTE formula.
Using The Find & Replace Function
Using the same table of data we used before, let us now remove the word “Item” from the first column such that we will have just the serial numbers left in the cell.
We will also tweak it a bit this time by using “item” instead of “Item”, and selecting the “Match Case” checkbox to see how it all plays out.
Following the same procedure we used before, we insert “item” into the Find What textbox, and the Replace With textbox is left blank.
Under “Options”, we also check the Match Case checkbox.
When we click “Replace All”, we see an error message as below:
This implies that when the Match case checkbox is checked, the keyword or search phrase you input must be exactly the same as what you’re trying to replace, meaning that “item” is not the same as “Item”.
When we uncheck the Match case and try again, it works this time around, and we see a message box that shows we are successful:
Using the SUBSTITUTE function
To remove a string of characters from a cell using Excel’s SUBSTITUTE function, follow the same procedure as before.
The formula would be thus:
=SUBSTITUTE(cell, string, “”)
Note: Ensure to use the exact string as the SUBSTITUTE function is case-sensitive.
When this formula is applied down the table, the result is as shown below:
Remove The Nth Occurrence of a Particular Character in Excel
If you looked closely at the arguments of the SUBSTITUTE function, you would notice that there’s a fourth optional argument – instance_num.
This argument is used to specify which occurrence of a particular value is to be removed from a string.
In some cases, you might want to remove a character or string but not all occurrences.
The table below shows how you can remove particular instances of a character by specifying the instance_num argument.
Remove The First N Characters of a String in Excel
Removing the first N characters of a text string is pretty straightforward.
We can use Excel’s REPLACE function to achieve this. Here’s the syntax:
REPLACE(cell, 1, N, “”)
This is essentially interpreted as “Take the first N characters from position 1 in the specified cell, and replace it with an empty string”.
The formula can also be applied to removing the first N characters from position M by specifying an integer M in place of 1 here.
Let’s take an example where we remove the first character:
When this formula is applied all the way down the table, the result is as shown below:
You can also achieve the same result using the RIGHT function with the following syntax:
RIGHT(cell, LEN(cell) – N)
In layman’s terms, this is an instruction to Excel to keep the (LEN(cell) – 1) rightmost characters in the specified cell.
Of course, LEN(cell) returns the number of characters in the cell.
Also, changing the number 1 in the formula to any integer N will remove the first N characters just as with the REPLACE function.
Remove The Last N Characters of a String in Excel
As you might have already guessed, excel also has a LEFT function that does the opposite of what the RIGHT function does.
It removes the last N characters from the string.
The syntax is as follows:
LEFT(cell, LEN(cell) – N)
This tells excel to leave the leftmost LEN(cell)-N characters in the specified cell.
Say we have a list of siblings with the same surname and we want to remove all surnames and leave their first name as in the table below, we can apply the LEFT function to remove the surname by specifying 5 as the number of characters to be removed (In this case “Paul” plus the space before it).
Here’s the result:
Remove all Text following a Specific Character in Excel
To remove all the characters after a specified character, we combine the LEFT and SEARCH functions thus:
LEFT(string, SEARCH(char, string))
Simply put, the SEARCH function returns the position of the specified character in the given string and passes it to the LEFT function which now truncates all characters except the leftmost ones up until that position. 1 is subtracted to ensure that the delimiter is not included.
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.
If we do not want the delimiter to be part of the output, we subtract 1 from the SEARCH result.
In the example below,we use the formula to remove all the user emails from the text, leaving just their first names.
Colon is the delimiter in this case so we subtract 1 to ensure it goes too.
The result:
Trim Leading and Trailing Spaces in Excel String
Whitespaces are occasionally added before or after text in text processing applications like MS Word, to create balance and flow.
However, in spreadsheet applications such as MS Excel, these can be problematic.
When you need to trim excess spaces in a cell, use Excel’s TRIM function.
Here’s the syntax of the TRIM function: =TRIM(cell)
The TRIM function does not only delete all spaces before and after the text, it also deletes spaces between words/substrings, leaving only a single space character.
Remove characters in Excel with Flash Fill
By providing a pattern, you can take advantage of Excel’s Flash Fill feature to automatically get rid of characters or parts of a string.
In the example below, we have names and corresponding email addresses delineated by a colon.
We can remove the colon and email by following a few steps:
- Create a new column where your resultant data will be.
- In the cell of the new column corresponding to the first entry, enter the part of the string you wish to keep.
- Start inputting the corresponding value for the second entry. Immediately MS Excel detects a pattern, it suggests and previews a flash fill
- Press enter to accept the suggestion, and you’re all done!
Note: If Excel does not detect any pattern in your data, manually fill out a few additional cells to help it “learn”. Also, ensure you have Flash Fill enabled in Excel. If afterward it still doesn’t work, you might need to fall back to a different method.
Summary & Conclusion
In this article, we have examined how we can eliminate specific characters from strings in MS Excel.
We looked at different scenarios including removing a single character, removing multiple characters, removing a string of characters, and trimming spaces.
We saw that we can either take advantage of in-built tools in excel like Find & Replace, or use functions like SUBSTITUTE, REPLACE, and TRIM.
Finally, we also looked at the Flash Fill and how it ‘magically’ detects patterns and auto-fills other entries in a given column.
You can check out similar tutorials on this blog via the links below to learn more about the powerful and interesting functionalities of Microsoft Excel.
