Delete String If Found Matching String

[muhammad susanto]

hi...

in the same line/row....

COL. E /ROW	COL.F / ADDRESS	COL. G / REGION	COL. H / AFTER FORMULA
2	JL. HANG LEKIU NO 29 SUKAMULIA SAIL	SAIL	JL. HANG LEKIU NO 29 SUKAMULIA
3	JL. SAOMATI RT 03/07, SAIL/TENAYAN RAYA	TENAYAN RAYA	JL. SAOMATI RT 03/07, SAIL/
4	JL. BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR	RUMBAI PESISIR	JL. BELANAK IV/452 LIMBUNGAN
5	JL. LANGSAT NO.11	SUKAJADI	JL. LANGSAT NO.11
6	JL. KIJANG NO 24 HARJOSARI/SUKAJADI	SUKAJADI	ETC…
7	JL. HANG TUAH KP. KELAPA NO 38 SAIL	SAIL
8	JL. KEMUNING GG MESJID NO 30A SENAPELAN	SENAPELAN
9	JL. GRIYA TIKA UTAMA SAI MINTAN BLOK N. NO.15 BKT.RAYA	BUKIT RAYA
10	JL. BUNGA HARUM 7 HARJOSARI/SUKAJADI	SUKAJADI
11	JL. JEND.SUDIRMAN. ATAS SIMP.TIGA/B. RAYA	BUKIT RAYA
12	JL. PESISIR NO 127 MERANTI PANDAK RUMBAI PESISIR	RUMBAI PESISIR
13	JL. MUSLIMIN TANAH DATAR/PKB KOTA	PEKANBARU KOTA
14	JL. PINUS GG.SUNGKAI NO 1 TANGKERANG UTARA	BUKIT RAYA
15	JL. LEMBAH MULIA NO.01 SUKAMAJU/SAIL	SAIL
16	JL. SINGKAWANG NO 12 C	SAIL
17	JL. DURIAN NO 93 PEKANBARU	SUKAJADI
18	JL. KARTIKA UJUNG UMBAN SARI RUMBAI	RUMBAI
19	JL. TASKURUN NO.34 WONOREJO/MARPOYAN DAMAI	MARPOYAN DAMAI
20	JL. RAWAWIRI NO.9 TKG.LABUAI	BUKIT RAYA
21	JL. KAPAS NO 4 BUKIT RAYA	BUKIT RAYA
22	JL. KEMBANG SARI NO 18 SAIL	SAIL

<tbody>
</tbody>

if the string match, delete duplicate string and display not duplicate...
how to figure it out?

note : red font i hope the clue can explain..

thanks in advance..

m.susanto

 

[mole999]

This works

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"BUKIT RAYA",""),"MARPOYAN DAMAI",""),"PEKANBARU KOTA",""),"RUMBAI",""),"PESISIR",""),"SAIL",""),"SENAPELAN",""),"SUKAJADI",""),"TENAYAN RAYA",""))

 

[pgc01]

Hi

Try:

=TRIM(SUBSTITUTE(F2;G2;""))

Copy down

 

[muhammad susanto]

hi guys...you are really great!!!
FANTASTIC!!!

thank you very much....