Need a formula to find and substitute a word in a text if a condition is met...

In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".

I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.

Any help would be much appreciated!

Something like this?

=iferror(if(find(",",A1)>=1,substitute(A1,"change/s","changes"),),substitute(A1,"change/s","change"))

Try this out:

Formula:

Please Login or Register  to view this content.

You're welcome and thanks!