There are several methods to remove text from an Excel cell. In this article, I’ll show 9 methods including VBA to remove text from the Excel cells. Just have a look at the following methods properly and you’ll find them useful to apply.
Download Practice Book
You can download the free practice Excel template from here and practice on your own.
9 Easy Methods to Remove Text From Excel Cell
Method 1: Use Find and Replace Tool to Remove Character From Multiple Cells
Let’s get introduced to our dataset first. Here I have placed some products and their ID between 2 columns and 6 rows.
Now I’ll use the Find and Replace tool to remove “ID” before digits from the Product ID column.
➤ Click Ctrl+H to open the Find and Replace dialog box.
➤ Write “ID” in the Find What bar.
➤ Then press Replace All
Look that our operation is done and a pop-up notification box is showing the result.
Method 2: Insert Excel SUBSTITUTE Function to Remove Certain Character from String
In this method, I’ll use the SUBSTITUTE function to remove certain characters like the previous method. SUBSTITUTE function replaces one or more instances of a given text string.
➤ Type the formula in Cell D5 which is given below:
➤ Then just hit the Enter button.
After that, copy the formula using Fill Handle for the rest of the cells.
Method 3: Enter REPLACE Function to Remove Text From Excel Cell
Now we’ll do the same operation using the REPLACE function. REPLACE function replaces text in a specified position of a supplied string.
➤ Write the formula in Cell D5 as given below:
➤ Now just press the Enter button.
Finally, use the AutoFill option to copy the formula to the other cells.
Method 4: Combine MID and LEN Functions to Remove Certain Character from a Cell in Excel
Here in this method, we’ll use the combination of MID and LEN functions to remove certain characters from a cell. The MID function is used to find out strings and return them from any mid part of the excel. And the LEN function is a text function in excel that returns the length of a string/ text.
➤ Enable editing in Cell D5
➤ Type the formula-
➤ To see the output just press the Enter button.
After that copy the formula using Fill Handle for the other cells.
👇 Breakdown of the Formula:
The LEN function will count the text length in Cell C5 and it will return as-
Here we have subtracted 2 from the result of the LEN function because we want to keep only 4 digits. 3 is used to locate the starting position of the text for the MID function. Finally it will return as-
Method 5: Use RIGHT and LEN Functions Together to Remove First Character from a Cell in Excel
Now I’ll use the RIGHT and LEN functions together to do the operation. The RIGHT function gives the number of characters from the right side of the string.
➤ In Cell D5 type the given formula:
➤ Press Enter button for the output.
Then to copy the formula for the other cells use the AutoFill option.
👇 Breakdown of the Formula:
The LEN function will count the text length in the Cell C5 and it will return as-
Now we’ll keep 4 digits from the right side of the string. For that we have subtracted 2 from the result of the LEN function. Then the RIGHT function will give the result as-
Method 6: Insert LEFT and FIND Functions Together to Remove Text After a Specific Character in Excel
Here we’ll use LEFT and FIND functions together to remove text after a specific character. The LEFT function is used to extract text from the left side of a text string. And the FIND function returns the location of a substring in a string. I have rearranged my dataset as the image below. Now I’ll remove the characters after ‘#’ including itself from every cell.
➤ Activate Cell D5 and write the formula given below:
➤ After that, press the Enter button to get result.
To apply it to the other cells just use the Fill Handle tool.
👇 Breakdown of the Formula:
The FIND function will find the position of ‘#’ in Cell C5 and it will return as-
We have subtracted 1 because we want to remove the ‘#’ too. Then the LEFT function will keep the number of characters from the left side. And the result will return as-
Method 7: Use Excel SUBSTITUTE Function to Remove the nth Instance of a Specific Character
Now we’ll use the SUBSTITUTE function again to remove nth instance of a specific character. That’s why I have rearranged my dataset like the image below. See that there are two ‘#’ in every cell. I’ll remove the first ‘#’
➤ By activating Cell D5 type the formula given below:
➤ And then hit Enter button.
➣ Note: If you want to remove the second ‘#’ then just type 2 instead of 1 in the formula.
To copy the formula for the other cells you can use the AutoFill option.
Method 8: Apply Flash Fill to Remove Characters in an Excel Cell
It’s the easiest method to remove characters. Excel Flash Fill is a special tool that analyzes the information you are entering and automatically fills data when it identifies a pattern.
➤ First, type the digits you want to keep in Cell D5
➤ Then when you are going to type in the next cell then Excel will catch the pattern and will show it.
➤ Now just press the Enter button and all cells will be filled with that pattern.
Method 9: Embed Excel VBA Codes to Remove Last Characters
Lastly, I’ll describe the Excel VBA method to remove the last characters of cells.
➤ Select the cell ranges where you want to apply VBA
➤ Right-click your mouse to the title name of the sheet.
➤ Press View Code from the context menu.
A VBA window will open up.
➤ Type the codes given below:
Sub DeleteLastChar() n = Int(InputBox("Enter the Number of Removed Last Characters: ")) For i = 1 To Selection.Rows.Count Selection.Cells(i, 1) = Left(Selection.Cells(i, 1), Len(Selection.Cells(i, 1)) - n) Next i End Sub
➤ Click the Play button to run the code.
A dialog box named Macro will open up.
➤ Then press the Run option.
➤ Now type the number of characters that you want to remove.
➤ Press OK
Look that the last 3 characters are removed.
I hope all of the methods described above will be good enough to remove text from Excel cells. Feel free to ask any questions in the comment section and please give me feedback.