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

If you are looking for some of the easiest ways to remove specific text from a cell in Excel, then you will find this article worth it. While working with Excel and dealing with large datasets, sometimes it becomes necessary to delete some text from a cell.

You can do this manually, but it will waste your valuable time. Let’s dive into the article and get some of the easiest ways to remove specific text from cells in Excel.

Download Excel Workbook

11 Ways to Remove Specific Text from Cell in Excel

I have a dataset where I have 3 columns. I will use different cells to remove some specific texts and extract some important data using the following methods. Here, I have used Microsoft Excel 365 Version for this purpose.

data table

Method-1: Using Find & Replace Option to Remove Specific Text

For this method, I will use the first column; Product Code where with different items the company name has been included with a character “-”. So, I will extract the product name and delete the company name including this character. You can use the Find & Replace option to perform this task.

find & replace

Step-01:
➤Select the datatable
➤Go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

find & replace

Then Find and Replace Dialog Box will appear
➤Write -XYZ  in the Find What Option
➤Select Replace All Option

find & replace

Now another Wizard will pop up
➤Press OK

find & replace

Result:
After that, you will get the Items name as a result.

excel remove specific text from cell

Here, I’ve changed the Product Code column to Items.

Method-2: Using Flash Fill Feature

Here, I will use the first column; Product Code where with different items the company name has been included with a character “-”. So, I will extract the product name and delete the company name including this character. For showing this result I have added an Items column. You can use the Flash Fill feature to perform this task.

flash fill feature

Step-01:
➤Write down the part of the text you want to keep in Cell E5

flash fill feature

➤Press ENTER

flash fill feature

Step-02:
➤Follow Home Tab>>Editing Dropdown>>Fill Dropdown>>Flash Fill Option

flash fill feature

Result:
Now you will get your desired output in the Items column

flash fill feature

Method-3: Using SUBSTITUTE function to Remove Specific Text

Like previous methods, I will use the first column; Product Code where with different items the company name has been included with a character “-”. Unlike the previous one in this section, I’ll use the SUBSTITUTE function for this purpose.

SUBSTITUTE function

Step-01:
➤ Select Cell E5
➤Type the following formula

=SUBSTITUTE(B5,"-XYZ","")

B5 is the text, -XYZ is the old text you want to replace and it will be replaced by Blank.

SUBSTITUTE function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

SUBSTITUTE function

Result:
Then you will get the texts with removing the unwanted part in the Items column

SUBSTITUTE function

Method-4: Using MID function

Similar to the previous one I will use the first column; Product Code where with different items the company name has been included with a character “-”. For showing this result I have added an Items column. You can use the MID function and the Find function for this case.

MID function

Step-01:
➤ Select Cell E5
➤Type the following formula

=MID(B5,1,FIND("-",B5,1)-1)

B5 is the text, 1 is the start number,
FIND("-", B5, 1)-1 here, FIND will give the position of character “-” and then the value will be deducted from 1. It will be the number of characters in the MID function.

MID function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

MID function

Result:
Now you will get your desired texts in the Items column

MID function

Method-5: Using RIGHT function

In the Color with Code column, I have some colors combined with their code no. For removing the code no you can use the RIGHT function.

RIGHT function

Step-01:
➤ Select Cell E5
➤Type the following formula

=RIGHT(D5,LEN(D5)-FIND("-",D5,1))

D5 is the text,
LEN(D5) is the total length of the string
FIND("-", D5,1) will give the position of character “-” and then the value will be deducted from the total length of the string and it will be the number of characters for the RIGHT function.

RIGHT function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

RIGHT function

Result:
Now you will get only the name of the colors as below.

RIGHT function

Method-6: Using LEFT function

If you want to extract the color code and remove the color name from the column Color with Code then you can use the LEFT function. I have added a Color Code column for this reason.

LEFT function

Step-01:
➤ Select Cell E5
➤Type the following formula

=LEFT(D5,3)

D5 is the text,
3 is the number of characters you want to extract.

