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

Get FREE Advanced Excel Exercises with Solutions!

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.

The following image shows an overview of the article which represents the removal of specific text from cells in Excel.

Overview of Removing Specific Text from Cell

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 methods described below. Sample dataset of removing specific text from cell

Here, I have used Microsoft Excel 365 Version. You may use any other version at your convenience.


1. Using Find & Replace Option to Remove Specific Text from Cell in Excel

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.

Selection of column to apply find and replace feature

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

Performing find and replace feature to remove specific character

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

Putting specific characters to replace from the selected cells

Now another Wizard will pop up
➤ Press OK

Confirmation of replacement of characters from multiple cells

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

Final output using the find and replace feature

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

Read More:  How to Remove Letters from Cell in Excel


2. Removing Text from Cell with Excel’s 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. To show this result I have added an Items column. You can use the Flash Fill feature to perform this task.

Sample dataset to remove characters using the flash fill feature

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

Typing manually to apply flash fill feature

➤ Press ENTER.

Choosing cells to perform flash fill option

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

Clicking flash fill option from editing feature

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

Final output removing specific text from cell

Read More: How to Remove Text from an Excel Cell but Leave Numbers


3. Using SUBSTITUTE Function to Remove Specific Text from Cell in Excel

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.

Sample dataset to apply SUBSTITUTE function to remove specific text from cell

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.

Applying SUBSTITUTE formula to substitute specific text from a cell

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

Result by substituting extra characters from a cell

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

Final output using the SUBSTITUTE formula removing specific text


4. Applying MID Function to Delete Specific Text from Cell

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 “-”. To show this result I have added an Items column. You can use the MID function and the Find function for this case.

Sample dataset for removing specific characters applying the MID formula

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.

Performing the MID function to remove characters

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

Result of removing characters from a column using the MID formula

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

Final output applying the MID formula removing characters from a column


5. Inserting Excel’s RIGHT Function to Remove Specific Text from Cell

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.

Sample dataset to apply RIGHT function for removing characters

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.

Formula of RIGHT to remove specific character

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

Pulling the fill handle down to fill all the cells with the RIGHT function

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

Final output by removing specific characters by using RIGHT function

Read More: How to Remove Text before a Space with Excel Formula


6. Applying LEFT Function to Remove Text from Cell in Excel

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.

Sample dataset to remove characters using the 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.

Formula of LEFT to remove specific characters

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

Dragging the fill handle to fill the column

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

Final output using the LEFT function


7. Using REPLACE Function to Remove Text from Cell in Excel

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.

Sample dataset to delete texts or numeric values

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.

Formula of REPLACE to delete specific characters from a cell

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

Dragging the fill handle to apply the REPLACE function for other cells

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

Final output using the REPLACE formula


8. Removing Text After Specific Character in Excel

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

Sample dataset to remove text after specific character

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

Pressing keyboard shortcut to open find and replace window

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.

Providing specific text to replace

Now another Wizard will pop up
➤ Press OK

Confirming replacement of specific texts

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

Final output removing text after specific character

Read More: How to Remove Text After Character in Excel


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.

Sample dataset for 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.

Peroforming SUBSTITUTE formula for removing multiple characters

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

Output removing multiple characters using the SUBSTITUTE formula

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

Final output removing multiple characters


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.

Saqmple dataset for deleting text before Nth occurance

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.

Combination of RIGHT, SUBSTITUTE, CHAR, LEN, FIND functions to delete text before Nth occurance

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

Output using the combination of RIGHT, SUBSTITUTE, CHAR, LEN, FIND functions

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

Final output by deleting text before Nth occurance

Read More: How to Remove Everything After a Character in Excel


11. Removing Texts after the nth Occurrence of a Specific Character

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

Sample dataset for deleting text after Nth occurance

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.

Combination of LEFT, SUBSTITUTE, CHAR, FIND formula to delete text after Nth occurance

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

Result of applied formula deleting text after Nth occurance

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

Final output by deleting text after Nth occurance

Read More: How to Remove Text between Two Characters in Excel


Download Excel Workbook


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.

<< Go Back To Excel Remove Text | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo