3 Cara Mengambil Angka Mengandung Huruf di Excel Menggunakan Rumus Dasar

Microsoft Excel adalah salah satu perangkat lunak spreadsheet yang paling populer digunakan di seluruh dunia. Banyak dari kita menggunakan Excel untuk mengelola dan menganalisis data, dan seringkali kita harus mengambil angka mengandung huruf di Excel yakni situasi di mana data yang kita miliki mengandung campuran huruf dan angka. Bagaimana cara mengambil angka dari data semacam ini? Artikel ini akan membahas berbagai metode dan rumus yang dapat Anda gunakan untuk mengatasi masalah ini di Microsoft Excel.

Mengambil Angka Mengandung Huruf di Excel Menggunakan Fungsi MID:

Salah satu cara termudah untuk mengambil angka mengandung huruf di Excel adalah dengan menggunakan rumus MID. Rumus-rumus ini memungkinkan Anda untuk mengekstrak sejumlah karakter tertentu dari teks di sel Excel.

Sayangnya untuk contoh rumus MID ini tidak bisa mengambil angka ketika letak huruf dan angkanya tidak beraturan. Rumus MID hanya bisa digunakan ketika huruf-huruf terletak di depan atau tengah data saja.

Anda bisa menggunakan fungsi MID berikut:

=MID(A3; MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A3&"0123456789")); LEN(A3)-COUNTIF(A3;"*"))*B3

Dengan catatan tipe data tipe datanya seperti tabel di bawah ini:

Dan berikut adalah hasil akhirnya:

Penjelasan Rumus

Berikut adalah penjelasan lengkap tentang penggunaan rumus MID untuk mengambil angka mengandung huruf di Excel:

1. MID(A3;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9}; A3&"0123456789")); LEN(A3)-COUNTIF(A3; "*")) Ini adalah bagian pertama dari rumus. Mari kita break down setiap bagian dari rumus ini:

• A3: Ini adalah sel atau range sel di mana Anda ingin mengambil potongan teks.
• SEARCH({0;1;2;3;4;5;6;7;8;9}; A3&"0123456789"): Ini adalah fungsi SEARCH yang mencari setiap karakter dalam array {0;1;2;3;4;5;6;7;8;9} di dalam teks yang ada di sel A3 ditambah dengan string “0123456789”. Ini dilakukan untuk menemukan indeks karakter pertama yang sesuai dalam sel A3.
• MIN(SEARCH({0;1;2;3;4;5;6;7;8;9}; A3&"0123456789")): Ini mengambil nilai minimum dari hasil pencarian karakter, yang seharusnya adalah indeks karakter pertama yang cocok dalam sel A3.
• LEN(A3): Ini adalah panjang seluruh teks dalam sel A3.
• COUNTIF(A3; "*"): Ini adalah fungsi COUNTIF yang menghitung berapa kali karakter wildcard (“*”) muncul dalam sel A3. Karakter wildcard ini sering digunakan untuk menghitung jumlah sel yang tidak kosong. Setelah kita memahami setiap bagian ini, kita mendapatkan indeks karakter pertama yang sesuai dalam sel A3 dan panjang seluruh teks dalam sel A3 dikurangi dengan jumlah karakter wildcard yang muncul dalam sel tersebut. Dengan kata lain, kita mendapatkan panjang teks yang ingin kita ekstrak dari sel A3.

1. * B3 Ini adalah bagian kedua dari rumus. Setelah kita mendapatkan panjang teks yang ingin diekstrak, kita mengalikannya dengan nilai dalam sel B3. Ini mungkin dilakukan untuk menghitung hasil tertentu berdasarkan panjang teks yang diekstrak dari sel A3, kemudian dikalikan dengan nilai dalam sel B3.

Jadi, rumus mengambil angka mengandung huruf di Excel ini sebagian besar digunakan untuk mengekstrak sebagian dari teks dalam sel A3 berdasarkan karakter pertama yang sesuai dengan angka (0 hingga 9), kemudian mengalikannya dengan nilai dalam sel B3. Hasil akhirnya akan menjadi hasil dari rumus ini.

Mengambil Angka Mengandung Huruf di Excel Menggunakan Fungsi LEFT:

Selain menggunakan fungsi MID, Anda juga dapat menggunakan fungsi LEFT untuk mengambil angka mengandung huruf di Excel. Dengan catatan tipe datanya adalah angka-angka yang ada terletak di depan huruf.

