How to Remove Text from a Cell in Excel (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 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 a Cell in Excel

In the following dataset, you can see the Product Name and Product ID columns. Using this dataset, we will go through 9 easy methods to remove text from the Excel cell. Here, we used Excel 365. You can use any available Excel version.

Dataset to Remove Text from Excel Cell


1. Use of Find and Replace Tool to Remove Text from a Cell in Excel

In this method, we will use the Find and Replace tool to remove “ID” before digits from the Product ID column.

Steps:

  • In the beginning, click CTRL+H to open the Find and Replace dialog box.
  • After that, write ID in the Find what.
  • Then, press Replace All.

 Using Find and Replace Dialog Box to Replace Text from Excel Cell

  • Therefore, you can look that our operation is done and a pop-up notification box is showing the result.
  • Then, click OK.

Popping up Dialog Box to Show the Number of Replacement

Hence, you can see the ID has been removed from the cells.

The Result after Using Find and Replace Feature

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


2. Use of SUBSTITUTE Function to Remove Text from a Cell

In this method, we will use the SUBSTITUTE function to remove text from the Excel cell. The SUBSTITUTE function replaces one or more instances of a given text string.

Steps:

  • First of all, type the formula in cell D5 which is given below:

=SUBSTITUTE(C5,”ID”,””)

Using SUBSTITUTE Function to remove text from Excel cell

  • Then, press ENTER.

Therefore, you can see the result in cell D5.

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

Applying the Fill Handle Tool

Therefore, you can see the complete Result column.

The Outcome After Using SUBSTITUTE Function

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


3. Applying REPLACE Function to Remove Text from a Cell in Excel

In this method, we will use the REPLACE function to remove text from the Excel cell. The REPLACE function replaces text in a specified position of a supplied string.

Steps:

  • First of all, write the formula in cell D5 as given below:

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

Applying REPLACE Function to Remove Text from Excel Cell

  • After that, press ENTER.
  • Hence, you can see the result in cell D5.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Employing Fill Handle Tool

Therefore, you can see the complete Result column.

Complete Result Column after using REPLACE Function

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


4. Combining MID and LEN Functions

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 Excel. And the LEN function is a text function in excel that returns the length of a string/ text.

Steps:

  • In the first place, enable editing in cell D5.
  • Then, type the formula.

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

Combining MID and LEN Functions to remove text from Excel Cell

Formula Breakdown

  • LEN(C5) → becomes
    • LEN(“ID1001”)
      • Output: 6
  • LEN(C5)-2 → becomes
    • LEN(6-2)
      • Output: 4
  • MID(C5,3,LEN(C5)-2) → becomes
    • MID(“ID1001”,3,4)
      • Output: 1001
  • Then, to see the output just press ENTER.
  • Therefore, you can see the result in cell D5.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Use of Fill Handle Feature

  • As a result, you can see the complete Result.

The output after using the MID and LEN Functions


5. Merging RIGHT and LEN Functions

Now I’ll use the RIGHT and LEN functions together to remove text from the Excel cell. The RIGHT function gives the number of characters from the right side of the string.

Steps:

  • First of all, in cell D5 type the given formula:

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

Merging RIGHT and LEN Functions to remove text from Excel Cell

Formula Breakdown

  • LEN(C5) → becomes
    • LEN(“ID1001”)
      • Output: 6
  • LEN(C5)-2 → becomes
    • LEN(6-2)
      • Output: 4
  • RIGHT(C5,LEN(C5)-2) → becomes
    • RIGHT(“ID1001”,4)
      • Output: 1001
  • At the moment, press ENTER.
  • Therefore, you can see the result in cell D5.
  • Moreover, we will drag down the formula with the Fill Handle tool.

Use of Fill Handle Feature

Hence, you can see the complete Result column.

Complete Result column after using the RIGHT and LEN Functions

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


6. Use of LEFT and FIND Functions to Remove Text from a Cell in Excel

Here we’ll use the LEFT and FIND functions together to remove text from the Excel cell. 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:

  • First of all, we will activate cell D5 and write the formula given below:

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

Use of LEFT and FIND Functions to remove text from Excel Cell

Formula Breakdown

  • FIND(“#”, C5) → The FIND function will find the position of ‘#’ in cell C5.
    • Output: 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.
    • Output: 1001
  • After that, press the ENTER button to get the result.
  • Therefore, you can see the result in cell D5.
  • Then, to apply it to the other cells just use the Fill Handle.

Employing Fill Handle Tool

Hence, you can see the complete Result column.

 The Output after using the LEFT and FIND Function

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


7. Removing Nth Instance of Certain Character

Now we’ll use the SUBSTITUTE function to remove the nth instance of a certain 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:

  • First of all, we will activate cell D5 and type the formula given below:

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

Using SUBSTITUTE Function for nth Instance

  • Then hit ENTER.
  • Hence, you can see the result in cell D5.
Note: If you want to remove the second ‘#’ then just type 2 instead of 1 in the formula.
  • After that, to copy the formula for the other cells you can use the AutoFill feature.

Applying Fill Handle Feature

As a result, you can see the complete Result column.

Complete Result Column after Removing Text from nth Instance.


8. Applying Flash Fill Feature

In this method, we will use the Flash Fill feature to remove text from Excel cell. This is the easiest of all methods.

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.

Applying Flash Fill to Remove Text from Excel Cell

  • After that, just press the ENTER button and all cells will be filled with that pattern.

The result after Applying Flash Fill

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


9. Use of VBA Code to Remove Text from a Cell

Lastly, we will describe the Excel VBA method to remove text from an Excel cell.

Steps

  • First of all, we will select the cell ranges where we want to apply VBA.
  • Here, we select cells D5:D9.
  • After that, we Right-click on the mouse to the title name of the sheet.
  • Then, we select View Code from the context menu.

Selecting View Code from Context Menu

  • At this moment, a VBA window will open up.
  • Then, we will type the codes given below:
Sub Delete_Last_Char()
n = Int(InputBox("Type Number of Last Characters tobe Removed: "))
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

VBA Code

  • Afterward, click the Run button to run the code.
  • Next, an Input window will appear.
  • Then, type the number of characters that you want to remove.
  • Here, we typed 3.
  • In addition, press OK.

 Typing Number in the Input Window

Therefore, the last 3 characters in the Result column are removed.

Complete Result column using VBA

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


Practice Section

You can download the above Excel file and practice the explained methods.

Practice Section of the explained methods


Conclusion

Here, we show you 9 methods to remove text from the Excel cell. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.


Related Article

Md. Sourov Hossain Mithun

Md. Sourov Hossain 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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo