Extract the last text and mark (/)

[muhammad susanto]

hi all.....

how to extract this below text (delete last string) :

like sample :

[TABLE="width: 573"]
<tbody>[TR]
[TD]SAMPLE[/TD]
[TD]AFTER FORMULA[/TD]
[/TR]
[TR]
[TD]JL.HANG LEKIU NO 29 SUKAMULIA SAIL[/TD]
[TD]JL.HANG LEKIU NO 29 SUKAMULIA[/TD]
[/TR]
[TR]
[TD]JL.SAOMATI RT 03/07, SAIL/TENAYAN RAYA[/TD]
[TD]JL.SAOMATI RT 03/07, SAIL[/TD]
[/TR]
[TR]
[TD]JL.BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR[/TD]
[TD]JL.BELANAK IV/452 LIMBUNGAN[/TD]
[/TR]
[TR]
[TD]JL.KIJANG NO 24 HARJOSARI/SUKAJADI[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]JL.HANG TUAH KP. KELAPA NO 38 SAIL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JL.KEMUNING GG MESJID NO 30A SENAPELAN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JL.PINUS GG.SUNGKAI NO 1 TANGKERANG UTARA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

any assistance, much appreciated..

m.susanto

 

[oldbrewer]

you need to define rules ie the first example SAIL is deleted
so it could be delete last word
but example 2 the last 2 words and a preceding "/" are deleted and SAIL remains ????

we need RULES

 

[bosco_yip]

Maybe......

=LEFT(A2,LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99))-1)&TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"/"," "),LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99)),250)," ",REPT(" ",50)),99))

 

[muhammad susanto]

Maybe......

=LEFT(A2,LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99))-1)&TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A2,"/"," "),LOOKUP(1,-MID(A2,ROW($1:$99),1),ROW($1:$99)),250)," ",REPT(" ",50)),99))

hi bosco, thank works great!!!

i just delete "/(last text)" like my clue in red font, i think it's clear...

 

[muhammad susanto]

i have new problem :
JL.IKAN RAYA MUARA FAJAR RUMBAI --------JL.IKAN RAYA MUARA FAJAR RUMBAI (not change-should be)
JL.HANGTUAH GG.SUKMA-------------------JL.HANGTUAH GG.SUKMA (not change-should be)
JL.SAMAN HUDI SAGO/SENAPELAN ---------JL.SAMAN HUDI SAGO

note : /SENAPELAN -- should be delete

there are more shorter formula?

 

[bosco_yip]

i have new problem :
JL.IKAN RAYA MUARA FAJAR RUMBAI --------JL.IKAN RAYA MUARA FAJAR RUMBAI (not change-should be)
JL.HANGTUAH GG.SUKMA-------------------JL.HANGTUAH GG.SUKMA (not change-should be)
JL.SAMAN HUDI SAGO/SENAPELAN ---------JL.SAMAN HUDI SAGO

note : /SENAPELAN -- should be delete

there are more shorter formula?

Try,

=LEFT(A1,FIND("/",A1&"/")-1)

 

[muhammad susanto]

Try,

=LEFT(A1,FIND("/",A1&"/")-1)

hi bosco, not fully work...

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH="align: left"]JL.HANG LEKIU NO 29 SUKAMULIA SAIL[/TH]
[TH]JL.HANG LEKIU NO 29 SUKAMULIA SAIL[/TH]
[/TR]
[TR]
[TD]JL.HANGTUAH GG.SUKMA[/TD]
[TD]JL.HANGTUAH GG.SUKMA[/TD]
[/TR]
[TR]
[TD]JL.IKAN RAYA MUARA FAJAR RUMBAI[/TD]
[TD]JL.IKAN RAYA MUARA FAJAR RUMBAI[/TD]
[/TR]
[TR]
[TD]JL.SAOMATI RT 03/07, SAIL/TENAYAN RAYA[/TD]
[TD]JL.SAOMATI RT 03/07, SAIL[/TD]
[/TR]
[TR]
[TD]JL.ABDUL MUIS NO.32 CINTARAJA/SAIL[/TD]
[TD]JL.ABDUL MUIS NO.32 CINTARAJA[/TD]
[/TR]
[TR]
[TD]JL.KUANTAN II GG.PALUH NO 3C SEKIP/LIMA PULUH[/TD]
[TD]JL.KUANTAN II GG.PALUH NO 3C SEKIP[/TD]
[/TR]
</tbody>[/TABLE]

for text Bold, the data is should be not deleted after use formula otherwise "/(text)" is deleted

 

[bosco_yip]

hi bosco, not fully work...

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH="align: left"]JL.HANG LEKIU NO 29 SUKAMULIA SAIL[/TH]
[TH]JL.HANG LEKIU NO 29 SUKAMULIA SAIL[/TH]
[/TR]
[TR]
[TD]JL.HANGTUAH GG.SUKMA[/TD]
[TD]JL.HANGTUAH GG.SUKMA[/TD]
[/TR]
[TR]
[TD]JL.IKAN RAYA MUARA FAJAR RUMBAI[/TD]
[TD]JL.IKAN RAYA MUARA FAJAR RUMBAI[/TD]
[/TR]
[TR]
[TD]JL.SAOMATI RT 03/07, SAIL/TENAYAN RAYA[/TD]
[TD]JL.SAOMATI RT 03/07, SAIL[/TD]
[/TR]
[TR]
[TD]JL.ABDUL MUIS NO.32 CINTARAJA/SAIL[/TD]
[TD]JL.ABDUL MUIS NO.32 CINTARAJA[/TD]
[/TR]
[TR]
[TD]JL.KUANTAN II GG.PALUH NO 3C SEKIP/LIMA PULUH[/TD]
[TD]JL.KUANTAN II GG.PALUH NO 3C SEKIP[/TD]
[/TR]
</tbody>[/TABLE]

for text Bold, the data is should be not deleted after use formula otherwise "/(text)" is deleted

Try,

=IFERROR(LEFT(A1,LOOKUP(99,FIND("/",A1,ROW($1:$99)))-1),A1)

 

[muhammad susanto]

Try,

=IFERROR(LEFT(A1,LOOKUP(99,FIND("/",A1,ROW($1:$99)))-1),A1)

hi bosco, thank works great!!! WONDERFUL!!!!!

 

[István Hirsch]

hi bosco, thank works great!!! WONDERFUL!!!!!

So, for „JL.BELANAK IV/452 LIMBUNGAN RUMBAI PESISIR” you want „JL.BELANAK IV”?