Extract Some Text Certainty

[Jhon Mustofa]

hi all..

how to extract text this below :

Jl. Hang Lekir VIII No. 10,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U ----------- Pekanbaru (after formula)
Jalan Gatot Subroto Nomor 31 Pekanbaru - 0761-23513,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U ----------Pekanbaru
JL. MELUR NO. 143, PEKANBARU,,,, --------Pekanbaru

i want to pull only text "Pekanbaru"

any assistance, much appreciated...


john m

[AlKey]

Enter formula in B1 and copy down

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255)),255,255))

Data Range

	A	B
1	Jl. Hang Lekir VIII No. 10,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U	Pekanbaru
2	Jalan Gatot Subroto Nomor 31 Pekanbaru - 0761-23513,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U	Pekanbaru
3	JL. MELUR NO. 143, PEKANBARU,,,,	PEKANBARU

[bebo021999]

This will extract text between first colon"," and second colon",":

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),100,100))

[Jhon Mustofa]

hi...

sorry i miss about sample..

i just wanna out text only "Pekanbaru" the other text not "extract"

Jl. K.H Dewantara Gg Alhusna, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U------------Pekanbaru
Jl. Jend. Sudirman, Taluk Kuantan,Taluk Kuantan,KUANTAN TENGAH,KUANTAN SENGINGI,R I A U---------blank cause non contains text "Pekanbaru"
Jl. P Hidayat Gg SD V No. 71 RT 12 RW 04, Tembilahan,Tembilahan,TEMBILAHAN,INDRAGIRI HILIR,R I A U--blank cause non contains text "Pekanbaru"

[bebo021999]

Try:

=IF(ISNUMBER(SEARCH("Pekan Baru",A1)),"Pekan Baru","")

[Jhon Mustofa]

Try:

=IF(ISNUMBER(SEARCH("Pekan Baru",A1)),"Pekan Baru","")

have 2 conditions :
"Pekanbaru" and "Pekan Baru"

eg. Jalan Pendawa Nomor 11 Sp. Tiga Pekanbaru,Simpang Tiga,,,-------your formula not working

how fixed?

[AlKey]

Try this formula with criteria in C1:C2

=IFERROR(LOOKUP(10^308,SEARCH($C$1:$C$2,A1),$C$1:$C$2),"")

Data Range

	A	B	C
1	Jl. Hang Lekir VIII No. 10,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U	Pekan Baru	Pekanbaru
2	Jalan Gatot Subroto Nomor 31 Pekanbaru - 0761-23513,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U	Pekan Baru	Pekan Baru
3	JL. MELUR NO. 143, PEKANBARU,,,,	Pekanbaru
4	Jl. K.H Dewantara Gg Alhusna, Pekanbaru,Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U	Pekan Baru
5	Jl. Jend. Sudirman, Taluk Kuantan,Taluk Kuantan,KUANTAN TENGAH,KUANTAN SENGINGI,R I A U
6	Jl. P Hidayat Gg SD V No. 71 RT 12 RW 04, Tembilahan,Tembilahan,TEMBILAHAN,INDRAGIRI HILIR,R I A U

[bebo021999]

Try with two conditions:

=IF(OR(ISNUMBER(SEARCH("Pekan Baru",A1)),ISNUMBER(SEARCH("PekanBaru",A1))),"Pekan Baru","")

[Jhon Mustofa]

hi guys....thank all....