Formula umum
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Ringkasan
Untuk menghapus karakter numerik dari string teks, Anda dapat menggunakan rumus yang didasarkan pada fungsi TEXTJOIN. Pada contoh yang ditunjukkan, rumus di C5 adalah:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Penjelasan
Excel tidak memiliki cara untuk mentransmisikan huruf dalam string teks ke array secara langsung dalam rumus. Sebagai solusinya, rumus ini menggunakan fungsi MID, dengan bantuan dari fungsi ROW dan INDIRECT untuk mencapai hasil yang sama. Rumus di C5, disalin, adalah:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Ini terlihat cukup rumit tetapi intinya adalah kita membuat array dari semua karakter di B5, dan menguji setiap karakter untuk melihat apakah itu angka. Jika demikian, kami membuang nilainya dan menggantinya dengan string kosong (""). Jika tidak, kami menambahkan karakter non-numerik ke array "diproses". Terakhir, kami menggunakan fungsi TEXTJOIN (baru di Excel 2019) untuk menggabungkan semua karakter menjadi satu, mengabaikan nilai kosong.
Bekerja dari dalam ke luar, fungsi MID digunakan untuk mengekstrak teks dalam B5, satu karakter dalam satu waktu. Kuncinya adalah cuplikan BARIS dan TIDAK LANGSUNG di sini:
ROW(INDIRECT("1:100"))
yang memutar array yang berisi 100 angka seperti ini:
(1,2,3,4,5,6,7,8… .99,100)
Catatan: 100 mewakili karakter maksimum untuk diproses. Ubah agar sesuai dengan data Anda, atau gunakan fungsi LEN seperti yang dijelaskan di bawah ini.
Array ini masuk ke fungsi MID sebagai argumen start_num . Untuk num_chars , kami menggunakan 1.
Fungsi MID mengembalikan larik seperti ini:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Catatan: item tambahan dalam larik dihapus agar mudah dibaca.
Untuk array ini, kami menambahkan nol. Ini adalah trik sederhana yang memaksa Excel untuk memaksa teks menjadi angka. Nilai teks numerik seperti "1", "2", "3", "4" dll. Dikonversi tanpa kesalahan, tetapi nilai non-numerik akan gagal dan memunculkan kesalahan #VALUE. Kami menggunakan fungsi IF dengan fungsi ISERR untuk menangkap kesalahan ini. Ketika kami melihat kesalahan, kami tahu kami memiliki karakter non-numerik, jadi kami membawa karakter itu ke dalam larik yang diproses dengan fungsi MID lainnya:
MID(B5,ROW(INDIRECT("1:100")),1)
Jika tidak mendapatkan kesalahan, kami tahu kami memiliki nomor, jadi kami memasukkan string kosong ("") ke dalam array sebagai pengganti nomor tersebut.
Hasil larik terakhir masuk ke fungsi TEXTJOIN sebagai argumen text1. Untuk pembatas, kami menggunakan string kosong ("") dan untuk ignore_empty kami menyediakan TRUE. TEXTJOIN kemudian menggabungkan semua nilai yang tidak kosong dalam larik dan mengembalikan hasilnya.
Panjang array yang tepat
Alih-alih meng-hardcode angka seperti 100 menjadi TIDAK LANGSUNG, Anda dapat menggunakan fungsi LEN untuk membuat larik dengan jumlah karakter aktual dalam sel seperti ini:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN mengembalikan jumlah karakter dalam sel sebagai angka, yang digunakan sebagai pengganti 100. Hal ini memungkinkan rumus untuk meningkatkan skala ke sejumlah karakter secara otomatis.
Menghapus ruang ekstra
Saat Anda menghapus karakter numerik, Anda mungkin memiliki karakter spasi ekstra yang tersisa. Untuk menghapus spasi di depan dan di belakang, dan menormalkan spasi di antara kata, Anda dapat membungkus rumus yang diperlihatkan di halaman ini di dalam fungsi TRIM:
=TRIM(formula)
Dengan URUTAN
Di Excel 365, fungsi SEQUENCE baru bisa menggantikan kode ROW + INDIRECT di atas:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Di sini, kami menggunakan SEQUENCE + LEN untuk membangun larik dengan panjang yang benar dalam satu langkah.
Dengan LET
Kami selanjutnya dapat merampingkan rumus ini dengan fungsi LET. Karena array dibuat dua kali di atas dengan SEQUENCE dan LEN, kita dapat mendefinisikan array sebagai variabel, dan membuatnya hanya sekali:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Disini nilai array diset hanya sekali, kemudian digunakan dua kali di dalam fungsi MID.
