Thread: Find and Replace Instances of a Word in a Cel

Hi All

I have a huge amount of data and my problem is I need to work out how to find a certain amount of words from a cell, and either replace or delete them. I am using another formula to count how many instances of each word are in a cell, I have a max limit of 15 repeating words for a given body of text about 300 words.

So I hoping someone may know a formula that will find x instances of a word (doesnt matter there location or order in the text) and delete them or repalce them someting

Doing this manually or event with the find replace tool will be painfull

Any help would be greatly appreciated

Thanks Marx

It would be interesting to know what formula you are using to find the instances! Usually if the word was dog, will tell you the occurances

=LEN(A1)-LEN(SUBSTITUTE(A1,"dog",""))/len("dog")

To just delete the word dog every time
SUBSTITUTE(A1,"dog","")

or perhaps

trim(SUBSTITUTE(A1,"dog",""))

So it is in the above formula!

Regards

Dav

Thanks for reply, the formula you suggested at the top is the formula I am using to find the instances of the word,

Correct me if im wrong but will your solution delete all the instances of the word ?

Where my problem comes in is that I need to delete only the words that are over the max limit of 15, so if the above formula tells me say 18 instances of "dog", I would like a formula that I can specify to remove 3 instances of "dog" from the text but leave the rest,

Thanks Marx

OK its painful mainly because its repetitive and you need 3 cells due to the limit in nested ifs

You need to replace the first 15 instances of "dog" with a character string that does not appear in the text string, I have choosen zzzz, which may work for you, otherwise you may have to change it.

if the value is in a1
in a2 put
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"dog","z zzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz" ,1),"dog","zzzz",1)

in a3 put
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"dog","zzzz",1),"do g","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1),"dog","zzzz",1)

in a4 put
=SUBSTITUTE(SUBSTITUTE(A3,"dog",""),"zzzz","dog")

a4 is your answer. It may be quicker to reference the dog and zzzz by 2 cells on your sheet so you can change them more quickly, but I hope its a start. There are potential problems if a formula gets too long as well, but I hope this is a start

I would have probably put it accross a row, but wanted to make sure it works

Let me know how you get on

Regards

Dav