Rumus Mengambil atau Memisahkan Angka di Excel

 

Untuk mengambil sebagian teks, Microsoft Excel sudah menyediakan fungsi LEFT, MID dan RIGHT. Namun, jika yang ingin kita ambil adalah angka saja bagaimana rumusnya?

Tutorial excel kali ini akan membahas rumus excel untuk mengambil angka atau cara memisahkan angka di excel. Pada kasus mengambil angka ini, ada tiga kemungkinan posisi angka pada teks yang akan di ambil:

1. 1. Angka berada di sebelah kanan teks
2. 2. Angka berada di sebelah kiri teks
3. 3. Angka berada di posisi tengah teks

Untuk kasus pertama, dimana posisi angka yang diambil berada di sebelah kanan, sebenarnya sudah pernah kita bahas pada halaman tutorial yang menjelaskan tentang Rumus Memisahkan teks dan Angka di Excel.

Oleh sebab itu penting sebelum melanjutkan tutorial ini anda mempelajari terlebih dahulu cara memisahkan teks dan angka yang sudah pernah dibahas pada halaman tersebut.

Kalau sudah, mari kita lanjutkan pembahasan mengenai rumus excel memisahkan angka ini.

Rumus Mengambil Angka di Kanan Teks

Untuk memisahkan angka yang berada disebelah kanan atau akhir teks, ada 2 rumus excel yang bisa Anda gunakan.

Rumus 1: Mengambil Angka di Excel

Salah satu rumus excel yang bisa digunakan untuk mengambil angka pada teks excel yang berada di posisi sebelah kanan adalah sebagai berikut:

RIGHT(RefTeks;LEN(RefTeks)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};RefTeks&"0123456789"))+1)

Rumus ini seperti yang sudah pernah kita bahas pada bagian lain blog tutorial excel ini.

RefTeks pada rumus tersebut adalah referensi cell atau teks yang akan diambil sebagian angkanya. Silahkan perhatikan contoh berikut:

Seperti yang sudah umum diketahui bahwa untuk mengambil sebagian teks atau angka dari sebelah kanan, bisa dilakukan dengan memanfaatkan fungsi RIGHT Excel.

RIGHT(RefTeks;JumlahKarakter)

Hanya saja saat menggunakan fungsi Right, kita juga perlu tahu berapa jumlah atau banyak karakter yang akan diambil.

Banyak karakter ini bisa didapatkan dengan mengurangi jumlah keseluruhan teks dengan posisi pertama angka pada teks tersebut.

Untuk mengetahui jumlah atau banyak karakter sebuah cell bisa kita dapatkan dengan fungsi LEN:

LEN(RefTeks)

Sedangkan untuk posisi pertama sebuah angka dalam teks bisa didapatkan dengan rumus excel berikut:

MIN(FIND({0;1;2;3;4;5;6;7;8;9};RefTeks&"0123456789"))

Pada rumus tersebut jumlah karakter perlu ditambah 1 supaya angka pertama ikut terambil.

Hasil akhirnya seperti terlihat pada contoh diatas rumus pada cell F3, untuk mengambil angka pada cell C3 adalah sebagai berikut:

=RIGHT(C3;LEN(C3)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};C3&"0123456789"))+1)

Rumus 2: Memisahkan Angka Di Excel

Selain menggunakan rumus di atas, Anda juga dapat menggunakan rumus excel berikut untuk memisahkan angka yang ada di sebelah kanan teks.

