Your spreadsheet is loaded with multiple values and these values are not necessarily needed all the time. So, what would you do with these unwanted entries?
Undoubtedly, you must be looking for the best solution to removing characters in Excel. Your data could be from several sources and some parts of this data could be irrelevant and unwanted. You have to convert data according to your choice so that it could be useful. Once you successfully remove the characters from a string, your data could turn into a new dataset.
Below are some easy-to-follow methods that can help you in removing characters in Excel. So, let’s jump into finding the best solution.
How to Remove Specific Characters in Excel
When you need to delete a specific character from Excel cells, you can manage it by using two different methods – the Find & Replace tool and a Formula.
How to Remove Characters from Multiple Cells with Find and Replace Option
Using the Find and Replace tool lets you easily find and replace the character that you don’t want anymore. Simply, you find a character and replace it with nothing.
- Choose a cell range from which you need to remove a character.
- Press CTRL + H and the Find and Replace toolbox will appear.
- Type the character in the Find what field.
- Don’t type anything in the Replace field and leave it blank.
- Click Replace all.
Below is an example you can see in which the # symbol from cell A2 to A6 is deleted.
Eventually, the # symbol is no more displayed because it is deleted from the cell range selected in it. you will also get notified about how many replacements have been done so far.
Bonus Tip:
You can directly remove the characters from the source data. If you see the outcome is not what you needed, simply press CTRL + Z, and all the functions will be reversed instantly, and you will get the original data back in front of you.
On the other hand, when your data contains alphabetical characters, click Options to expand the Find and Replace option, now click on the Match case field.
How to Remove Characters from Left in Excel
Removing characters in Excel is not always so easy, sometimes your desired characters are placed somewhere you cannot find and removed instantly. However, it still is the most common thing to do in Excel and you can do it simply by using three formulas. Let’s find out how you can do it:
Remove the First Character in Excel
When you need to remove the first character from a string, try using REPLACE option, or else you can use both the RIGHT and LEN functions together.
REPLACE(string, 1, 1, “”)
In this case, you can take 1 character from the first position while replacing it with an empty string (“”).
RIGHT(string, LEN(string) – 1)
Here in this formula, the LEN function is used that helps in calculating the net length of the string and subtracting 1 character from it. You will notice the difference to RIGHT so that it can extract that many characters from the end of the string.
For instance, the formula to remove the first character from cell A2 would be as:
REPLACE(A2, 1, 1, “”)
RIGHT(A2, LEN(A2) – 1)
Remove the Last Character in Excel
You will use the following formula to remove the last character in a cell:
LEFT(string, LEN(string) – 1)
Here, you can subtract 1 from the total string length and the difference is there to the LEFT function for extraction. For instance, the formula to remove the last character from cell A2 in B2 is:
LEFT(A2, LEN(A2) – 1)
How to Remove Characters in Excel with REPLACE Function
This method explains how you can remove unwanted characters in Excel by using Replace function. In this example, you will find a column named Delivery Details from which you have to remove the delivery status and get the Address by using the Replace function. So, let’s get started:
Choose a cell range that you need to remove the character.
You can see that cell E4 is selected. Now, enter the formula in the selected cell or in the Formula Bar.
REPLACE(D4, 1, 13, “”)
Basically, the function REPLACE has four arguments. The D4 cell is selected in the old_text. You can choose blank space (“”) as new_text. As per this example, you will notice the 13 characters are replaced from starting (1) with blank space.
Press the ENTER key and you will get the replaced character.
Remove Characters from the Left
When you need to delete a character from the left side of a string, RIGHT, and LEN or REPLACE functions are helpful. Also, remember that you need to indicate every single time how many characters you need to remove.
REPLACE(string, 1 num_chars, “”)
Or
RIGHT(string, LEN(string) – num_chars)
You can remove the first 2 characters in the A2 string by applying the following formula:
REPLACE(A2, 1, 2, “”)
RIGHT(A2, LEN(A2) – 2)
When you have to remove the first 3 characters, the formula would be as:
REPLACE(A2, 1, 3, “”)
RIGHT(A2, LEN(A2) – 3)
Below you can easily see the REPLACE formula in functional form. And RIGHT LEN functions, both give you the same outcome.
Remove Characters from Right
Generally, you can use the following formula when you need to remove some characters from the end of a cell:
LEFT(string, LEN(string) – num_chars)
Well, technically these formulas are similar. Below you can see some examples. Use 3 for num_chars when you have to delete the last 3 characters:
=LEFT(A2, LEN(A2) – 3)
Likewise, when you need to remove the last 5 characters, you will add 5 for num_chars:
LEFT(A2, LEN(A2) – 5)
Final Thoughts
That’s it!
This is how removing characters in Excel becomes convenient even for beginners. Simply by applying the above methods, anyone can handle the task easily. Just remember that practice is compulsory otherwise, you cannot become an expert.
