Hai Odong,
Formula tersebut pada dasarnya adalah proses text to column atau sering
disebut dengan split text berdasar suatu delimiter tertentu.
Pada fitur Text To Column, delimiter nya selalu 1 karakter. Tapi dengan
formula, delimiter-nya bisa berupa kalimat.
Contoh 1 :
1;23a;bunyi pertama;123 berisi 4 field (sebut aja kolom) dengan
masing-masing field dipisahkan oleh karakter delimiter (pemisah) ';' (titik
koma)
Contoh 2
1 23a bunyi pertama 123 berisi 4 field (sebut aja kolom) dengan
masing-masing field dipisahkan oleh karakter delimiter (pemisah) ' '
(spasi) kecuali pada field ke-3 yang boleh berisi karakter spasi tapi tetap
sebagai 1 field.
Contoh 3
1<kid>23a<kid>bunyi pertama<kid>123 berisi 4 field (sebut aja kolom) dengan
masing-masing field dipisahkan oleh kata delimiter (pemisah) '<kid>'
Contoh 4
1 saya mau makan 23a saya mau makan bunyi pertama saya mau makan 123 berisi
4 field (sebut aja kolom) dengan masing-masing field dipisahkan oleh
kalimat delimiter (pemisah) ' saya mau makan ' (ada spasi di sebelum dan
sesudah kalimat)
Fitur text to column bisa memproses contoh data 1, tetapi sulit untuk yang
contoh 2,3,4
Fitur formula bisa dimanfaatkan untuk proses split data.
Langkah :
1. ketahui delimiter-nya
> sudah bisa ya... dari 4 contoh di atas, sudah diketahui delimiternya.
2. bentangkan data menjadi terpisah berdasar delimiternya dengan lebar
bentangan yang tetap dan konsisten
> fungsi substitute digunakan untuk mengganti delimiter menjadi suatu
teks pembentang data tersebut.
> teks pembentang data yang mudah dikelola adalah spasi, karena spasi
bisa dipotong oleh trim
> lebar teks pembentang yang ideal adalah sebanyak jumlah karakter data
(fungsi Len bisa dimanfaatkan), tetapi pada kasus yang pasti batas maksimal
karakter datanya, bisa menggunakan lebar bentangan sebanyak jumlah karakter
maksimal tersebut.
> fungsi Rept bisa digunakan untuk membentuk teks pembentang
Contoh : data di cells a1 berbunyi 1<kid>23a<kid>bunyi pertama<kid>123
1. delimiter : <kid>
2. lebar teks pembentang adalah Len( a1 ) -> kalau dihitung manual, ada 35
karakter, berarti hasil Len( a1 ) bernilai 35.
jadi, teks pembentang adalah Rept( " " , Len( a1 ) )
atau kalau sudah fix selalu datanya maksimal berisi 35 karakter disetiap
record, maka teks pembentang bisa menjadi Rept( " " , 35 )
atau kalau sudah fix selalu datanya tidak akan lebih dari 99 karakter di
setiap record walau ada yang hanya berisi 35 karakter di record tertentu,
teks pembentangnya adalah Rept( " " , 99 )
Wujudnya : karakter spasi sebanyak itu
Proses split text dengan formula substitute menjadi : (misal diletakkan di
cells b1)
=Substitute( a1 , "<kid>" , Rept( " " , Len( a1 ) )
penampakannya :
1 (spasi sebanyak 35 karakter) 23a (spasi sebanyak 35 karakter)
bunyi pertama (spasi sebanyak 35 karakter) 123
Kalau sudah dapat splited text seperti di atas (yang misalnya diletakkan di
cells b1 tersebut), barulah bisa memulai proses pengambilan data field yang
dibutuhkan.
Contoh : di c1:F1
c1 : ambil field ke-1 bisa dengan :
=Left( b1 , Len( a1 ) ) -> kalau teks pembentang tadi
menggunakan 99, =Left( b1 , 99 )
atau
=Mid( b1 , 1 , Len( a1 ) ) -> kalau teks pembentang tadi menggunakan
99, =Mid( b1 , 1 , 99 )
hasilnya :
1(diikuti spasi sebanyak 34 karakter)
d1 : ambil field ke-2 dengan :
=Mid( b1 , 1 + Len( a1 ) , Len( a1 ) )
hasilnya :
(diawali 1 spasi)23a(diikuti spasi sebanyak 31 karakter)
e1 : ambil field ke-3 dengan :
=Mid( b1 , 1 + 2*Len( a1 ) , Len( a1 ) )
hasilnya :
(diawali 4 karakter spasi)bunyi pertama(diikuti spasi sebanyak 18 karakter)
f1 : ambil field ke-4 dengan :
=Mid( b1 , 1 + 3*Len( a1 ) , Len( a1 ) )
hasilnya :
(diawali 17 karakter spasi)123
atau : =Right( b1 , Len( a1 ) )
hasilnya :
(diawali 32 karakter spasi)123
Berarti, setiap formula pengambil nilai field perlu membuang spasi
berlebih, yaitu dengan Trim. Contoh : =Trim( Right( b1 , Len( a1 ) ) )
Bagaimana ? Sampai sini sudah bisa paham dasarnya kan ya.... :)
Regards,
Kid
On Mon, Nov 28, 2016 at 11:00 AM, odong nando odongna...@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> dear bos kid..
>
> sudah sering saya melihat gabungan formula yang bos kid berikan ini, tapi
> samapai sekarang saya belum bisa mengerti maksud dari gabungan formula ini
> sehingga mendapatkan hasil yang di inginkan...
> bila bos kid ada waktu, tolong di jabarkan maksud dari formulanya, dan
> bila memang sudah pernah di jabarkan tolong infokan link nya terimakasih. :)
>
> br,
>
> odong nando
>
>
> ------------------------------
> *Dari:* "'Mr. Kid' mr.nm...@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *Kepada:* BeExcel <belajar-excel@yahoogroups.com>
> *Dikirim:* Senin, 28 November 2016 10:42
> *Judul:* Re: [belajar-excel] Ambil Qty
>
>
> Hai DD,
>
> Bagian yang akan diambil adalah field ke-3 dari kanan dan bertipe numerik.
> Dua buah field terkanan juga bertipe numerik. Delimiter adalah spasi
> (kecuali pada field nama produk). Formula :
> =IFError( 1 * Trim( Left( Right( Substitute( datanya , " " , Rept( " " ,
> 99 ) ) , 3 * 99 ) , 99 ) ) , "" )
>
> Regards,
> Kid
>
>
>
>
>
>
> 2016-11-28 8:43 GMT+07:00 'Dede Sulaeman' dede_sulaeman@pinusmerahabadi.
> co.id [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
> Dear Mr Heri,
>
> Formulanya belum tepat setelah saya masukan ke kasus sebenarnya.
> Untuk karakter #### itu adalah nama produk yang sengaja saya ganti menjadi
> ####.
> File terlampir saya rubah karakter #### menjadi nama produk sembarang.
>
> Salam,
> DD
>
>
>
> *From:* belajar-excel@yahoogroups.com [mailto:belajar-excel@
> yahoogroups.com <belajar-excel@yahoogroups.com>]
> *Sent:* 26 November 2016 5:14
> *To:* belajar-excel@yahoogroups.com
> *Subject:* Re: [belajar-excel] Ambil Qty
>
>
>
>
> السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>
> Semangat pagi,.
>
>
>
> Pakai angka coba2 boleh kah ?!
>
>
>
> =TRIM(LEFT(TRIM(MID(A1;FIND(" ";A1;25);50));FIND(" ";TRIM(MID(A1;FIND("
> ";A1;25);50));1)))
>
>
>
>
>
> وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>
>
>
> Warm Regards,
>
> On 25/11/2016 15:26, 'Dede Sulaeman' dede_sulaeman@pinusmerahabadi. co.id
> <dede_sulae...@pinusmerahabadi.co.id> [belajar-excel] wrote:
>
>
> Dear B’Exceler,
>
> Saya ada kausus untuk mengambil qty di tengah text, sudah saya dapatkan
> angkanya hanya saja apakah ada formula yang lebih simpel dari formula yang
> sudah saya buat? Karena saya perlu banyak membutuhkan kolom bantu untuk
> mengambil angka tersebut. Mohon masukannya. Terima kasih.
>
> Salam,
> DD
>
>
>
>
>
>
>
>
>
