Rumus Mengambil atau Memisahkan Angka di Excel

Untuk mengambil sebagian teks, Microsoft Excel sdh menyediakan fungsi LEFT, MID & RIGHT. Namun, jika yg 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 yg akan di ambil:

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

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

Oleh sebab itu penting sebelum melanjutkan tutorial ini anda mempelajari terlebih dahulu cara memisahkan teks & angka yg sdh pernah dibahas pada halaman tersebut.

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

1. Mengambil Angka di Kanan Teks
2. Mengambil Angka di Kiri Teks
3. Mengambil Angka di Tengah

Rumus Mengambil Angka di Kanan Teks

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

Rumus 1: Mengambil Angka di Excel

Salah satu rumus excel yg bisa digunakan untuk mengambil angka pada teks excel yg berada di posisi sebelah kanan adalah sbgai brkut:

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

Rumus ini sprti yg sdh pernah kita bahas pada bagian lain blog tutorial excel ini.

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



Seperti yg sdh 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 yg 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)

Se&gkan untuk posisi pertama sebuah angka dlm teks bisa didapatkan dengan rumus excel brkut:

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 sprti terlihat pada contoh diatas rumus pada cell F3, untuk mengambil angka pada cell C3 adalah sbgai brkut:

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

Untuk Rumus Excel diatas, Anda bisa juga mengganti fungsi FIND dengan fungsi SEARCH.

Rumus 2: Memisahkan Angka Di Excel

Selain menggunakan rumus di atas, Anda juga dapat menggunakan rumus excel brkut untuk memisahkan angka yg 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 brkut:



Pada contoh di atas rumus excel yg 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 & kita lanjutkan ke pembahasan brkutnya dulu.

Rumus Mengambil Angka di kiri Teks

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

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

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

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

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

Perhatikan contoh brkut:



Rumus excel yg digunakan pada cell D3 adalah sbgai brkut:

=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 yg digunakan adalah fungsi LEFT.

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

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

Kemudian jumlah karakter setiap teks yg tlah diubah tadi digunakan untuk mengurangi jumlah karakter asli tanpa pengubahan.

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

Untuk lebih jelasnya brkut 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 adalah bagian dari fungsi-fungsi text pada excel sehingga nilai angka yg dihasilkan juga berupa data Teks meskipun terlihat sprti angka pada umumnya.

Rumus Mengambil Angka di Tengah Teks

Mengambil angka di awal teks sdh. Di akhir teks sdh, 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 brkut:

=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 yg 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 sprti 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 yg mengambil angka dengan fungsi Left atau Right & menghasilkan data teks, nilai yg 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 slahkan di copy-paste saja.

Berikut beberapa fungsi yg digunakan pada rumus excel di atas:

1. Fungsi Left, Mid, Right
2. Fungsi Len
3. Fungsi Substitute
4. Fungsi Find-Search
5. Fungsi Sum
6. Fungsi Sumproduct
7. Fungsi Min
8. Fungsi Large
9. Fungsi Indirect
10. Fungsi Index
11. Fungsi Isnumber
12. Fungsi Row

Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: