Cara Mengekstrak Angka Dari String di Excel

Bagaimana caranya

OlehAdam Davidson

Diterbitkan7 Juni 2023

Excel adalah spreadsheet yang kuat, tetapi tidak dapat menghasilkan keajaiban. Jika Anda mengimpor data, dan data tersebut berantakan, Excel tidak akan dapat berbuat banyak dengannya. Misalnya, jika Anda memiliki nilai yang dilampirkan ke karakter lain sebagai bagian dari string, Anda tidak akan dapat melakukan perhitungan pada karakter tersebut kecuali jika Anda mengekstraknya terlebih dahulu.

Syukurlah, itu mungkin untuk melakukan hal itu, meskipun itu bisa lebih rumit daripada yang mungkin Anda pikirkan. Berikut cara mengekstrak angka dari string di Excel.

Cara Mengekstrak Angka dari Ujung String Menggunakan Flash Fill

Cara cepat dan mudah untuk mengekstrak angka dari akhir string adalah dengan menggunakan Excel Isi Flash fitur. Fitur ini memungkinkan Anda mengisi rentang sel dengan cepat berdasarkan pola yang terdeteksi di satu sel tertentu. Satu kelemahan dari metode ini adalah Anda harus melakukan ekstraksi pertama sendiri dan jika Anda hanya ingin mengekstrak nomor dari satu string, itu tidak akan berguna.

Dalam kebanyakan kasus, Isi Cepat dapat mengekstrak angka dengan benar dari akhir string, meskipun tidak sempurna, jadi Anda harus selalu memeriksa hasilnya dengan hati-hati. Jika tidak berfungsi dengan benar, coba isi dua atau tiga sel pertama secara manual, bukan hanya yang pertama.

Untuk mengekstrak angka dari akhir string menggunakan Flash Fill:

1. Pilih sel di sebelah sel pertama yang berisi string Anda.
   
2. Masukkan nomor yang muncul di akhir string.
   
3. Jika semua data Anda berada dalam kolom kontinu tanpa celah, pilih sel tepat di bawah sel yang baru saja Anda masukkan nomornya.
   
4. Jika data Anda memiliki celah di beberapa baris, pilih sel pertama dan semua sel di bawahnya yang ingin Anda ekstrak angkanya, termasuk baris kosong.
   
5. Tekan Ctrl+E di Windows atau Cmd+E di Mac untuk menjalankan fitur Flash Fill.
6. Sel-sel Anda yang tersisa akan secara otomatis diisi berdasarkan pola yang terlihat oleh Excel. Dalam kebanyakan kasus, angka dari akhir string akan diekstrak dengan benar.
   

Cara Mengekstrak Angka dari Ujung String Menggunakan Rumus

Salah satu kelemahan menggunakan metode Isi Cepat adalah Anda harus mengekstrak sendiri angka dari string untuk baris pertama. Dimungkinkan untuk mengekstraksi angka dari akhir string menggunakan rumus, meskipun rumusnya cukup rumit.

Untuk mengekstrak angka dari akhir string menggunakan rumus:

1. Klik di sel tempat Anda ingin nomor yang diekstraksi muncul.
2. Masukkan rumus berikut, ganti 'B2' dengan sel yang berisi string Anda:

   =VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2)))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0))))

3. Tekan Memasuki.
4. Nomor tersebut harus diekstraksi dari string.
5. Untuk menerapkan rumus ke sel yang tersisa, klik gagang seret di sudut kanan bawah sel.
   
6. Seret ke bawah sel tempat Anda ingin rumus direplikasi.
   
7. Lepaskan mouse Anda dan formula akan disalin ke sel yang dipilih.
8. Sel-sel ini sekarang harus berisi nomor yang Anda ekstrak.
   
9. Setiap sel tanpa string untuk diekstrak akan mengembalikan kesalahan. Jika Anda ingin mengembalikan sel kosong, Anda bisa menggunakan rumus:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2))-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2)))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Setiap sel kosong sekarang akan mengembalikan ruang kosong.
    

Anda mungkin bertanya-tanya apa fungsi formula ini. Singkatnya, ini menghasilkan array yang panjangnya sama dengan string. Itu menempatkan setiap karakter dari string ke dalam array ini secara berurutan. Kemudian mengevaluasi masing-masing karakter tersebut untuk melihat apakah mereka angka atau bukan. Setiap karakter yang berupa angka akan dikonversi menjadi nol. Setiap karakter yang bukan angka akan dikonversi menjadi angka yang mewakili posisinya dalam string.

Misalnya, AB5HG23 akan diubah menjadi 1, 2, 0, 4, 5, 0, 0. Kemudian mencari nilai tertinggi dalam daftar ini, yaitu posisi karakter terakhir yang bukan angka. Terakhir, ini mengembalikan semua karakter setelah titik ini, yang merupakan angka di akhir string. Fungsi VALUE kemudian mengubahnya kembali menjadi angka. Sederhana, bukan?

