Mengekstrak Angka dari Text Data Entri

Meng-extract porsi angka dari sebuah entri data dapat dilakukan dengan berbagai cara. Jika sejumlah entri data mempunya pola urutan huruf dan angka yang tetap serta panjang text-nya konstan, maka kita bisa mengambil porsi angka dengan mudah menggunakan fungsi pengolah text seperti LEFT, RIGHT dan MID dikombinasikan dengan fungsi CONCATENATE. Namun jika pola urutan kombinasi angka dan huruf tidak tetap, maka fungsi standar pengolah text di excel tidak bisa menyelesaikan kasus tersebut. Untuk itu diperlukan sebuah UDF (User Defined Function) untuk menyelesaikan tugas ini.

Catatan pelajaran excel kali ini akan membahas bagaimana membuat dan menerapkan fungsi ambilAngka(), dimana fungsi ini berguna untuk men-ektrak porsi angka dari sebuah entri text.







Screenshot berikut memperlihatkan bagaimana fungsi ambilAngka() bisa mengextract porsi angka dari entri data, tidak peduli bagaimana pola susunan karakter serta panjang data entri.

Selanjutnya mari kita simak baik-baik bagaimana menerapkan code VBA untuk membuat fungsi ambilAngka sehingga bisa diterapkan pada spreadsheet seperti gambar di atas.

Contoh kode VBA untuk extract angka dari text.

Berikut contoh kode VBA yang dapat digunakan untuk extrak porsi angka dari data entri.

Function ambilAngka(txt As String) As String
Dim i As Integer, iKarakter As String, Angka As String
For i = 1 To Len(txt)
  iKarakter = Mid(txt, i, 1)
  If IsNumeric(iKarakter) Then
    Angka = Angka & iKarakter
  End If
Next
ambilAngka = Angka
End Function


Supaya code diatas dapat digunakan maka harus diketikan atau dicopy ke modul VBA. Jika pembaca sudah mengenal dasar – dasar VBA sebelumnya, tentunya bukan hal yang sulit bagi anda untuk segera mengcopy kan code di atas ke modul VBA.

Bagi pembaca yang masih baru mengenal VBA tidak perlu khawatir. VBA itu sangat menyenangkan, apalagi jika kita bisa merasakan manfaatnya yang luar biasa dalam meningkatkan efisiensi dan efektifitas kerja menggunakan microsoft Excel.

Baiklah mari kita lanjutkan. Bagaimana masuk ke modul VBA.

• Untuk excel 2007 atau yang lebih baru, pastikan tab developer tersedia dan setting macro security enable. Demikian juga jika anda masih menggunakan excel 2003, pastikan macro security enable.
• Untuk masuk ke module VBA, tekan shortcut ALT = F11 atau melalui ribbon dengan cara klik icon Visual Basic pada tab developer.

• Pada jendela VBA, klik menu Insert → klik Module

• Langkah selanjutnya ketikan atau copy kode VBA di atas pada module seperti diperlihatkan dalam screenshot di bawah ini.

• Setelah code diketik/ di copy ke modul VBA, maka fungsi ambilAngka() sudah tersedia dan siap digunakan.
• Simpan file dengan extension .xlsm (Excel Macro - Enable Workbook) atau dengan extension .xlsb (Excel Binary Workbook) jika anda menggunakan excel 2007, 2010 atau versi yang lebih baru.

Cara menggunakan fungsi ambilAngka()

Gambaran cara menggunakan fungsi ambilAngka() sudah diperlihatkan pada bagian awal catatan ini., silahkan di scroll kembali ke bagian atas untuk melihat screenshot contoh penerapannya di excel.

Cara penulisan rumusnya sangat sederhana. Yaitu:

=ambilangka(entri)

Misalnya kita menuliskan rumus sebagai berikut:

=ambilangka("AB12cfgR44Db")

Maka ouput dari rumus di atas adalah : "1244" yang merupakan porsi angka dari "AB12cfgR44Db"
Karena data entri terletak dalam sel excel, maka rumus ambilAngka dapat dituliskan dengan menggunakan referensi sel:

Misalnya:

=ambilAngka(A1)

Rumus ini berguna untuk mengambil porsi angka dari text data entri yang terletak pada sel A1.

Demikian pembahasan singkat mengenai contoh kode macro / vba yang dapat digunakan untuk mengekstrak porsi angka dari entri text. Semoga bermanfaat.

Salam.

Artikel terkait:

• Konversi Angka Berformat Text Menjadi Angka Nyata
• Membuat Daftar Isi Otomatis
• Memasukan Data ke Beberapa Worksheet Sekaligus

Menghitung Jumlah Sel Dengan Kriteria Warna

Bagaimana cara menghitung jumlah sel dengan kriteria warna tertentu? Pertanyaan ini kerap muncul di forum-forum diskusi mengenai excel.

Nampaknya sampai dengan saat ini, solusi terbaik untuk menghitung banyaknya sel yang berwarna tertentu adalah dengan menggunakan VBA.


Nah, dalam kesempatan ini je-xcel akan memberikan tips singkat bagaimana membuat UDF (User Defined Function) untuk menghitung banyaknya sel dalam sebuah range yang memiliki warna tertentu.

Code VBA untuk menghitung banyak sel dengan kriteria warna tergolong sangat singkat. Perhatikan contoh berikut:

Function hitungWarna(selAcuan As Range, rangeWarna As Range)
Dim sel As Range
For Each sel In rangeWarna
    If sel.Interior.Color = selAcuan.Interior.Color Then
        hitungWarna = hitungWarna + 1
    End If
Next
End Function

UDF hitungWarna dapat digunakan dalam lembar kerja excel, setelah code diatas diketik atau dicopy pada module standar dalam jendela VBA, caranya sebagai berikut

• Masuk dulu ke dalam jendela VBA dengan cara tekan shorcut ALT+F11

• Dari jendela VBA, klik menu Insert , dan klik module

• Maka kemudian akan muncul module standar yang baru. Copy atau ketik code di atas ke dalam module:

• Save as file excel dengan type  file Excel macro enable workbook atau Excel binary workbook
• Sampai pada tahap ini, fungsi hitungWarna sudah dapat digunakan dalam lembar kerja excel.

Penjelasan fungsi:

Fungsi hitungWarna dapat digunakan untuk menghitung banyaknya sel didalam rangeWarna yang memiliki warna sama dengan selAcuan.

Contohnya:

selAcuan   = D1

rangeWarna = A1:B5

Maka untuk menghitung banyaknya sel di range A1:B5 yang memiliki warna yang sama dengan sel D1 dapat dituliskan rumusnya sebagai berikut:

=hitungwarna(D1,A1:B5)

Sesuai contoh gambar berikut:

Dari gambar tersebut, bisa disimpulkan bahwa fungsi hitungWarna berhasil menghitung banyaknya warna yang sama dengan sel D1 (kuning) pada range A1:B5.  (dalam contoh ada 3 warna)


Demikian tips singkat bagaimana menggunakan code VBA untuk menghitung banyaknya sel dengan kriteria warna.

Semoga bermanfaat.


Baca juga artikel belajar excel lainnya:

• Cara Cepat Membuat Hyperlink dengan VBA
• Macro untuk Setting Comment
• Macro untuk Melihat dan Menyembunyikan Comment dengan Cepat