Rumus Mengambil Atau Memisahkan Angka Di Excel | Kelas Excel
Rumus excel mengambil angka dari teks atau cara memisahkan angka di excel baik angka di permulaan , di tengah atau di tamat teks.
Untuk mengambil sebagian teks , Microsoft Excel sudah menawarkan fungsi LEFT , MID dan RIGHT. Namun , jikalau yang ingin kita ambil yakni angka saja bagaimana rumusnya?
Tutorial excel kali ini akan membahas rumus excel untuk mengambil angka atau cara memisahkan angka di excel. Pada problem mengambil angka ini , ada tiga kemungkinan posisi angka pada teks yang hendak di ambil:
- Angka berada di sebelah kanan teks
- Angka berada di sebelah kiri teks
- Angka berada di posisi tengah teks
Untuk problem pertama , dimana posisi angka yang diambil berada di sebelah kanan , sebenarnya sudah pernah kita diskusikan pada halaman bimbingan yang menerangkan tentang Rumus Memisahkan teks dan Angka di Excel.
Oleh alasannya yakni itu penting sebelum melanjutkan bimbingan ini anda mempelajari apalagi dahulu cara memisahkan teks dan angka yang sudah pernah dibahas pada halaman tersebut.
Kalau sudah , mari kita teruskan pembahasan mengenai rumus excel memisahkan angka ini.
Rumus Mengambil Angka di Kanan Teks
Untuk memisahkan angka yang berada disebelah kanan atau tamat teks , ada 2 rumus excel yang sanggup Anda gunakan.
Rumus 1: Mengambil Angka di Excel
Salah satu rumus excel yang sanggup digunakan untuk mengambil angka pada teks excel yang berada di posisi sebelah kanan yakni selaku 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 diskusikan pada bab lain blog bimbingan excel ini.
RefTeks pada rumus tersebut yakni referensi cell atau teks yang hendak diambil sebagian angkanya. Silahkan amati contoh berikut:
Seperti yang sudah lazim dikenali bahwa untuk mengambil sebagian teks atau angka dari sebelah kanan , sanggup dijalankan dengan mempergunakan fungsi RIGHT Excel.
RIGHT(RefTeks;JumlahKarakter)Hanya saja ketika memakai fungsi Right , kita juga perlu tahu berapa jumlah atau banyak karakter yang hendak diambil.
Banyak karakter ini sanggup ditemukan dengan meminimalisir jumlah keseluruhan teks dengan posisi pertama angka pada teks tersebut.
Untuk mengenali jumlah atau banyak karakter suatu cell sanggup kita peroleh dengan fungsi LEN:
LEN(RefTeks)Sedangkan untuk posisi pertama suatu angka dalam teks sanggup ditemukan 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 mudah-mudahan angka pertama ikut terambil.
Hasil alhasil menyerupai terlihat pada contoh diatas rumus pada cell F3 , untuk mengambil angka pada cell C3 yakni selaku 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 merubah 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 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 mengetahui cara kerja rumus pada cell tersebut. Apabila masih belum terang juga , harap bersabar dan kita teruskan ke pembahasan selanjutnya 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 permulaan teks , pastinya kita tidak sanggup memakai posisi pertama angka selaku acuan. Yang sanggup digunakan pola yakni posisi huruf pertama pada teks tersebut. Namun dalam problem 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 yakni referensi cell atau teks yang hendak diambil angkanya saja.
Perhatikan contoh berikut:
Rumus excel yang digunakan pada cell D3 yakni selaku 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 yakni fungsi LEFT.
Adapun rumus SUM(....) akan menciptakan jumlah angka yang hendak di ambil dari permulaan kata.
Pada rumus diatas fungsi LEN dan fungsi SUBSTITUTE digunakan untuk mengenali berapa kali masing-masing digit angka 0-9 timbul dalam teks dengan cara merubah atau merubah setiap angka dengan teks kosong("").
Kemudian jumlah karakter setiap teks yang sudah diubah tadi digunakan untuk meminimalisir jumlah karakter orisinil tanpa pengubahan.
Operasi perkiraan 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 alhasil didapatkanlah teks angka 1440. Masih galau juga? Silahkan dibaca ulang lagi. Hehehehe...
Untuk mengingatkan bahwa fungsi LEFT , MID atau RIGHT ialah bab dari fungsi-fungsi text pada excel sehingga nilai angka yang dihasilkan juga berupa data Teks walaupun terlihat menyerupai angka pada umumnya.
Rumus Mengambil Angka di Tengah Teks
Mengambil angka di permulaan teks sudah. Di tamat 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 dijalankan 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 amati , dengan rumus di atas , apabila RefTeks tidak mengandung angka sama sekali , ternyata rumus diatas menciptakan angka nol(0)--Lihat contoh 11.
Supaya tidak menciptakan angka nol(0) , Anda sanggup menyertakan 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 yakni dengan menjumlah 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 berlainan dengan rumus sebelumnya yang mengambil angka dengan fungsi Left atau Right dan menciptakan data teks , nilai yang dihasilkan oleh rumus ini yakni 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 yakni akan cukup panjang sekali. Makara silahkan di copy-paste saja.
Berikut beberapa fungsi yang digunakan pada rumus excel di atas:
Tidak ada komentar untuk "Rumus Mengambil Atau Memisahkan Angka Di Excel | Kelas Excel"
Posting Komentar