Cara Mengekstrak Angka dari Awal String Menggunakan Flash Fill

Anda juga dapat menggunakan Isi Cepat untuk mengekstrak angka dari awal string dengan mengisi sendiri contoh pertama dan membiarkan Excel menghentikan polanya.

Untuk mengekstrak angka dari awal string menggunakan Flash Fill:

1. Pilih sel di sebelah kanan string pertama yang memiliki nomor yang ingin Anda ekstrak.
2. Masukkan nomor yang muncul di awal string.
   
3. Jika semua data Anda berada dalam kolom kontinu tanpa celah, pilih sel langsung di bawahnya.
   
4. Jika data Anda memiliki celah, pilih sel pertama dan semua sel di bawahnya yang ingin Anda ekstrak angkanya, termasuk baris kosong.
   
5. Tekan Ctrl+E di Windows atau Cmd+E di Mac untuk memulai Flash Fill.
6. Sel yang Anda pilih sekarang harus menunjukkan angka yang diekstraksi.
   

Cara Mengekstrak Angka dari Awal String Menggunakan Rumus

Anda juga bisa menggunakan rumus untuk mengekstrak angka dari awal string. Ini bekerja dengan cara yang mirip dengan rumus di bagian sebelumnya tetapi menemukan posisi karakter non-numerik pertama dan mengembalikan semua karakter sebelumnya.

Untuk mengekstrak angka dari awal string menggunakan rumus:

1. Pilih sel tempat Anda ingin nomor yang diekstraksi muncul.
2. Masukkan rumus berikut, ganti 'B18' dengan sel yang berisi string Anda:

   =VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Tekan Memasuki dan nomor akan diekstrak.
   
4. Kamu bisa gunakan IsiOtomatis di Excel untuk menyalin rumus ke sel lain yang Anda perlukan.
5. Jika Anda ingin setiap baris kosong mengembalikan hasil kosong, gunakan rumus berikut sebagai gantinya:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Cara Mengekstrak Angka dari Mana Saja dalam String Menggunakan Flash Fill

Seperti disebutkan sebelumnya, Isi Cepat mungkin tidak berfungsi jika sel pertama tidak cocok dengan format beberapa sel lainnya. Dalam hal ini, Anda mungkin perlu menghitung dua atau tiga sel secara manual sebelum Flash Fill memberikan hasil yang Anda perlukan.

Untuk mengekstrak angka dari mana saja dalam string menggunakan Flash Fill:

1. Di sel di sebelah string pertama, ketik angka yang ada di string secara manual.
   
2. Jika semua data Anda berada dalam kolom berkelanjutan, pilih sel langsung di bawahnya.
   
3. Jika data Anda memiliki beberapa celah, pilih sel pertama dan semua sel di bawahnya yang ingin Anda ekstrak angkanya, termasuk baris kosong.
   
4. Tekan Ctrl+E di Windows atau Cmd+E di Mac untuk memulai Flash Fill.
5. Sel-sel sekarang harus diisi dengan angka yang diekstrak.
   

Cara Mengekstrak Bilangan dari Mana Saja dalam String Menggunakan Rumus

Anda bisa menggunakan rumus untuk mengekstrak semua angka dari string, di mana pun angka itu muncul.

Untuk mengekstrak angka dari mana saja dalam string menggunakan Rumus:

1. Pilih sel tempat Anda ingin nomor yang diekstraksi muncul.
2. Masukkan rumus berikut, ganti 'B9' dengan sel yang berisi string Anda:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Rumus Anda harus mengekstrak semua angka dari string.
   
4. Jika Anda ingin setiap sel kosong mengembalikan sel kosong, bukan nol, gunakan rumus ini sebagai gantinya:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9)))),1))*ROW(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Kamu bisa gunakan IsiOtomatis untuk menerapkan rumus ke sel lain di spreadsheet Anda.
6. String apa pun yang tidak berisi angka apa pun akan menghasilkan nol. Anda dapat mempelajari caranya mengonversi nol menjadi tanda hubung di Excel jika Anda ingin mengubah ini.

Perluas Keterampilan Excel Anda

Mempelajari cara mengekstrak angka dari string di Excel dapat membantu Anda dengan cepat mengekstrak data yang Anda perlukan dari daftar string. Meskipun rumus yang diperlukan mungkin rumit, Anda tidak perlu memahami dengan tepat cara kerjanya; hanya itu yang mereka lakukan!

Ada lebih banyak trik Excel berguna yang dapat Anda pelajari untuk meningkatkan keterampilan Anda ke level berikutnya. Kamu bisa belajar cara menghitung sel dengan teks di dalamnya, atau bagaimana caranya menghitung usia seseorang dari tanggal lahir mereka. Anda bahkan bisa membuat generator nomor acak di Excel.