Remove String Line If Contains Specific Word

[Tvynn]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Hi, would like to seek your kind help to remove those original statements that contain "/ERCT" as below:



Case1 Case2 Original Statement Desired Result

1 1 B@gmail.com/ERCT(Renu/20Oct18)<wbr>; A@gmail.com/CRS(Amy/30Oct18)[FONT=&quot];[/FONT]
A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18)
A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com
B@gmail.com



1 A@gmail.com/(Amy/30Oct18); A@gmail.com/(Amy/30Oct18)[FONT=&quot];[/FONT]
A@gmail.com/reinstate(Daphne/<wbr>20Oct18) A@gmail.com/reinstate(Daphne/<wbr>20Oct18)
A@gmail.com/ERCT(Renu/20Oct18) B@gmail.com
B@gmail.com




1 Z@gmail.com/CRS(Amy/30Oct18); Z@gmail.com/CRS(Amy/30Oct18)[FONT=&quot];[/FONT]
Z@gmail.om/reinstate(Amy/<wbr>30Oct18); Z@gmail.com/reinstate(Amy/<wbr>30Oct18)[FONT=&quot];[/FONT]
B@gmail.com/(Renu/23Oct18); B@gmail.com/(Renu/23Oct18)[FONT=&quot];[/FONT]
/ERCT(Renu/23Oct18); A@gmail.com/(Renu/23Oct18)[FONT=&quot];[/FONT]
A@gmail.com/(Renu/23Oct18);
Z@gmail.com/ERCT(Renu/23Oct18)


A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/CRS(Amy/30Oct18)[FONT=&quot];[/FONT]
A@gmail.com/(Daphne/20Oct18) A@gmail.com/(Daphne/20Oct18)
/ERCT(Renu/20Oct18) [FONT=&quot]/ERCT(Renu/20Oct18)[/FONT]
B@gmail.com B@gmail.com


Note:
1. [FONT=&quot]Either Case1 or Case2 is not blank, remove entire statement that contain "ERCT" (remove the blank break line if any)
[/FONT]2. Both Case1 or Case2 are blank (last situation), keep existing statement, including entire statement that contain "ERCT"
3. All statements of each situation listed above are in a same cell instead of different cells

Appreciate your kind help! Many thanks!

 

[JumboCactuar]

Do you need formula/VBA?

Can easily be done with excels built in Find/Replace

 

[Tvynn]

Hi, would like to seek your kind help to remove those original statements that contain "/ERCT" as below:

Case1	Case2	original statement	keep only not "/ERCT" after formula	Explanation
1	1	B@gmail.com/ERCT(Renu/20Oct18)<wbr>; A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com	A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com	Either Case1 or Case2 is not blank, remove entire statement that contain "/ERCT"
1		A@gmail.com/(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18); A@gmail.com/ERCT(Renu/20Oct18); B@gmail.com	A@gmail.com/(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18); B@gmail.com	Either Case1 or Case2 is not blank, remove entire statement that contain "/ERCT" (remove the blank break line of 1 ERCT if possible)
	1	Z@gmail.com/CRS(Amy/30Oct18); Z@gmail.com/reinstate(Amy/<wbr>30Oct18); B@gmail.com/(Renu/23Oct18); /ERCT(Renu/23Oct18); A@gmail.com/(Renu/23Oct18); Z@gmail.com/ERCT(Renu/23Oct18);	Z@gmail.com/CRS(Amy/30Oct18); Z@gmail.com/reinstate(Amy/<wbr>30Oct18); B@gmail.com/(Renu/23Oct18); A@gmail.com/(Renu/23Oct18);	Either Case1 or Case2 is not blank, remove entire statement that contain "/ERCT" (remove the blank break line of 2 ERCT if possible)
		A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/(Daphne/20Oct18) /ERCT(Renu/20Oct18); B@gmail.com	A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/(Daphne/20Oct18) /ERCT(Renu/20Oct18) B@gmail.com	Both Case1 or Case2 are blank, keep existing statement, including entire statement that contain "/ERCT"

<tbody>
</tbody>

* Note: All statements of each situation listed above are in a same cell instead of different cellsKindly ignore previous message. Appreciate your kind help! Many thanks!



<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Tvynn]

Hi Jumbo, yes can try also, but if formula can do it then would also be good, thanks!

Case1	Case2	original statement	keep only not "ERCT" after formula	Explanation
1	1	B@gmail.com/ERCT(Renu/20Oct18)<wbr>; A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com	A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com	Either Case1 or Case2 is not blank, remove entire statement that contain "ERCT"
1		A@gmail.com/(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) A@gmail.com/ERCT(Renu/20Oct18)； B@gmail.com	A@gmail.com/(Amy/30Oct18); A@gmail.com/reinstate(Daphne/<wbr>20Oct18) B@gmail.com	Either Case1 or Case2 is not blank, remove entire statement that contain "ERCT" (remove the blank break line of 1 ERCT if possible)
	1	Z@gmail.com/CRS(Amy/30Oct18); Z@gmail.com/reinstate(Amy/<wbr>30Oct18); B@gmail.com/(Renu/23Oct18); /ERCT(Renu/23Oct18); A@gmail.com/(Renu/23Oct18); Z@gmail.com/ERCT(Renu/23Oct18)	Z@gmail.com/CRS(Amy/30Oct18); Z@gmail.com/reinstate(Amy/<wbr>30Oct18); B@gmail.com/(Renu/23Oct18); A@gmail.com/(Renu/23Oct18);	Either Case1 or Case2 is not blank, remove entire statement that contain "ERCT" (remove the blank break line of 2 ERCT if possible)
		A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/(Daphne/20Oct18) /ERCT(Renu/20Oct18) B@gmail.com	A@gmail.com/CRS(Amy/30Oct18); A@gmail.com/(Daphne/20Oct18) /ERCT(Renu/20Oct18) B@gmail.com	Both Case1 or Case2 are blank, keep existing statement, including entire statement that contain "ERCT"

<tbody>
</tbody>