Jumat, 17 Agustus 2018

Cara Memisahkan Teks dan Angka di Excel

Rumus Excel [.] com - Dalam membuat data di Excel, tentu tak hanya memasukkan angka saja tapi juga ada data teks atau string, terkadang juga pada suatu sel berisi teks dan angka sekaligus. Untuk itu kali ini kita akan sama-sama belajar memisahkan teks dan angka di Excel.

Hasil akhirnya nanti kurang lebih seperti ini:

Memisahkan Teks dan Angka di Excel
Untuk memisahkan angka dan teks di Excel kita bisa memanfaatkan kombinasi fungsi-fungsi Excel seperti LEFT, RIGHT, MID, FIND dan lain-lain, namun untuk kali ini kita akan belajar membuat fungsi sendiri, yang akan di berinama AmbilTeks dan AmbilAngka. Dan berikut langkah-langkah pembuatan fungsinya:

  1. Buka Aplikasi VBA Excel dengan menekan tombol Alt + F11.
  2. Tambahkan Module baru, dengan cara klik menu Insert - Module.

  3. Masukkan script berikut ini ke dalam module yang tadi dibuat.
    Option Explicit
    
    Function AmbilAngka(Str As String) As String
    Dim X As Object
    Set X = CreateObject("VBScript.RegExp")
    With X
        .Global = True
        .Pattern = "[\D]"
    AmbilAngka = .Replace(Str, vbNullString)
    End With
    End Function
    
    
    Function AmbilTeks(Str As String) As String
    Dim X As Object
    Set X = CreateObject("VBScript.RegExp")
    With X
        .Global = True
        .Pattern = "[0-9]"
    AmbilTeks = .Replace(Str, vbNullString)
    End With
    End Function
    
Penjelasan Script:
Fungsi VBA yang digunakan adalah fungsi Regex, fungsi regex ini biasa digunakan untuk menvalidasi data tertentu contoh umum untuk validasi email dengan tanda @ sebagai kode regexnya, coba perhatikan script diatas pada bagian pattern:
.Pattern = "[\D]" dan .Pattern = "[0-9]"
Kode regex-nya adalah karakter yang ada di antara tanda kurung [ kode regex ].

[\D] untuk menvalidasi karakter non digit (bukan angka)
[0-9] atau [\d] untuk menvalidasi karakter digit atau angka 0 - 9.

Selain regex pattern juga ada regex Replace, yang fungsinya untuk mengganti karakter tertentu. Contohnya untuk fungsi AmbilTeks:
AmbilTeks = .Replace(Str, vbNullString)
dengan pattern angka [0-9], yang artinya semua angka akan di ubah menjadi vbNullString alias karakter null atau dihilangkan, jadi misal kode vbNullString diubah menjadi "*" maka semua karakter angkanya akan di ubah menjadi *.

Itulah sedikit penjelasan mengenai script diatas, mungkin lain waktu akan saya jelaskan lebih detail mengenai penggunaan fungsi regex dan kode-kodenya.

Fungsi AmbilTeks dan AmbilAngka

Untuk penulisan fungsinya adalah:
=AmbilTeks (teks) dan AmbilAngka (teks) 
teks merupakan teks atau angka, sel referensi dan tidak bisa digunakan pada rentang (range) data.

Contohnya seperti gambar berikut ini:

Memisahkan Teks dan Angka di Excel

Lampiran

Fungsi Ambil Angka dan teks Excel Binary Workbook (xlsb)

Sekian tutorial membuat fungsi sendiri untuk memisahkan teks dan angka di Excel, Semoga Bermanfaat...

6 komentar
  1. mantap om, keliling kesana kemari. akhirnya dapat yang mudah n simple. sukses terus blognya

    BalasHapus
  2. Mantap sangat membantu, sukses terus blognya

    BalasHapus
  3. Terima kasih semoga ilmunya bermanfaat bagi Kita semua.

    BalasHapus
  4. Mantap gan.. cari2 referensi ketemunya disini. Terima Kasih

    BalasHapus
  5. terima kasih.....
    Mantap jiwa......membantu banget.....
    sukses selalu blognya....

    BalasHapus
  6. MANTAP MAKASIH BANYAK BERGUNA SEKALII

    BalasHapus


EmoticonEmoticon