Extract Address After Second Mark (,) From Left

[muhammad susanto]

hi...

how extract data address after second mark "comma" from left :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data[/TD]
[TD]after formula[/TD]
[/TR]
[TR]
[TD]Jl. Dr. Sutomo No. 63, Pekanbaru,,,,[/TD]
[TD]Jl. Dr. Sutomo No. 63, Pekanbaru[/TD]
[/TR]
[TR]
[TD]Jl. Riau Gg Dwikarsa No. 5, Pekanbaru, Pekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I A U[/TD]
[TD]Jl. Riau Gg Dwikarsa No. 5, Pekanbaru[/TD]
[/TR]
[TR]
[TD]Jl Kaharudin Nasution No 163 Pekanbaru,Sail,,,[/TD]
[TD]Jl Kaharudin Nasution No 163 Pekanbaru,Sail[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

your helps, much appreciated..

m.susanto

 

[pgc01]

Hi

For ex.:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",","#",2))-1)

Assuming you don't use "#" in your addresses.

 

[muhammad susanto]

hi

for ex.:

=left(a2,find("#",substitute(a2,",","#",2))-1)

assuming you don't use "#" in your addresses.

hi..pgc, it not fully work the result not exactly with my sample.

How do??

 

[pgc01]

Hi

THis is a test:



~<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">data</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl. Dr. Sutomo No. 63, Pekanbaru,,,,</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl. Dr. Sutomo No. 63, Pekanbaru</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl. Riau Gg Dwikarsa No. 5, Pekanbaru, P<br>ekanbaru,PEKAN BARU KOTA,PEKAN BARU,R I <br>A U</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl. Riau Gg Dwikarsa No. 5, Pekanbaru</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl Kaharudin Nasution No 163 Pekanbaru,S<br>ail,,,</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Jl Kaharudin Nasution No 163 Pekanbaru,S<br>ail</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1.xlsm]Sheet4</td></tr></table>

 

[muhammad susanto]

hi..PGC..

i have test your formula the result is :

Jl. Dr. Sutomo No. 63 not include text "Pekanbaru" should be Jl. Dr. Sutomo No. 63, Pekanbaru

where missing it?

 

[pgc01]

Hi

As you see I posted a test with your values and it works fine.

I can only think that you have some other comma before.

What do you get if you write in the cell:

[TABLE="width: 572"]
<colgroup><col width="572"></colgroup><tbody>[TR]
[TD="width: 572"]First, Second, Third[/TD]
[/TR]
</tbody>[/TABLE]

 

[muhammad susanto]

this in indonesia format :

your original code :

=LEFT(e3,FIND("#",SUBSTITUTE(e3,",","#",2))-1)

then suit in indonesia language setting

=LEFT(E3;FIND(",";SUBSTITUTE(E3;";";",";2))-1)

where is wrong?

 

[pgc01]

Try:

=LEFT(E3;FIND("#";SUBSTITUTE(E3;",";"#";2))-1)

 

[muhammad susanto]

try:

=left(e3;find("#";substitute(e3;",";"#";2))-1)

yesss!!!! Great!! Thank you pgc,,

 

[pgc01]

You're welcome. Thanks for the feedback.