Rumus Mengambil Atau Memisahkan Angka Di Excel
Untuk mengambil sebagian teks, Microsoft Excel sudah menyediakan fungsi LEFT, MID dan RIGHT. Namun, jikalau yang ingin kita ambil yaitu 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:
- Angka berada di sebelah kanan teks
- Angka berada di sebelah kiri teks
- Angka berada di posisi tengah teks
Untuk kasus pertama, dimana posisi angka yang diambil berada di sebelah kanan, sebetulnya sudah pernah kita bahas pada halaman tutorial yang menjelaskan wacana Rumus Memisahkan teks dan Angka di Excel.
Oleh alasannya yaitu 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 simpulan teks, ada 2 rumus excel yang sanggup Anda gunakan.
Rumus 1: Mengambil Angka di Excel
Salah satu rumus excel yang sanggup dipakai untuk mengambil angka pada teks excel yang berada di posisi sebelah kanan yaitu sebagai berikut:
RIGHT(RefTeks;LEN(RefTeks)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};RefTeks&"0123456789"))+1)Rumus ini menyerupai yang sudah pernah kita bahas pada bab lain blog tutorial excel ini.
RefTeks pada rumus tersebut yaitu 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, sanggup dilakukan dengan memanfaatkan fungsi RIGHT Excel.
RIGHT(RefTeks;JumlahKarakter)Hanya saja ketika memakai fungsi Right, kita juga perlu tahu berapa jumlah atau banyak karakter yang akan diambil.
Banyak karakter ini sanggup didapatkan dengan mengurangi jumlah keseluruhan teks dengan posisi pertama angka pada teks tersebut.
Untuk mengetahui jumlah atau banyak karakter sebuah cell sanggup kita dapatkan dengan fungsi LEN:
LEN(RefTeks)Sedangkan untuk posisi pertama sebuah angka dalam teks sanggup 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 agar angka pertama ikut terambil.
Hasil risikonya menyerupai terlihat pada contoh diatas rumus pada cell F3, untuk mengambil angka pada cell C3 yaitu sebagai berikut:
=RIGHT(C3;LEN(C3)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};C3&"0123456789"))+1)Untuk Rumus Excel diatas, Anda sanggup juga mengganti fungsi FIND dengan fungsi SEARCH.
Rumus 2: Memisahkan Angka Di Excel
Selain memakai rumus di atas, Anda juga sanggup memakai 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 dipakai 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 terang juga, harap bersabar dan kita lanjutkan ke pembahasan berikutnya dulu.
Rumus Mengambil Angka di kiri Teks
Bagaimana cara mengambil angkanya saja, jikalau posisi angka ada di sebelah kiri teks?
Jika posisi angka yang diambil ada di awal teks, tentunya kita tidak sanggup memakai posisi pertama angka sebagai acuan. Yang sanggup dipakai pola yaitu posisi huruf pertama pada teks tersebut. Namun dalam kasus ini kita tidak akan memakai cara tersebut.
Untuk mengambil angka saja yang ada di sebelah kanan teks, kita gunakan cara kedua saja. Namun, dengan sedikit adaptasi sehingga rumus excelnya menjadi menyerupai 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 menyerupai sebelumnya, sesuaikan RefTeks yaitu referensi cell atau teks yang akan diambil angkanya saja.
Perhatikan contoh berikut:
Rumus excel yang dipakai pada cell D3 yaitu 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 dipakai yaitu fungsi LEFT.
Adapun rumus SUM(....) akan menghasilkan jumlah angka yang akan di ambil dari awal kata.
Pada rumus diatas fungsi LEN dan fungsi SUBSTITUTE dipakai 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 dipakai untuk mengurangi jumlah karakter orisinil tanpa pengubahan.
Operasi perhitungan ini terjadi pada setiap angka dalam konstanta array. Hasilnya lalu 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 risikonya didapatkanlah teks angka 1440. Masih galau juga? Silahkan dibaca ulang lagi. Hehehehe...
Untuk mengingatkan bahwa fungsi LEFT, MID atau RIGHT merupakan bab dari fungsi-fungsi text pada excel sehingga nilai angka yang dihasilkan juga berupa data Teks meskipun terlihat menyerupai angka pada umumnya.
Rumus Mengambil Angka di Tengah Teks
Mengambil angka di awal teks sudah. Di simpulan teks sudah, kini 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 sanggup 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 sanggup menambahkan fungsi IF Excel pada rumus tersebut lalu cek apakah pada teks ada angkanya atau tidak. Jika tidak mengandung angka, isi dengan teks kosong.
Logika yang kita gunakan yaitu 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 menyerupai 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 yaitu data numeric atau angka. Sehingga secara default angka 0 didepan angka akan eksklusif dihilangkan lantaran dianggap tidak bernilai.
Saya kira untuk rumus ini tidak perlu di perjelas lebih panjang lagi, alasannya yaitu akan cukup panjang sekali. Makara silahkan di copy-paste saja.
Berikut beberapa fungsi yang dipakai pada rumus excel di atas:
Post a Comment for "Rumus Mengambil Atau Memisahkan Angka Di Excel"