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