0

So basically just find and replace but some of my data is so messy

For example I wanna find the word "cat" and replace it with "mr.cat"

but there are some word like "redcat" that will show up in my search too

img

and If I use the match entire cell contents some word like "the cat" will not show

img2

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
nichkhun
  • 5
  • 2
  • Search for `" cat"`? With a space before the word? And if the left most word is cat then use partial match and search using `"cat"`. Then check if it is the left most word `Ucase(Left(trim(cell.value),3)) ="CAT"` – Siddharth Rout Jun 26 '19 at 06:59
  • It also depends on different kind of words.. For example what would you like to do with words like "this.cat", "that.cat", "Category" etc... – Siddharth Rout Jun 26 '19 at 07:01
  • I reccomend to use the powerful [Regular Expressions (regex)](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops): https://regex101.com/r/BuVKxJ/1 – Pᴇʜ Jun 26 '19 at 07:03

1 Answers1

0

Add another column and include the following formula

 =IF(EXACT(A5,"cat"),"mr.cat",SUBSTITUTE(A5," cat"," mr.cat"))

example

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Pan
  • 53
  • 6
  • This will not recognize if a cell begins with `"cat"` but is not exactly `"cat"` like in `cat in the house`. You will need a third subtitution criteria: `=IF(EXACT(A5,"cat"),"mr.cat",SUBSTITUTE(SUBSTITUTE(A5," cat"," mr.cat"),"cat ","mr.cat "))` that covers `"cat "`. – Pᴇʜ Jun 26 '19 at 07:54