LEFT function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

LEFT function

Result:
Afterward, you will get the code of colors in the Color Code column.

LEFT function

Method-7: Using REPLACE function

For removing the color codes in the Color with Code column you can use the REPLACE function. For having the outputs I have added the Color column.

REPLACE function

Step-01:
➤ Select Cell E5
➤Type the following formula

=REPLACE(D5,1,4,"")

D5 is the text,
1 is the start number, 4 is the number of characters you want to replace with blank.

REPLACE function

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

REPLACE function

Result:
Afterward, you will get the name of colors in the Color column.

REPLACE function

Method-8: Removing Text after Specific Character

Suppose, you want to remove the last three sizes in the Size column. So, you can use the Find & Replace option here.

remove text after specific character

Step-01:
➤Select the datatable
➤Go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

remove text after specific character

Then Find and Replace Dialog Box will appear
➤Write “,*” in the Find What Option
➤Select Replace All Option
,*  will help to find all of the texts after the comma.

remove text after specific character

Now another Wizard will pop up
➤Press OK

remove text after specific character

Result:
Then you will get the first sizes in the Size column.

remove text after specific character

Method-9: Removing Multiple Characters Simultaneously

Suppose, you want to remove all of the brackets separating colors in the Color with Code column and use “-” as a separator. So, you can use the SUBSTITUTE function here.

removing multiple characters

Step-01:
➤ Select Cell E5
➤Type the following formula

=SUBSTITUTE(SUBSTITUTE(D5,"(","-"),")","")

D5 is the text,
SUBSTITUTE(D5,"(","-") here,“(” is the old text you want to replace with “-“.
Then this output will be used by another SUBSTITUTE function.

removing multiple characters

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

removing multiple characters

Result:
Now you will get your desired format in the output column as below.

removing multiple characters

Method-10: Deleting Texts before the nth occurrence of a Specific Character

Suppose, you want to get only the last size instead of the 4 sizes in the Size column. To do this you can use the RIGHT function and the SUBSTITUTE function.

deleting text before nth occurrence

Step-01:
➤ Select Cell E5
➤Type the following formula

=RIGHT(SUBSTITUTE(C5,",",CHAR(9),3),LEN(C5)-FIND(CHAR(9),SUBSTITUTE(C5,",",CHAR(9),3),1)+1)

C5 is the text,
SUBSTITUTE(C5,",", CHAR(9),3) here comma will be replaced by CHAR(9)(blank) and 3 is used to define the position of a comma before which I want to remove texts
Then the RIGHT function will give the output as the last size number from the right side.

deleting text before nth occurrence

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

deleting text before nth occurrence

Result:
Now you will get your desired sizes in the Size column

deleting text before nth occurrence

Method-11: Deleting Texts after the nth occurrence of a Specific Character

For getting only the first size instead of 4 sizes in the Size column, you can use the LEFT function and the SUBSTITUTE function.

deleting text after nth occurrence

Step-01:
➤ Select Cell E5
➤Type the following formula

=LEFT(SUBSTITUTE(C5,",",CHAR(9),1),FIND(CHAR(9),SUBSTITUTE(C5,",",CHAR(9),1),1)-1)

C5 is the text,
SUBSTITUTE(C5,",", CHAR(9),3) here comma will be replaced by CHAR(9)(blank) and 1 is used to define the position of a comma after which I want to remove texts
Then the LEFT function will give the output as the last size number from the left side.

deleting text after nth occurrence

Step-02:
➤Press ENTER
➤Drag down the Fill Handle tool.

deleting text after nth occurrence

Result:
Then you will get the first sizes in the Size column.

deleting text after nth occurrence

Practice Section

For doing practice on your own we have provided a section for this purpose in a sheet named Practice. Please do it by yourself.

practice

Conclusion

In this article, I have tried to explain the easiest methods to remove specific text from cells in Excel. Hope you will find it helpful. Please provide any further suggestions if you have any. Thank you.

 

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo