MEMISAHKAN ANGKA DAN TEXT

CONTOH :
^ Rp                      8,500,000.00
 Menjadi : 8,500,000


RUMUS :
=MID(G1194,MIN(FIND({0,1,2,3,4,5,6,7,8,9},G1194&"0123456789")),SUM(LEN(G1194)-LEN(SUBSTITUTE(G1194,{1,2,3,4,5,6,7,8,9,0},""))))


__________________________________________
LENGKAPNYA
__________________________________________

Pernahkah anda mendapatkan sebuah data excel, dimana data tersebut berisi gabungan teks dan angka tertentu, lalu anda bermaksud untuk mendapatkan teks-nya saja atau mengambil angka-nya saja dari data excel tersebut?



Jika pola gabungan teks dan angka tersebut sama (misal: terdiri dari 6 huruf dan 4 angka), maka untuk memisahkannya akan cukup mudah dengan menggunakan fungsi LEFT, MID atau RIGHT pada microsoft excel yang sudah kita bahas pada tutorial sebelumnya.

Apabila jumlah karakter teks dan angkanya tidak sama maka tentunya tidak akan cukup jika hanya menggunakan fungsi-fungsi pengambilan karakter tersebut. Sebagai contoh perhatikan gambar dibawah ini :



Dari gambar tersebut bagaimanakah rumus excel yang digunakan untuk memisahkan atau mendapatkan nilai teks atau mengambil nilai angkanya sehingga didapatkan data pada kolom B dan C.

Untuk model data seperti diatas, kita tetap bisa menggunakan fungsi LEFT, MID atau Right. Dimana fungsi LEFT digunakan untuk mengambil beberapa karakter dari kiri sedangkan fungsi MID digunakan untuk mengambil beberapa karakter dari tengah (posisi tertentu) dan fungsi RIGHT digunakan untuk mengambil beberapa karakter dari sebelah kanan.

Apabila jumlah karakter huruf dan angka pada masing-masing sel sama tentunya akan sangat mudah untuk diketahui dari posisi mana dan berapa jumlah karakter yang akan diambil.

Lain persoalan jika jumlah huruf atau angka yang diambil tidak tentu posisi dan berapa jumlah karakternya.

Salah satu solusi rumus excel untuk masalah ini adalah sebagai berikut:

CARA MENCARI POSISI ANGKA PERTAMA

Langkah pertama yang perlu kita lakukan untuk mengambil teks atau mengambil angka pada sel gabungan teks dan angka tersebut adalah mencari posisi angka pertama pada teks tersebut.

Untuk mendapatkan posisi angka pertama anda bisa menggunakan rumus excel berikut:

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

Dimana Teks bisa anda gantikan dengan referensi sel yang mengandung gabungan teks dan angka yang ingin anda pisahkan datanya.

Misal untuk mendapatkan posisi angka pertama pada gabungan teks yang ada di sel A2 maka rumus excelnya sebagai berikut:

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

Untuk lebih jelasnya perhatikan gambar berikut:

Penjelasan tentang cara kerja rumus excel diatas bisa anda baca pada bagian akhir tutorial ini.

RUMUS EXCEL UNTUK MENGAMBIL TEKS

Setelah mendapatkan posisi angka pertama maka kita bisa lanjut untuk menentukan rumus excel yang digunakan untuk mengambil nilai teks dari sel gabungan teks dan angka tertentu.

rumus excel yang digunakan untuk mengambil teks adalah sebagai berikut:

=LEFT(A2;PosisiAngkaPertama-1)

Sehingga untuk mendapatkan teks dari sel A2 rumus excel yang digunakan adalah:

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

Agar lebih jelas perhatikan gambar berikut untuk masing-masing sel:



Kenapa posisi teks harus dikurangi 1, sebab jumlah karakter yang akan kita ambil tentunya tidak termasuk angka pertama, sehingga posisi angka pertama hanya dijadikan acuan banyak karakter yang kita ambil dari kiri dengan menggunakan fungsi LEFT excel.

RUMUS EXCEL UNTUK MENGAMBIL ANGKA

Sebab posisi angka ada di sebelah kanan, maka cara untuk mengambil angka saja adalah dengan menggunakan fungsi RIGHT. Sedangkan jumlah karakter yang diambil bisa kita dapatkan dengan cara mengurangi jumlah keseluruhan karakter teks pada sel terkait dengan posisi angka pertama.

Posisi angka pertama sudah kita bahas diatas bagaimana cara menentukannya. Sedangkan untuk mengetahui jumlah karakter sebuah sel adlaah dengan mnggunakan fungsi LEN Excel.

Sehingga rumus excel untuk mengambil angka dari gabungan teks tersebut adalah:

=RIGHT(A2;LEN(A2)-PosisiAngkaPertama+1)

Perhatikan gambar berikut:


Pada contoh gambar tersebut rumus excel yang digunakan pada sel B2 adalah:

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

Loch kog posisinya ditambah 1, Yup kalau tidak maka angka pertama tidak akan ikut terambil.

Fungsi LEFT, MID dan RiGHT menghasilkan nilai teks, agar menjadi nilai numeric hasil dari rumus excel di atas perlu dikonversi lagi dengan fungsi VALUE pada microsoft Excel

CARA KERJA RUMUS EXCEL

Pada bagian ini saya tidak akan menjelaskan tentang cara kerja fungsi-fungsi pengambilan karakter LEFT,MID dan RIGHT. Saya yakin anda sudah cukup familiar dengan ketiga fungsi tersebut. Jika anda memang berminat silahkan dibaca pada penjelasan tentang ketiga fungsi ini pada laman berikut:

Cara Mengambil Beberapa Karakter Teks pada Microsoft Excel

Pada bagian ini saya hanya akan menjelaskan tentang cara kerja rumus excel yang digunakan untuk mencari posisi angka pertama yakni:

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

Rumus diatas menggunakan fungsi FIND Excel untuk mencari posisi pertama masing-masing angka dari Array {0;1;2;3;4;5;6;7;8;9} pada teks.

Misal pada teks "Meja98" maka Fungsi FIND akan mencari posisi 0 pada teks Meja980123456789 dan menghasilkan angka 7. Kemudian mencari posisi angk 1 pada teks Meja980123456789 dan menghasilkan angka 8, dan seterusnya secara bersamaan sampai angka 9 sehingga fungsi FIND angka menghasilkan array berikut:

{7;8;9;10;11;12;13;14;15;6;5}

Dari array diatas lalu rumus diatas menggunakan fungsi MIN untuk mencari nilai terkecil dari array tersebut yang menghasilkan angka 5.

MIN({7;8;9;10;11;12;13;14;15;6;5})

Nilai 5 inilah yang merupakan posisi pertama angka pada teks yang didapatkan dari pencarian angka 9 dengan fungsi FIND.

Jika anda bertanya kenapa pada teks tersebut perlu di gabung dengan teks angka "0123456789". Hal ini agar fungsi FIND tidak menghasilkan nilai error.


Refferensi :
© Kelas Excel | https://www.kelasexcel.web.id/2017/06/cara-memisahkan-teks-angka-di-excel.html