RIGHT(RefTeks;SUM(LEN(RefTeks)-LEN(SUBSTITUTE(RefTeks;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

Perhatikan contoh berikut:

Pada contoh di atas rumus excel yang digunakan pada cell D3 adalah:

=RIGHT(C3;SUM(LEN(C3)-LEN(SUBSTITUTE(C3;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

Kog bisa?

Silahkan gunakan tombol Evaluate Formula untuk memahami cara kerja rumus pada cell tersebut. Apabila masih belum jelas juga, harap bersabar dan kita lanjutkan ke pembahasan berikutnya dulu.

Rumus Mengambil Angka di kiri Teks

Bagaimana cara mengambil angkanya saja, jika posisi angka ada di sebelah kiri teks?

Jika posisi angka yang diambil ada di awal teks, tentunya kita tidak bisa menggunakan posisi pertama angka sebagai acuan. Yang bisa digunakan acuan adalah posisi abjad pertama pada teks tersebut. Namun dalam kasus ini kita tidak akan menggunakan cara tersebut.

Untuk mengambil angka saja yang ada di sebelah kanan teks, kita gunakan cara kedua saja. Namun, dengan sedikit penyesuaian sehingga rumus excelnya menjadi seperti berikut ini:

LEFT(RefTeks;SUM(LEN(RefTeks)-LEN(SUBSTITUTE(RefTeks;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

Pada rumus di atas, sama seperti sebelumnya, sesuaikan RefTeks adalah referensi cell atau teks yang akan diambil angkanya saja.

Perhatikan contoh berikut:

Rumus excel yang digunakan pada cell D3 adalah sebagai berikut:

=LEFT(C3;SUM(LEN(C3)-LEN(SUBSTITUTE(C3;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

Karena kita akan mengambil sebagian teks dari sebelah kiri maka fungsi excel yang digunakan adalah fungsi LEFT.

Adapun rumus SUM(....) akan menghasilkan jumlah angka yang akan di ambil dari awal kata.

Pada rumus diatas fungsi LEN dan fungsi SUBSTITUTE digunakan untuk mengetahui berapa kali masing-masing digit angka 0-9 muncul dalam teks dengan cara mengubah atau mengganti setiap angka dengan teks kosong("").

Kemudian jumlah karakter setiap teks yang telah diubah tadi digunakan untuk mengurangi jumlah karakter asli tanpa pengubahan.

Operasi perhitungan ini terjadi pada setiap angka dalam konstanta array. Hasilnya kemudian dijumlahkan oleh fungsi SUM excel.

Untuk lebih jelasnya berikut urutannya kerjanya:

Step 1:

=LEFT(C3;SUM(LEN(C3)-LEN(SUBSTITUTE(C3;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

Step 2:

=LEFT("1440-Tahun";SUM(LEN("1440-Tahun")-LEN({"144-Tahun";"440-Tahun";"1440-Tahun";"1440-Tahun";"10-Tahun";"1440-Tahun";"1440-Tahun";"1440-Tahun";"1440-Tahun";"1440-Tahun"})))

Step 3:

=LEFT("1440-Tahun";SUM(10-{9;9;10;10;8;10;10;10;10;10}))

Step 4:

=LEFT("1440-Tahun";SUM({1;1;0;0;2;0;0;0;0;0}))

Step 5:

=LEFT("1440-Tahun";4)

Dan akhirnya didapatkanlah teks angka 1440. Masih bingung juga? Silahkan dibaca ulang lagi. Hehehehe...

Untuk mengingatkan bahwa fungsi LEFT, MID atau RIGHT merupakan bagian dari fungsi-fungsi text pada excel sehingga nilai angka yang dihasilkan juga berupa data Teks meskipun terlihat seperti angka pada umumnya.

Rumus Mengambil Angka di Tengah Teks

Mengambil angka di awal teks sudah. Di akhir teks sudah, sekarang bagaimana rumus excel atau cara mengambil angka di tengah excel?

Jika posisi angka di tengah atau tidak beraturan(terpisah-pisah oleh teks lain), untuk megambil angkanya saja bisa dilakukan dengan rumus excel berikut:

=SUMPRODUCT(MID(0&RefTeks;LARGE(INDEX(ISNUMBER(--MID(RefTeks;
ROW(INDIRECT("$1:$"&LEN(RefTeks)));1))*ROW(INDIRECT("$1:$"&LEN(RefTeks)));0);
ROW(INDIRECT("$1:$"&LEN(RefTeks))))+1;1)*10^ROW(INDIRECT("$1:$"&LEN(RefTeks)))/10)

Berikut contohnya:

Jika Anda perhatikan, dengan rumus di atas, apabila RefTeks tidak mengandung angka sama sekali, ternyata rumus diatas menghasilkan angka nol(0)--Lihat contoh 11.

Supaya tidak menghasilkan angka nol(0), Anda bisa menambahkan fungsi IF Excel pada rumus tersebut kemudian cek apakah pada teks ada angkanya atau tidak. Jika tidak mengandung angka, isi dengan teks kosong.

Logika yang kita gunakan adalah dengan menghitung jumlah karakter angka pada teks tersebut dengan rumus sebelumnya:

SUM(LEN(RefTeks)-LEN(SUBSTITUTE(RefTeks;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};"")))

Sehingga rumus excelnya menjadi seperti ini:

=IF(SUM(LEN(C3)-LEN(SUBSTITUTE(C3;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};"")))>0;
SUMPRODUCT(MID(0&C3;
LARGE(INDEX(ISNUMBER(--MID(C3;
ROW(INDIRECT("$1:$"&LEN(C3)));1))*
ROW(INDIRECT("$1:$"&LEN(C3)));0);
ROW(INDIRECT("$1:$"&LEN(C3))))+1;1)*
10^ROW(INDIRECT("$1:$"&LEN(C3)))/10);"")

Perhatikan contoh nomor 4. Rumus ini berbeda dengan rumus sebelumnya yang mengambil angka dengan fungsi Left atau Right dan menghasilkan data teks, nilai yang dihasilkan oleh rumus ini adalah data numeric atau angka. Sehingga secara default angka 0 didepan angka akan langsung dihilangkan karena dianggap tidak bernilai.

Saya kira untuk rumus ini tidak perlu di perjelas lebih panjang lagi, sebab akan cukup panjang sekali. Jadi silahkan di copy-paste saja.