Konsep :
ubah delimiter terakhir menjadi '|', lalu cari posisi karakter '|', dan ambil semua karakter sebelum posisi karakter '|' tersebut
Syntax Formula :
PN =IFError( Left( teks , Find( pengganti_delimiter , Substitute( teks , delimiter , pengganti_delimiter , Len( teks ) - Len( Substitute( teks , delimiter , "" ) ) ) ) -1 ) , teks )
Brand =Mid( teks , Len( hasil_PN ) + 1 + jumlah_karakter_delimiter , jumlah_karakter_terbanyak_field_terakhir )
Bagian-bagian formula :
teks : data bertipe string
delimiter : characters pemisah antar field
pengganti_delimiter : characters pengganti delimiter terakhir
hasil_PN : cells hasil formula PN
jumlah_karakter_delimiter : jumlah karakter delimiter yang digunakan, pada contoh formula PN di atas, teks menggunakan delimiter '-', maka nilai ini adalah 1 karakter
jumlah_karakter_terbanyak_field_terakhir : jumlah karakter terbanyak yang mungkin dicapai oleh field terakhir
Catatan :
1. IFError digunakan untuk kondisi teks tidak berisi delimiter yang bisa diubah menjadi '|', yang berarti harus mengambil teks apa adanya.
2. Len()-Len(Substitute) : banyaknya delimiter di dalam teks
*** Jadi, Len()-Len(Substitute)+1 adalah banyaknya fields (atau kolom) yang terbentuk jika dipisah dengan delimiter tersebut.
3. Formula ini lebih aman dan karakter '|' bisa disesuaikan dengan kondisi setempat, walaupun cukup panjang.
Contoh :
1. data di A1 bertipe text (string), dengan delimiter karakter '-' (1 karakter delimiter), field terakhir maksimal berisi 99 karakter
data tidak pernah berisi karakter '|', sehingga karakter '|' bisa dijadikan pengganti_delimiter
PN di B1 =IFError( Left( A1 , Find( "|" , Substitute( A1 , "-" , "|" , Len( A1 ) - Len( Substitute( A1 , "-" , "" ) ) ) ) -1 ) , A1 )
Brand di C1 =Mid( A1 , Len( B1 ) + 1 + 1 , 99 ) atau =Mid( A1 , Len( B1 ) + 2 , 99 )
2. data di A1 bertipe text (string), dengan delimiter karakter '-' (1 karakter delimiter), field terakhir maksimal berisi 999 karakter
data berisi berbagai macam karakter termasuk koma, pipe, enter, tab, dan sebagainya,
sehingga pengganti_delimiter disusun sendiri dengan suatu tulisan yang pasti tidak pernah digunakan oleh data, misal berbunyi '<kid ganteng>'
*** penampakan salah satu data : ABC-XYZ|123-TES
PN di B1 =IFError( Left( A1 , Find( "<kid ganteng>" , Substitute( A1 , "-" , "<kid ganteng>" , Len( A1 ) - Len( Substitute( A1 , "-" , "" ) ) ) ) -1 ) , A1 )
Brand di C1 =Mid( A1 , Len( B1 ) + 1 + 1 , 99 ) atau =Mid( A1 , Len( B1 ) + 2 , 999 )
>> Nice to know <<
Formula yang lebih pendek : (formula brand dapat menggunakan formula yang telah dijelaskan di atas)
*** Array Formula, pastikan di-entry dengan CTRL SHIFT ENTER ***
PN =IFError( Left( teks , Match( 2 , 1/(Mid( teks , Row($1:$999) , 1 ) =delimiter) ) -1 ) , teks )
Catatan :
1. Array formula untuk teks berisi maksimal 999 karakter -> lihat angka 999 di bagian Row()
2. Semakin banyak jumlah karakter, maka semakin lambat proses kalkulasinya
*** Jika jumlah karakter maksimal pada setiap record dari seluruh data bisa diketahui, maka gunakanlah angka tersebut.
*** misal diketahui jumlah karakter dalam setiap record maksimal adalah 71 karakter, maka ubah Row($1:$999) menjadi Row($1:$71)
3. Hati-hati dengan ulah user dalam men-Delete atau meng-Insert Entire Row di area yang dirujuk oleh bagian Row(), karena akan mengubah formula
Konsep :
mencari posisi terakhir delimiter dalam teks, lalu mengambil semua karakter sebelum posisi terakhir delimiter tersebut
Pengembangan Formula :
Array formula di atas bisa diubah agar tidak lagi menjadi array formula, yaitu dengan salah satu dari cara berikut :
A. memanfaatkan fungsi Index
PN =IFError( Left( teks , Match( 2 , Index( 1/(Mid( teks , Row($1:$999) , 1 ) =delimiter) , 0 ) ) -1 ) , teks )
B. memanfaatkan fungsi LookUp
PN =IFError( Left( teks , Lookup( 2 , 1/(Mid( teks , Row($1:$999) , 1 ) =delimiter) , Row($1:$999) ) -1 ) , teks )
Regards,
Kid
On Sat, Jan 11, 2020 at 8:05 PM halak hita halakhita1983@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
Terima kasih mr.kidNamun bagaimana FORMULA nya jika text di PN tidak standar atau tidak selalu 15 sebelum tanda "-".Sebab PN ini bervariasi jumlah karakternya..MisalnyaSC12345-R1-MAULRT1234-R1-MODV0123456789-WEWV123456-MAULZA636721-1-MODEZA7200891600-R2-DELR123456778901-R10-SAZHA0003195008-RENZA6443800702-ZR78008002400-ERT8000A2200-01-DTBagaimana Formulanya agar PN bisa diambil sebelum tanda "-" terakhirHasil yg diinginkanSC12345-R178008002400800A2200-01Pada tanggal Kam, 9 Jan 2020 18:33, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:PN= Left( teks , 15 )Brand= Mid( teks , 17 , 99 )On Thu, Jan 9, 2020 at 12:01 AM Mr. Kid <mr.nmkid@gmail.com> wrote:---------- Forwarded message ---------
From: Yahoo Groups Notification <belajar-excel-accept-wybdwi1lka1fbycp34m0j02jlkrq@yahoogroups.com>
Date: Wed, Jan 8, 2020 at 9:32 AM
Subject: MODERATE -- halakhita1983@gmail.com posted to belajar-excel
To: <belajar-excel-owner@yahoogroups.com>
Hello,
A message has been sent to the belajar-excel group from
halakhita1983@gmail.com
A complete copy of this message has been attached for your convenience.
---------- Forwarded message ----------
From: halak hita <halakhita1983@gmail.com>
To: belajar-excel@yahoogroups.com
Cc:
Bcc:
Date: Wed, 8 Jan 2020 09:31:35 +0700
Subject: Formula pemisahan codeDear master excelTolong dibantu apa formula ambil code id seperti terlampir.Terima kasihHalkit
__._,_.___
Posted by: "Mr. Kid" <mr.nmkid@gmail.com>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar