SUBSTITUTE with "" if find one of the words in a cell

[Dahlia]

Hello Experts,

I want to strip off the below words if found any in bunch of words in a cell:-

1. bin
2. binti
3. bte
4. b.
5. bt.

input samples:

1. Marina binti Kamarudin
2. Kamal b. Mohd. Rusli
3. Sh. Hamidah bte Abg. Mokhtar
4. Mohd Firdaus bin Abdullah
5. Siti Nurul Nazihah bt. Md. Abd. Zaidel

expected output:-

1. Marina Kamarudin
2. Kamal Mohd. Rusli
3. Sh. Hamidah Abg. Mokhtar
4. Mohd Firdaus Abdullah
5. Siti Nurul Nazihah Md. Abd. Zaidel

I found this syntax SUBSTITUTE able to do this but only for one criteria only. I don't know how to improvise the formulas to be able to check multiple criterias and replace it with empty space if met one of them.

=SUBSTITUTE(text,find,replace)

sample : =SUBSTITUTE(A1,"binti ","") ==> assuming the text is in column A.

i tried to change it to =SUBSTITUTE(A1,{"binti ","bin ","bte ","bt. ","b."},"") , assuming that the curley bracket would be for multiple criterias with OR operator but I got an #ERROR! as a result.

Appreciate if anyone could share solution on this.

Thanks a lot.

DZ

[FDibbins]

No, you need to just keep adding SUB to your formula
=substitute(SUBSTITUTE(A1,"binti ",""),"bin")...........................

[:) Sixthsense :)]

This will work when the replacement text is single.

Data Range

	A	B	C
1	bin	Marina binti Kamarudin	Marina Kamarudin
2	binti	Kamal b. Mohd. Rusli	Kamal Mohd. Rusli
3	bte	Sh. Hamidah bte Abg. Mokhtar	Sh. Hamidah Abg. Mokhtar
4	b.	Mohd Firdaus bin Abdullah	Mohd Firdaus Abdullah
5	bt.	Siti Nurul Nazihah bt. Md. Abd. Zaidel	Siti Nurul Nazihah Md. Abd. Zaidel

Data Range

	C
1	=SUBSTITUTE(B1,LOOKUP(2,1/SEARCH(" "&$A$1:$A$5&" ",B1),$A$1:$A$5),"")

[azumi]

Try :

=SUBSTITUTE(A1,LOOKUP(2*15,SEARCH($J$2:$J$6,SUBSTITUTE(SUBSTITUTE(A1,",","")," ","")),$J$2:$J$6),"")

[Dahlia]

Woww! Thanks so much for everyone's solution.

I have tried each and each works like a charm! Really appreciate it so much!

Best Regards,
DZ

[FDibbins]

Thanks for the feedback.