Find an exact match in a string and substitute with another value.

[MrOllyR]

I was wondering if anyone can help me.

Sentence in cell A1: The security broke up a fight that had started between the two groups of people. Ended broke_up

Extracting the main sentence without the last two words
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1)))
Returns in A2: The security broke up a fight that had started between the two groups of people.

Extracting the last word from the string
Extracting the last word from the string "broke_up" (A1) in A3: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
Replacing the "_" with " " in the last word "broke_up" (A3) in order to make up to three separate words in A4: =SUBSTITUTE(A3,"_"," "). In this instance there are only two words- "broke" and "up".
Extracting each word from A4 into cells A5, A6 and A7:
A5: =TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))), (1-1)*LEN(A4)+1, LEN(A4))). Returns "broke"
A6: =TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))), (2-1)*LEN(A4)+1, LEN(A4))). Returns "up".
A7: =TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))), (3-1)*LEN(A4)+1, LEN(A4))). Returns nothing as there are only 2 words.

Extracting the second to last word from the string
Extracting the second to last word "Ended" from the string (A1) in A8: =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60))
Replacing the "_" with " " (A8) in A9: =SUBSTITUTE(A8,"_"," "). In this instance, the string is made up of one word so will return the same value "Ended".

Output
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,A4,"________"),A5,"________"),A6,"________")&CHAR(10)&"(" & A8 & ")"

This returns:

The security ________ ________ a fight that had started between the two gro________s of people.
(Ended)

This is good except it has substituted the letters "u" and "p" in the word "groups", too. Is there a way of adapting what I have done so it only returns words which meet the criteria and not if the letters are within other words like the example above?

Thank you in advance.

 

[MrOllyR]

Sorry, it should be: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,A5,"________"),A6,"________"),A7,"________")&CHAR(10)&"(" & A9 & ")"

 

[Peter_SSs]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," "&A5&" "," ________ ")," "&A6&" "," ________ ")," "&A7&" "," ________ ")&CHAR(10)&"(" & A9 & ")"

 

[MrOllyR]

Thank you! Much appreciated!

 

[Peter_SSs]

Thank you! Much appreciated!

You're welcome. Thanks for the follow-up.