How to remove particular word from Excel cell

Hi,

Today, We are going to share a useful tip if you work with Microsoft Excel. Many times, we have to remove some particular word from excel cell which might be spread across entire Row or Column.

Requirement

Lets have a look at below screen to understand requirement more clearly.

ExcelSubTotal

In above screenshot, we applied Subtotal utility of Microsoft Excel which summarizes data based on records. Above sheet of companies have dispatched mentioned quantity. Here we have applied Count function with subtotal so all the filters have been added Count at last of each company. But while presenting data, showing count or average or sum in each cell does not look good. We might like to remove the word Count here.

Procedure

Lets move to process without wasting a second.

We will apply a Text Function here which we have already tried to elaborate with you in earlier post.

Text Function – Part 1  Text Function – Part 2  Text Function – Part 3

We have copied the summary to new sheet so we can only show the summary instead of all rows. We have added an extra column to apply formula and display the final output.

[code]

=SUBSTITUTE(A2,”Count”,””)

[/code]

Have a look at below screen.

ExcelSubstitute1

Lets understand the formula above. This is text function which takes four parameter among them, first one is Text in which formula is to be applied, second is the text phrase to be removed, in this case “Count” is the phrase we wanted to remove, third one is the new text which will replace the text. In above case we have just put a blank or null inside quote.

Now lets press enter and fill the formula. Here is our final output.

ExcelSubstituteFinal

Yes, it is the final output as required. We have successfully removed the word Count from above sheet. You can try this with your data.

Thanks for reading. Do not forget to provide feedback with your view or suggestion to improve us as comment below.

John Bhatt

Aka, Prithvi Raj Bhatt, A Proud Nepali, Blogger, Software Developer, ASP.NET, Web Designer & Developer, Faculty and a Troubleshooter blog @PRsBlog

Leave a Reply

Your email address will not be published. Required fields are marked *

Inline
Inline