How to Remove Text From Excel Cell (9 Easy Ways)

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.

Find and Replace Tool to Remove Character From Multiple Cells

Now I’ll use the Find and Replace tool to remove “ID” before digits from the Product ID column.

Steps:

➤ Click Ctrl+H to open the Find and Replace dialog box.

➤ Write “ID” in the Find What bar.

➤ Then press Replace All

Find and Replace Tool to Remove Character From Multiple Cells

Look that our operation is done and a pop-up notification box is showing the result.

Read More: How to Remove Text from an Excel Cell but Leave Numbers (8 Ways)


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.

Steps:

➤ Type the formula in Cell D5 which is given below:

=SUBSTITUTE(C5,"ID","")

➤ Then just hit the Enter button.

Excel SUBSTITUTE Function to Remove Certain Character from String

After that, copy the formula using Fill Handle for the rest of the cells.

Read More: How to Remove Letters from Cell in Excel (10 Methods)


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.

Steps:

➤ Write the formula in Cell D5 as given below:

=REPLACE(C5,1,2,"")

➤ Now just press the Enter button.

REPLACE Function to Remove Text From Excel Cell

Finally, use the AutoFill option to copy the formula to the other cells.

Read More: How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)


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.

Steps:

➤ Enable editing in Cell D5

➤ Type the formula-

=MID(C5,3,LEN(C5)-2)

➤ To see the output just press the Enter button.

MID and LEN Functions to Remove Certain Character from a Cell in Excel

After that copy the formula using Fill Handle for the other cells.

MID and LEN Functions to Remove Certain Character from a Cell in Excel

👇 Breakdown of the Formula:

LEN(C5)

The LEN function will count the text length in Cell C5 and it will return as-

{6}

MID(C5,3,LEN(C5)-2)

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-

{1001}


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.

Steps:

➤ In Cell D5 type the given formula:

=RIGHT(C5,LEN(C5)-2)

➤ Press Enter button for the output.

RIGHT and LEN Functions Together to Remove First Character from a Cell in Excel

Then to copy the formula for the other cells use the AutoFill option.

👇 Breakdown of the Formula:

LEN(C5)

The LEN function will count the text length in the Cell C5 and it will return as-

{6}

RIGHT(C5,LEN(C5)-2)

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-

{1001}

Read More: How to Remove Text before a Space with Excel Formula (5 Methods)


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.

Steps:

➤ Activate Cell D5 and write the formula given below:

=LEFT(C5,FIND("#",C5)-1)

➤ After that, press the Enter button to get result.

LEFT and FIND Functions Together to Remove Text After a Specific Character in Excel

To apply it to the other cells just use the Fill Handle tool.

👇 Breakdown of the Formula:

FIND(“#”,C5)

The FIND function will find the position of ‘#’ in Cell C5 and it will return as-

{5}

LEFT(C5,FIND(“#”,C5)-1)

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-

{1001}

Read More: How to Remove Text After Character in Excel (3 Ways)


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 ‘#

Steps:

➤ By activating Cell D5 type the formula given below:

=SUBSTITUTE(C5,"#","",1)

➤ And then hit Enter button.

Excel SUBSTITUTE Function to Remove the nth Instance of a Specific Character

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.

Steps:

➤ 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.

Flash Fill to Remove Characters in an Excel Cell

➤ Now just press the Enter button and all cells will be filled with that pattern.

Read More: How to Remove Text between Two Characters in Excel (3 Easy Ways)


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.

Step 1:

➤ 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.

Excel VBA Codes to Remove Last Characters

Step 2:

➤ 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

Excel VBA Codes to Remove Last Characters

Step 3:

➤ Click the Play button to run the code.

A dialog box named Macro will open up.

Step 4:

➤ Then press the Run option.

Excel VBA Codes to Remove Last Characters

Step 5:

➤ Now type the number of characters that you want to remove.

➤ Press OK

Excel VBA Codes to Remove Last Characters

Look that the last 3 characters are removed.

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)


Conclusion

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.


Related Article

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo