Cara Mengekstrak Angka dari String di Excel: Menemukan Pendekatan yang Tepat

Saat bekerja di Excel untuk pembersihan data, Anda mungkin menemui tugas mengekstrak angka dari string alfanumerik. Masalah ini umum terjadi jika Anda memiliki data di mana angka tertanam dalam string, seperti kode produk atau respons survei.

Dalam artikel ini, saya akan menunjukkan berbagai metode untuk mengekstrak angka dari string menggunakan rumus seperti RIGHT(), LEN(), dan FIND() untuk berbagai kasus penggunaan. Jika Anda belum familier dengan beberapa dasar ini, saya merekomendasikan Anda mengikuti kursus Introduction to Excel kami untuk membangun fondasi yang kuat di Excel. Anda juga sebaiknya melihat Data Preparation in Excel untuk mempelajari lebih lanjut tentang transformasi data mentah.

Rumus Excel untuk Mengekstrak Angka dari String

Excel menyediakan berbagai metode berbasis rumus untuk mengekstrak angka dari string. Setiap metode cocok untuk kebutuhan berbeda berdasarkan lokasi angka dalam string. Jika Anda terburu-buru, silakan langsung ke bagian yang menyelesaikan kebutuhan paling mendesak Anda.

Mengekstrak angka dari awal string di Excel

Terkadang, Anda akan menjumpai skenario di mana angka berada di awal string. Dalam kasus seperti itu, Anda akan menggunakan LEFT() dan FIND() untuk mengekstrak angka dari string. Saat Anda menggabungkan fungsi-fungsi tersebut ke dalam sebuah rumus, angka diekstrak dengan logika berikut:

• FIND(): Menemukan posisi huruf pertama dalam string.

• LEFT(): Mengekstrak karakter dari awal string hingga posisi huruf pertama dikurangi satu.

Bagian pertama dalam rumus di bawah ini menemukan huruf pertama dalam string. Lalu, bagian kedua mengekstrak semua yang ada sebelum huruf tersebut, menyisakan angka di depan saja. Jika tidak ada huruf, rumus mengembalikan seluruh string.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Mengekstrak angka dari awal string di Excel. Gambar oleh Penulis.

Metode ini sesuai saat bekerja dengan dataset yang umum menggunakan prefiks numerik seperti nomor pesanan, ID anggota, dan nomor batch.

Mengekstrak angka dari akhir string di Excel

Jika Anda ingin mengekstrak angka dari akhir string di Excel, Anda perlu menggunakan fungsi RIGHT(), LEN(), dan FIND(). Misalkan Anda memiliki data berikut di lembar Excel. Anda akan menggabungkan fungsi-fungsi tersebut untuk mengekstrak angka dengan ketentuan:

• FIND(): Menemukan posisi digit pertama dalam string.

• LEN(): Menghitung jumlah karakter dari digit pertama hingga akhir string.

• RIGHT(): Mengekstrak karakter dari digit pertama hingga akhir string.

Rumus di bawah ini menghapus semua teks di depan dan mengembalikan angka pertama beserta semua yang mengikutinya dalam string.

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

Mengekstrak angka dari akhir string di Excel. Gambar oleh Penulis.

Metode ini paling berguna pada dataset dengan karakter alfanumerik yang konsisten, seperti SKU produk, ID pesanan, dan nomor seri.

Mengekstrak angka dari posisi mana pun dalam string di Excel

Jika Anda memiliki dataset di mana angka muncul di mana saja dalam string, Anda akan memerlukan rumus tingkat lanjut menggunakan TEXTJOIN(), MID(), dan ROW() untuk mengekstrak angka.

Menggabungkan fungsi-fungsi ini dalam sebuah rumus akan mengekstrak angka dari string dengan logika berikut: 

• ROW() dan MID(): Melintasi setiap karakter string untuk menentukan apakah karakter tersebut numerik.

• IFERROR(): Uji logis untuk mengisolasi angka yang teridentifikasi dari karakter lain.

• TEXTJOIN(): Menggabungkan semua karakter numerik yang diekstrak menjadi satu string angka.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Ekstrak angka dari posisi mana pun dalam string. Gambar oleh Penulis.

Metode ini paling cocok ketika dataset Anda memiliki karakter alfanumerik dengan angka yang dapat muncul di mana saja dalam string. Perlu dicatat, fungsi-fungsi ini tidak semuanya tersedia di versi Excel yang lebih lama. Lihat jalur keterampilan Excel Fundamentals kami untuk mempelajari lebih lanjut tentang fungsi dan rumus yang tersedia di versi Excel terbaru.

Mengekstrak angka dengan menyaring karakter yang tidak diinginkan

Terkadang, data Anda berantakan. Untungnya, Anda dapat menggabungkan fungsi SUBSTITUTE() dan TEXTJOIN(). Ikuti langkah-langkah ini:

• SUBSTITUTE(): Fungsi SUBSTITUTE() dapat digunakan untuk mengganti semua karakter non-numerik dalam string dengan pembatas, seperti spasi atau string kosong. Misalnya, Anda dapat mengganti huruf dan karakter khusus dengan spasi.

• TEXTJOIN(): Fungsi TEXTJOIN() kemudian dapat digunakan untuk menggabungkan karakter yang tersisa (yang sekarang seharusnya hanya angka) menjadi satu string.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Mengekstrak angka dari string dengan menyaring karakter. Gambar oleh Penulis.

Kapan Anda Mungkin Perlu Mengekstrak Angka dari String di Excel

Sekarang mari kita lihat dua studi kasus kecil untuk berlatih mengekstrak angka dari string.

Contoh 1: Mengekstrak angka dari kode produk

Misalkan Anda mengelola inventaris produk di mana setiap item diidentifikasi oleh kode produk unik. Kode produk ini berupa string alfanumerik seperti "PROD1234," "ITEM5678," atau "SKU91011."

Jadi, untuk mengekstrak Product ID numerik dari kode produk alfanumerik, kita menggunakan fungsi berdasarkan posisi angkanya. Ingat, jika angka berada di akhir, terapkan RIGHT(), LEN(), dan FIND(). Jika angka muncul di awal, gunakan LEFT() dan FIND().

Contoh di bawah ini mengekstrak bagian numerik dari kode produk dari sisi kanan kode.

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

Mengekstrak angka dari kode produk di Excel. Gambar oleh Penulis.

Contoh 2: Data survei dengan angka yang tertanam

Data survei terkadang berisi teks dan angka seperti "Rated 5 out of 10," "Score: 8," atau "3 stars". Kita harus melakukan sedikit pekerjaan sebelum dapat menghitung rata-rata atau mengidentifikasi tren.

Karena angka dapat muncul di mana saja dalam teks, diperlukan rumus dinamis menggunakan TEXTJOIN(), MID(), dan ROW().

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Mengekstrak angka dari posisi mana pun di Excel. Gambar oleh Penulis.

Salin dan Tempel Contoh Kode untuk Kasus Khusus

Di bagian terakhir, saya ingin memberikan beberapa rumus tambahan yang menangani skenario berbeda dan spesifik. Silakan salin dan tempel, dan semoga ini menyelesaikan masalah khusus yang Anda hadapi. 

Ekstrak blok angka berurutan pertama

Jika string Anda berisi beberapa urutan numerik tetapi Anda hanya membutuhkan kelompok pertama, coba solusi ini menggunakan fungsi LET() dan SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Mengekstrak blok berurutan pertama dari string di Excel. Gambar oleh Penulis.

Mengekstrak angka di dalam tanda kurung

Untuk kasus di mana angka muncul di dalam tanda kurung, gunakan:

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Mengekstrak angka yang ada di dalam tanda kurung di Excel. Gambar oleh Penulis.

Mengekstrak angka setelah karakter tertentu

Saat data Anda menggunakan titik dua sebagai pembatas (misalnya, "Score: 8"), tangkap angkanya dengan:

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Mengekstrak angka setelah karakter khusus di Excel. Gambar oleh Penulis.

Rumus ini menarik semua yang berada setelah titik dua. Bungkus hasilnya dengan VALUE() jika Anda memerlukan nilai numerik. Ganti titik dua jika Anda memiliki pembatas lain.

Mengekstrak semua angka dan menggabungkannya menjadi satu nilai

Untuk menghapus semua karakter non-digit dan menggabungkan setiap karakter numerik dalam sebuah string, gunakan ini:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Mengekstrak semua angka dan menggabungkannya menjadi satu nilai di Excel. Gambar oleh Penulis.

Tip: Di versi Excel yang lebih lama, masukkan ini sebagai rumus array (Ctrl+Shift+Enter).

Mengekstrak angka termasuk titik desimal

Untuk string numerik yang mungkin menyertakan desimal (misalnya, "Price: 45.99"), gunakan rumus ini untuk mempertahankan digit dan titik desimal:

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Mengekstrak angka termasuk titik desimal di Excel. Gambar oleh Penulis.

Catatan: Rumus ini mengasumsikan hanya ada satu titik desimal.

Kesimpulan

Mengekstrak angka dari string adalah teknik penting untuk pembersihan data. Sebagai analis data, saya mendorong Anda untuk mempelajari cara menerapkan berbagai rumus yang dibahas dalam artikel ini.

Jika Anda ingin meningkatkan keterampilan Excel, saya merekomendasikan mengikuti kursus Data Analysis in Excel dan Data Visualization in Excel kami untuk menguasai analisis dan penyajian data. Coba juga kursus  Financial Modeling in Excel kami yang dapat membuka banyak peluang.