Ekstrak hanya angka dari teks (string)

Di Excel, Anda bisa menggunakan rumus untuk mengekstrak angka dari nilai dengan gabungan teks dan angka. Pada contoh berikut, kami menggunakan rumus untuk mendapatkan 1984 dari nilai “TPS1984”.

Rumus untuk mengekstrak angka dari teks

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

Dalam tutorial ini, kita akan memahami rumus ini secara detail. Dan untuk memahaminya kita harus membaginya menjadi beberapa bagian:

1. Pertama, kita memiliki fungsi LEN untuk mendapatkan jumlah karakter dalam suatu nilai.
   
2. Setelah itu, INDIRECT menggunakan referensi sel menggunakan 1 dan 7 (dikembalikan oleh LEN).
   
3. Kemudian fungsi ROW akan menggunakan INDIRECT dan mengembalikan array angka yang dimulai dengan 1 dan diakhiri dengan 7.
   
4. Sekarang MID akan menggunakan array ini dan mengembalikan yang baru dengan nilai sel yang Anda referensikan.
   
5. Selanjutnya, kita memiliki rumus perkalian sederhana untuk mengalikan array dengan 1. Dengan perkalian sederhana ini, Anda mendapatkan array baru yang akan menghasilkan #VALUE! Kesalahan nilai teks dan angka akan tetap ada.
   
6. Dari sini, fungsi IFERROR akan mengubah nilai error menjadi nilai kosong.
   
7. Dan pada akhirnya, TEXTJOIN akan menggabungkan nilai-nilai tersebut, dan Anda hanya akan mendapatkan angka pada hasilnya.
   

Rumus di atas hanya berfungsi untuk Excel versi 2019 ke atas. Ini karena kami menggunakan TEXTJOIN, yang tidak tersedia di versi sebelumnya.

Jika Anda menginginkan nilai untuk suatu bagian rumus, pilih bagian tersebut dan tekan tombol pintasan F9.

TEXTJOIN adalah fungsi volatil yang mengubah nilai saat Anda memperbarui sel di lembar kerja. Ini bisa membuat buku kerja Anda sedikit lambat.

Namun bagaimana jika saya menggunakan versi sebelumnya (2007, 2010, 2013, 2016)

Jika Anda menggunakan versi sebelumnya, Anda harus menggunakan rumus yang berbeda. Rumusnya seperti ini:

 =IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"")

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

Kedua rumus di atas berfungsi baik dengan jenis nilai yang berbeda. Saya mengujinya dengan cara berikut.

Di sel A1 di atas kita memiliki nilai kompleks yang terdiri dari teks, angka, dan simbol dan kita hanya memiliki angka di hasilnya.