hehehe...
tampaknya mas Bagus lagi sempet online cukup lama nih...
Urun formula ah... Walaupun formulanya lebih panjang (nyaris 300 karakter)
daripada punya mas Bagus...
Untuk data di A3 : (misal dipasang di B3)
=REPLACE(REPLACE(TRIM(LEFT(RIGHT(SUBSTITUTE(A3,"-",REPT("
",99)),3*99),99)),MATCH(2,INDEX((1+MID(TRIM(LEFT(RIGHT(SUBSTITUTE(A3,"-",REPT("
",99)),3*99),99)),ROW($1:$15),1))^0,0))+1,0,"-"),MATCH(1,INDEX(MID(TRIM(LEFT(RIGHT(SUBSTITUTE(A3,"-",REPT("
",99)),3*99),99)),ROW($1:$15),1)^0,0),0),0,"-")
gak asik ya kalau panjang begitu...
sebaiknya formula yang nyaris 300 karakter tersebut pakai kolom bantu saja.
Formula kolom bantu : (di pasang di D3)
=TRIM(LEFT(RIGHT(SUBSTITUTE(A3,"-",REPT(" ",99)),3*99),99))
Lalu formula hasil akhir dipasang di E3 :
=REPLACE(REPLACE(D3,MATCH(2,INDEX((1+MID(D3,ROW($1:$15),1))^0,0))+1,0,"-"),MATCH(1,INDEX(MID(D3,ROW($1:$15),1)^0,0),0),0,"-")
;)
File terlampir
Wassalam,
Kid
2015-06-24 17:09 GMT+10:00 Bagus [email protected] [belajar-excel] <
[email protected]>:
>
>
> Hai Salim;
>
> Coba begini:
>
> =IF(IFERROR(FIND("-S",A3),FIND("-T",A3))<15,MID(A3,6,IFERROR(FIND("-S",A3),FIND("-T",A3))-6),IF(IFERROR(FIND("-S",A3),FIND("-T",A3))<20,MID(A3,11,IFERROR(FIND("-S",A3),FIND("-T",A3))-11),MID(A3,16,IFERROR(FIND("-S",A3),FIND("-T",A3))-16)))
>
> Copas kebawah.
>
> Pada tanggal 24/06/15, Dede [email protected]
> [belajar-excel] <[email protected]> menulis:
> > Dear Mr Salim,
> >
> >
> >
> > Masukan dari saya sambil menunggu kiriman dari master yang lain.
> >
> >
> >
> > 1. Pecah data dengan “Text to Column” berdasarkan “-“ (karena saya
> > perhatikan NO_POLISI posisinya selalu berada ketiga dari kanan)
> >
> > 2. Masukan rumus di kolom Filter (Seperti terlampir)
> >
> > 3. Masukan rumus di kolom No Polisi (Seperti terlampir)
> >
> >
> >
> > Salam,
> >
> > DD
> >
> >
> >
> > From: Salim Jenggot [email protected] [belajar-excel]
> > [mailto:[email protected]]
> > Sent: 24 Juni 2015 09:03
> > To: [email protected]
> > Subject: [belajar-excel] Memisahkan/mengextract nomor kendaraan dari
> tengah
> > kalimat [1 Attachment]
> >
> >
> >
> >
> >
> > Dear Master Excel,
> >
> > Mohon dibantu, saya ada kebutuhan data memisahkan/mengextract nomor
> polisi
> > dari tengah kalimat seperti file terlampir.
> >
> > kesulitannya adalah tidak adanya keseragaman/kesamaan letak nomor polisi
> > didalam kalimat tersebut.
> >
> > dari data tersebut apakah bisa dibantu dibuatkan formulanya agar nomor
> > polisi dapat saya pisahkan ke kolom/baris lainnya?
> >
> > atas bantuannya diucapkan terima kasih.
> >
> > Salam,
> >
> > Salim jenggot
> >
> >
> >
> >
> >
> > _____
> >
> > This e-mail and any files transmitted with it are confidential and
> intended
> > solely for the use of the individual to whom it is addressed. If you have
> > received this email in error please send it back to the person that sent
> it
> > to you. Any views or opinions presented are solely those of its author
> and
> > do not necessarily represent those of NABATI or any of its subsidiary
> > companies. Unauthorized publication, use, dissemination, forwarding,
> > printing or copying of this email and its associated attachments is
> strictly
> > prohibited.
> >
> >
>
> --
> Wassalam
> Bagus
>
>
re-data extract nomor kendaraan.xlsx
Description: MS-Excel 2007 spreadsheet
