Wa'alaikumussalam wr wb Mas Nang, 1. Apakah benar, Untuk formula ambil angka hanya dapat dibuat 15 karakter sesuai keterbatasan excel, agar dapat menampung 40 karakter angka dapat dihitung di 3 cell dan hasilya dapat digabung menjadi text di cell lainnya? dengan asumsi setiap cell yang digunakan untuk menghitung masing-masing 15 karakter angka. > Yup. Setiap array formula pengambil angka tersebut untuk mengambil setiap 15 angka saja agar tidak ada angka yang hilang karena Excel membatasi proses komputasi dengan 15 digit. > Jadi, bagian Sum( ) yang menjadi pembatas jumlah digit per array formula tersebut adalah 15 angka saja. > Untuk data yang berisi 1000 karakter yang 150 karakter diantaranya adalah berisi angka, maka perlu 10 cells berisi array formula pengambil angka tersebut per 15 angka, lalu digabungkan menjadi data bertipe text. > Jadi, cells ke-1 ambil angka ke-1 sampai 15, cells ke-2 ambil dari angka ke-16 sampai 30, dst, lalu cells ke 11 menggabungkannya > kalau mau langsung, ya dibuat sebuah cells berisi 10 bunyi array formula yang digabungkan dengan & =arrayformula_angka_ke-1sd15 & arrayformula_angka_ke-16sd30 & dst
2. Untuk Udf apakah ada batasnya? saya mencoba untuk 700 karakter terdiri dari 306 angka dan 394 huruf masih berjalan normal. (tetapi angkanya dibuat string) > udf yang hasil akhirnya bertipe numerik, akan dibatasi oleh datatype hasil udf yang digunakan dengan maksimal 15 digit (pembatas cells Excel). > udf yang hasil akhirnya bertipe text akan dibatasi oleh datatype string dengan maksimal adalah 32k sekian karakter (pembatas cells Excel) dan harus ditulis ke dalam cells sebagai nilai bertipe text. Bila perlu tambahkan awalan karakter ' (petik satu) begitu ya... Nah untuk yang sudah punya Excel 2019, bisa juga dengan array formula Concat atau TextJoin (pilih saja). Array formula digunakan untuk menyusun item-item yang akan diproses oleh fungsi Concat ataupun oleh TextJoin. Kira-kira bunyinya begini : data di G4 (seperti file lalu saja deh) > Pakai Concat : > ambil huruf : =Concat( IF( IsNumber( - Mid( G4 , Row($1:$99) , 1 ) ) , "" , Mid( G4 , Row($1:$99) , 1 ) ) ) -> sesuaikan 99 dengan jumlah karakter maksimal data, seperti 999 atau 35 dsb > ambil angka : =Concat( IFError( -- Mid( G4 , Row($1:$99) , 1 ) , "" ) ) > Pakai TextJoin : > ganti fungsi Concat dengan *TextJoin*, lalu tambahkan input parameter "" (null string) dan set ignore empty value atau empty cells dengan *true* (boleh dengan angka* 1*) * pada kasus ini, ignore empty value atau ignore empty cells tidak ada dampaknya, karena data yang di join adalah sebuah karakter atau sebuah nullstring. > Contoh : > ambil huruf : =*TextJoin*( "" ,* 1 *, IF( IsNumber( - Mid( G4 , Row($1:$99) , 1 ) ) , "" , Mid( G4 , Row($1:$99) , 1 ) ) ) > ambil angka : =*TextJoin*( "" ,* 1 *, IFError( -- Mid( G4 , Row($1:$99) , 1 ) , "" ) ) Sila diatur saja dan dikembangkan sesuai kebutuhan. Wassalamu'alaikum wr wb Kid On Sun, Dec 2, 2018 at 6:22 AM Agus [email protected] [belajar-excel] < [email protected]> wrote: > > > Dear Mas Kid. > Assalamualaikum warahmatullah, > > 1. Apakah benar, Untuk formula ambil angka hanya dapat dibuat 15 karakter > sesuai keterbatasan excel, agar dapat menampung 40 karakter angka dapat > dihitung di 3 cell dan hasilya dapat digabung menjadi text di cell lainnya? > dengan asumsi setiap cell yang digunakan untuk menghitung masing-masing 15 > karakter angka. > 2. Untuk Udf apakah ada batasnya? saya mencoba untuk 700 karakter terdiri > dari 306 angka dan 394 huruf masih berjalan normal. (tetapi angkanya dibuat > string) > 3. Untuk yang membutuhkan, terlampir set by step formula mas Kid tanpa > array. > wasalamualaikum warahmatullah > > KenangAgus > > On 11/30/2018 16:10, 'Mr. Kid' [email protected] [belajar-excel] wrote: > > > Excel Flash Fill bisa membantu, tapi kenalkan lebih dulu si Excel dengan > isian manual dari berbagai pola, seperti dua records yang diawali huruf dan > 2 records lainnya yang diawali angka. Isi di kolom hasil dengan entry > manual dan coba lakukan flash fill (ada di ribbon Data). Jika gagal, coba > tambah records contoh dan lakukan lagi flash fill. > > PowerQuery juga bisa, dan simpel. Sumber data juga bisa dari file lain. > Hasilnya juga bisa dikirim langsung ke Excel Data Model untuk digunakan > oleh PowerPivot (bila perlu), tanpa perlu mengeluarkannya ke dalam > worksheet (langsung, dari sumber data -> powerquery yang extract transform > load -> excel data model -> powerpivot -> Excel Pivot Table) > Juga bisa mengolah langsung berpuluh juta records, bahkan mungkin ratusan > juta records (tergantung RAM setempat) dengan cepat. > Security data connection perlu di-enable. > Contohnya terlampir. > > Kalau ingin dengan formula (supaya gak mikirin contoh hasil dari beberapa > records atau tidak perlu ribet refresh), kira-kira begini (untuk yang ambil > angka ya). > Asumsi : > 1. angka dalam text tidak lebih dari 15 bijik > 2. jumlah karakter sumber data maksimal 99 karakter > * jika diluar asumsi, sila mengembangkan formula berikut > > Array formula pengambil angka (data di G4) : > > =SUM(IFERROR(MOD(LARGE(IF(ISNUMBER(-MID(G4,ROW($1:$99),1)),ROW($1:$99)*10+MID(G4,ROW($1:$99),1)),ROW($1:$99)),10)*10^(ROW($1:$16)-1),0)) > > Kalau pakai VBA, buatlah udf untuk replace angka (loop berisi replace > angka) dan udf untuk gabungkan setiap huruf (loop setiap karakter, untuk > gabungkan huruf dan abaikan angka). Tapi pastikan security macro-nya > di-enable > > > > > > On Fri, Nov 30, 2018 at 3:31 PM 'Dede Sulaeman' > [email protected] [belajar-excel] < > [email protected]> wrote: > >> >> >> Dear BeExceller, >> >> >> >> Furmulanya yang simple gimana yah? >> >> >> >> Salam, >> >> DD >> > > >
