Rumus Mengambil atau Memisahkan Angka di Excel
Untuk mengambil sebagian teks, Microsoft Excel sudah menyediakan fungsi LEFT, MID dan RIGHT. Namun, kalau yang ingin kita ambil ialah angka saja bagaimana rumusnya?
Tutorial excel kali ini akan membahas rumus excel untuk mengambil angka atau Tutorial 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 perkara pertama, dimana posisi angka yang diambil berada di sebelah kanan, bantu-membantu 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 balasan teks, ada 2 rumus excel yang bisa Anda gunakan.
Rumus 1: Mengambil Angka di Excel
Salah satu rumus excel yang mampu 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 seakan-akan yang sudah pernah kita bahas pada cuilan lain blog tutorial excel ini.
RefTeks pada rumus tersebut ialah referensi cell atau teks yang akan diambil sebagian angkanya. Silahkan perhatikan acuan berikut:
Seperti yang sudah umum diketahui bahwa untuk mengambil sebagian teks atau angka dari sebelah kanan, mampu dilakukan dengan memanfaatkan fungsi RIGHT Excel.
RIGHT(RefTeks;JumlahKarakter)Hanya saja saat memakai 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 mampu 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 seolah-olah terlihat pada pola diatas rumus pada cell F3, untuk mengambil angka pada cell C3 ialah 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 mampu juga mengganti fungsi FIND dengan fungsi SEARCH.
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 pola berikut:
Pada acuan di atas rumus excel yang digunakan pada cell D3 yaitu:
=RIGHT(C3;SUM(LEN(C3)-LEN(SUBSTITUTE(C3;"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";""))))Kog mampu?
Silahkan gunakan tombol Evaluate Formula untuk memahami Tutorial kerja rumus pada cell tersebut. Apabila masih belum terperinci juga, harap bersabar dan kita lanjutkan ke pembahasan berikutnya dulu.
Rumus Mengambil Angka di kiri Teks
Bagaimana Tutorial mengambil angkanya saja, jikalau posisi angka ada di sebelah kiri teks?
Jika posisi angka yang diambil ada di awal teks, tentunya kita tidak mampu memakai posisi pertama angka sebagai acuan. Yang mampu dipakai rujukan yaitu posisi aksara pertama pada teks tersebut. Namun dalam perkara 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 pembiasaan sehingga rumus excelnya menjadi seakan-akan 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 seakan-akan sebelumnya, sesuaikan RefTeks adalah referensi cell atau teks yang akan diambil angkanya saja.
Perhatikan pola berikut:
Rumus excel yang dipakai 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 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 digunakan untuk mengetahui berapa kali masing-masing digit angka 0-9 muncul dalam teks dengan Tutorial mengubah atau mengganti setiap angka dengan teks kosong("").
Kemudian jumlah karakter setiap teks yang telah diubah tadi digunakan untuk mengurangi jumlah karakter orisinil tanpa pengubahan.
Operasi perhitungan ini terjadi pada setiap angka dalam konstanta array. Hasilnya kemudian dijumlahkan oleh fungsi SUM excel.
Untuk lebih terperincinya 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 akibatnya didapatkanlah teks angka 1440. Masih galau juga? Silahkan dibaca ulang lagi. Hehehehe...
Untuk mengingatkan bahwa fungsi LEFT, MID atau RIGHT merupakan cuilan dari fungsi-fungsi text pada excel sehingga nilai angka yang dihasilkan juga berupa data Teks meskipun terlihat seolah-olah angka pada umumnya.
Rumus Mengambil Angka di Tengah Teks
Mengambil angka di awal teks sudah. Di akhir teks sudah, kini bagaimana rumus excel atau Tutorial 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 teladannya:
Jika Anda perhatikan, dengan rumus di atas, apabila RefTeks tidak mengandung angka sama sekali, ternyata rumus diatas menghasilkan angka nol(0)--Lihat acuan 11.
Supaya tidak menghasilkan angka nol(0), Anda mampu 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 ialah 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 seakan-akan 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 ialah 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, karena akan cukup panjang sekali. Jadi silahkan di copy-paste saja.
Berikut beberapa fungsi yang digunakan pada rumus excel di atas:
Komentar
Posting Komentar