Anda bisa menggunakan fungsi LEFT berikut:

=IFERROR(LEFT(B3;SUM(LEN(B3)-LEN(SUBSTITUTE(B3;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))*C3;"")

Dengan catatan tipe data tipe datanya seperti tabel di bawah ini:

Dan berikut adalah hasil akhirnya:

Penjelasan Rumus

Rumus Excel mengambil angka mengandung huruf di Excel ini sebenarnya adalah rumus yang digunakan untuk menghitung jumlah karakter angka (0 hingga 9) dalam sel B3, lalu mengambil sejumlah karakter dari awal sel B3 dan mengalikannya dengan nilai dalam sel C3. Rumus ini juga mengatasi kesalahan jika tidak ada karakter angka dalam sel B3. Mari kita bahas secara rinci:

1. IFERROR( ... ; "") Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan. Jika ekspresi di dalam tanda kurung tidak menghasilkan kesalahan, maka hasilnya akan sesuai dengan ekspresi tersebut. Namun, jika ada kesalahan, maka hasilnya akan kosong (“”), yaitu tanda kutip ganda tanpa isi.
2. LEFT(B3; SUM(LEN(B3) - LEN(SUBSTITUTE(B3; {"0"; "1"; "2"; "3"; "4"; "5"; "6"; "7"; "8"; "9"}; ""))) Ini adalah bagian utama dari rumus yang akan dievaluasi jika tidak ada kesalahan. Mari kita jelaskan setiap bagian:

• B3: Ini adalah sel atau range sel yang berisi teks dari mana Anda ingin mengambil sebagian karakter.
• SUBSTITUTE(B3; {"0"; "1"; "2"; "3"; "4"; "5"; "6"; "7"; "8"; "9"}; ""): Fungsi SUBSTITUTE digunakan untuk menghapus semua karakter angka (0 hingga 9) dari sel B3. Setiap karakter dalam array yang disebutkan dalam kurung kurawal akan digantikan dengan string kosong (“”). Dengan kata lain, ini menghapus semua angka dari sel B3.
• LEN(B3) - LEN(SUBSTITUTE(B3; {"0"; "1"; "2"; "3"; "4"; "5"; "6"; "7"; "8"; "9"}; "")): Ini adalah perbedaan panjang teks asli dalam sel B3 dan panjang teks yang telah dihapus karakter angkanya. Hasilnya adalah jumlah karakter angka dalam sel B3.
• SUM(LEN(B3) - LEN(SUBSTITUTE(B3; {"0"; "1"; "2"; "3"; "4"; "5"; "6"; "7"; "8"; "9"}; ""))): Fungsi SUM digunakan untuk menjumlahkan hasil perbedaan panjang karakter dari setiap karakter angka. Ini menghasilkan jumlah total karakter angka dalam sel B3.
• LEFT(B3; ... ): Fungsi LEFT digunakan untuk mengambil sejumlah karakter pertama dari sel B3, dan jumlah karakter ini adalah hasil dari perhitungan di atas (jumlah karakter angka dalam sel B3).

1. * C3 Setelah kita mengambil sejumlah karakter dari sel B3, kita mengalikannya dengan nilai dalam sel C3. Ini dilakukan untuk menghitung hasil tertentu berdasarkan jumlah karakter angka dalam sel B3 dan nilai dalam sel C3.

Jadi, keseluruhan rumus mengambil angka mengandung huruf di Excel ini menghasilkan hasil akhir yang berupa teks yang diambil dari awal sel B3, yang panjangnya ditentukan oleh jumlah karakter angka dalam sel tersebut, dan hasilnya dikalikan dengan nilai dalam sel C3. Jika tidak ada karakter angka dalam sel B3, maka hasilnya akan kosong.

Mengambil Angka Mengandung Huruf di Excel Menggunakan SUMPRODUCT

Dan pilihan terakhir yang bisa Anda gunakan untuk mengambil angka mengandung huruf di Excel adalah dengan menggunakan fungsi SUMPRODUCT.

Berbeda dengan kedua fungsi di atas, untuk fungsi SUMPRODUCT ini penulisan rumusnya jauh lebih panjang dan sedikit rumit.

Ini terjadi karena rumus ini mengakomodir segala jenis data. Seacak apapun datanya dengan rumus ini angka-angka tersebut dapat diambil atau dipisahkan.

Dan rumus yang digunakan untuk mengambil angka dari sebuah data acak yang berisi angka dan huruf adalah sebagai berikut:

=SUMPRODUCT(MID(0&B3;LARGE(INDEX(ISNUMBER(--MID(B3;ROW($1:$25);1))*ROW($1:$25);0);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)

Dengan rumus mengambil angka mengandung huruf di Excel ini Anda bisa mengambil angka dari contoh tabel yang serumit ini:

Dan inilah hasil akhirnya:

Penjelasan Rumus

Rumus Excel ini untuk menghitung jumlah dari angka-angka yang ada dalam teks yang terdapat dalam sel B3. Rumus ini cukup rumit dan melibatkan beberapa fungsi dan operasi. Mari kita jelaskan secara rinci:

=SUMPRODUCT(MID(0&B3; LARGE(INDEX(ISNUMBER(--MID(B3; ROW($1:$25); 1)) * ROW($1:$25); 0); ROW($1:$25)) + 1; 1) * 10 ^ ROW($1:$25) / 10)

1. MID(0&B3; ... ): Bagian ini pertama-tama menggabungkan teks dalam sel B3 dengan “0”, dan kemudian menggunakan fungsi MID untuk memisahkan setiap karakter dalam teks ini.
2. --MID(B3; ROW($1:$25); 1): Fungsi MID digunakan untuk mengambil satu karakter pada waktu tertentu dari teks dalam sel B3. ROW($1:$25) digunakan untuk membuat array dari 1 hingga 25 yang mewakili nomor baris. Fungsi ini menghasilkan karakter angka dalam teks, dan — digunakan untuk mengkonversi karakter tersebut menjadi nilai numerik.
3. ISNUMBER(--MID(B3; ROW($1:$25); 1)): Ini menghasilkan array Boolean yang menunjukkan apakah karakter pada posisi tertentu dalam sel B3 adalah angka atau bukan.
4. INDEX(ISNUMBER(--MID(B3; ROW($1:$25); 1)) * ROW($1:$25); 0): Ini menggunakan INDEX untuk menghasilkan array yang berisi hasil perkalian antara array Boolean di langkah sebelumnya dan nomor baris yang sesuai.
5. LARGE(...; ROW($1:$25)): Ini menghasilkan array dari nilai terbesar ke terkecil dalam array di atas. Ini akan mengembalikan nomor baris karakter angka dalam urutan dari yang terbesar hingga yang terkecil.
6. + 1: Ini menambahkan 1 ke setiap nomor baris karena nomor baris dimulai dari 1.
7. 10 ^ ROW($1:$25) / 10: Ini menghasilkan array yang berisi eksponensiasi 10 pada nomor baris, lalu dibagi dengan 10. Ini digunakan untuk mengkonversi karakter angka dalam bentuk desimal, misalnya, “123” menjadi 100 + 20 + 3.
8. MID(...) * 10 ^ ... / 10: Ini menggabungkan kedua array sebelumnya, yaitu karakter angka dalam bentuk desimal.
9. SUMPRODUCT(...): Akhirnya, SUMPRODUCT digunakan untuk menghitung jumlah dari semua elemen dalam array yang dihasilkan pada langkah sebelumnya.

Jadi, keseluruhan rumus mengambil angka mengandung huruf di Excel ini menghitung jumlah dari angka-angka yang ada dalam sel B3, terlepas dari seberapa banyak angka dan di mana mereka berada dalam teks.

Kesimpulan

Mengambil Angka Mengandung Huruf di Excel dapat dilakukan dengan berbagai cara. Anda dapat memanfaatkan rumus LEFT, RIGHT, dan MID, fungsi TEXT dan ISNUMBER, atau bahkan Power Query untuk membersihkan dan mengambil angka dari data Anda. Pilihlah metode yang paling sesuai dengan kebutuhan Anda dan selesaikan tugas Anda dengan efisien di Excel. Semoga artikel ini membantu Anda mengatasi masalah ini dan meningkatkan kemampuan Anda dalam mengelola data di Microsoft Excel.

Dan yang terakhir, barangkali ada yang membutuhkan file contoh 3 Cara Mengambil Angka Mengandung Huruf di Microsoft Excel Menggunakan Rumus Dasar di atas, file Excel bisa di download melalui tautan berikut ini: