1

Is it possible to build a formula in MS excel that would replace only exact word? For example:

My apple is red. I want to replace red with green, so it goes My apple is green.

I know I can use =replace

But if my sentence is: My apple is reddish. The formula should ignore and not replace red part.

I was thinking to use this combination " "&"red"&" " so it would change only if there is space before or after.But for some reason I can't make it work.

Edit: This what I have

enter image description here

=REPLACE(A1,SEARCH(" "&E1,A1),LEN(E1)+1," "&E1)

If I add &" " after E1 it will break, because there is no space left.

Titas aleksandravičius
  • 35
  • 7
  • Why not just `" red "`? Strange that it does not work. What happens? Does it replace reddish to? – Andreas Aug 11 '16 at 14:59
  • `"My apple is red."` doesn't have a space at the end is probably why that doesn't work. – Alexis Olson Aug 11 '16 at 15:00
  • Yes, you are correct. I get an error, because there is no space at the end. Maybe there is a smarter way how to do this replacement? – Titas aleksandravičius Aug 11 '16 at 15:07
  • You could do it with a REGEX in a VBA macro - detail on the how is at http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – JetSetJim Aug 11 '16 at 15:12

1 Answers1

0

If you do not actually have the . at the end of the statement then something like this will do it:

=IF(SEARCH("red",A1)+LEN("red")-1 = LEN(A1),SUBSTITUTE(A1," red"," green"),IF(SEARCH("red",A1)=1,SUBSTITUTE(A1,"red ","green "),SUBSTITUTE(A1," red "," green ")))

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81