Need to delete selected stings from cell content

I have a list of alphanumeric strings.

Starting Point
The cat sat on the mat.
The dog slept on the cushion.
The dromedary stood on the sand.
The lion did whatever it pleased.
The dromedary sat on the mat.


Fixed strings to delete:
The cat sat
The dog slept
The dromedary stood
The lion
The dromedary


Finishing point
on the mat.
on the cushion.
on the sand.
did whatever it pleased.
sat on the mat.


I tried nesting it using.:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,$A$22,""),A5,$A$23,""),A5,$A$24,""),A5,$A$25,""),$A$26,"")

However, I guess this only works when there are MULTIPLE substitutions to be made from within a single cell. I have ONE substitution to make from each cell in the list, from a relatively small pool of possible strings.


see attached sheet.

I don't do VBA.... NO knowledge of it whatsoever!!

Hello glen in C5 you can try
=SUBSTITUTE($A5,$A24,"",1)
then drag down.
Here One specify the position of the string to be deleted by specifying 1 substitute will delete first occurrence only. If you specify two then it will replace second occurrence only and will not effect any other occurrence

and if you use
=SUBSTITUTE($A5,$A24,"") then it will replace all occurrences of the string supplied.

Hi. It's red wine time... so I'll try this out in the morning.

Another way...
=MID(A5,SEARCH("on",A5,1),99)

Neither of these worked.

Ford's won't work for example 4 (the text to be retained doesn't necessarily begin with "on".

Hemesh's will work, but only does so because i didn't randomise the order of the examples and list of text to be deleted.

HOWEVER... if anyone is interested, this ghastly mess does work!! Brute force, perhaps...

=IF(LEFT(A5,LEN(A24))=A24,REPLACE(A5,1,LEN(A24),""),IF(LEFT(A5,LEN(A25))=A24,REPLACE(A5,1,LEN(A25),"")*IF(LEFT(A5,LEN(A26))=A24,REPLACE(A5,1,LEN(A26),""),IF(LEFT(A5,LEN(A27))=A24,REPLACE(A5,1,LEN(A27),""),IF(LEFT(A5,LEN(A28))=A24,REPLACE(A5,1,LEN(A27),""),"")))))

GHASTLY, but...

hello Glen,In that case try below array formula
copy paste below in C5, Then Hold control and Shift together then Hit enter to make it array formula

=SUBSTITUTE(A5,INDEX($A$24:$A$28,MAX(IF(LEFT(A5,LEN($A$24:$A$28))=$A$24:$A$28,ROW($A$24:$A$28)-23,0))),"")

then drag down. Now you can randomise

BRILLIANT.... Much more elegant than my monster. I had tried an array formula yesterday - but didn't get anywhere with it....

Glad I Could Help.

I didn't know such a thing could be done in Excel.. Good question by Glenn and a brilliant solution by Hemesh.

Thanks Saarang !