Formula generik
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Ringkasan
Untuk membuang aksara angka dari rentetan teks, anda boleh menggunakan formula berdasarkan fungsi TEXTJOIN. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Penjelasan
Excel tidak mempunyai cara untuk menghantar huruf dalam rentetan teks ke array langsung dalam formula. Sebagai penyelesaian, formula ini menggunakan fungsi MID, dengan bantuan fungsi ROW dan INDIRECT untuk mencapai hasil yang sama. Rumus dalam C5, disalin, adalah:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Ini kelihatan agak rumit tetapi intinya adalah kita membuat susunan semua watak dalam B5, dan menguji setiap watak untuk melihat apakah itu nombor. Sekiranya demikian, kami membuang nilainya dan menggantinya dengan rentetan kosong (""). Sekiranya tidak, kami menambahkan watak bukan angka ke larik "diproses". Akhirnya, kami menggunakan fungsi TEXTJOIN (baru di Excel 2019) untuk menggabungkan semua watak bersama-sama, mengabaikan nilai kosong.
Berfungsi dari dalam ke luar, fungsi MID digunakan untuk mengekstrak teks dalam B5, satu karakter pada satu masa. Kuncinya ialah coretan ROW dan INDIRECT di sini:
ROW(INDIRECT("1:100"))
yang membentangkan array yang mengandungi 100 nombor seperti ini:
(1,2,3,4,5,6,7,8… .99,100)
Catatan: 100 mewakili aksara maksimum untuk diproses. Ubah agar sesuai dengan data anda, atau gunakan fungsi LEN seperti yang dijelaskan di bawah.
Susunan ini masuk ke fungsi MID sebagai argumen start_num . Untuk num_chars , kami menggunakan 1.
Fungsi MID mengembalikan array seperti ini:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Catatan: item tambahan dalam larik dikeluarkan untuk dibaca.
Untuk tatasusunan ini, kami menambah sifar. Ini adalah muslihat mudah yang memaksa Excel untuk memaksa teks ke nombor. Nilai teks angka seperti "1", "2", "3", "4" dan lain-lain ditukar tanpa ralat, tetapi nilai bukan angka akan gagal dan membuang kesalahan # NILAI. Kami menggunakan fungsi IF dengan fungsi ISERR untuk menangkap kesalahan ini. Apabila kita melihat ralat, kita tahu bahawa kita mempunyai watak bukan angka, jadi kita memasukkan watak itu ke dalam array yang diproses dengan fungsi MID lain:
MID(B5,ROW(INDIRECT("1:100")),1)
Sekiranya tidak mendapat ralat, kami tahu kami mempunyai nombor, jadi kami memasukkan rentetan kosong ("") ke dalam array sebagai ganti nombor tersebut.
Hasil array akhir masuk ke fungsi TEXTJOIN sebagai argumen text1. Untuk pembatas, kami menggunakan rentetan kosong ("") dan untuk ign_empty kami membekalkan BENAR. TEXTJOIN kemudian menggabungkan semua nilai bukan kosong dalam array dan mengembalikan hasilnya.
Panjang susunan tepat
Daripada memasukkan nombor seperti 100 menjadi LANGSUNG, anda boleh menggunakan fungsi LEN untuk membina susunan dengan bilangan aksara sebenar dalam sel seperti ini:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN mengembalikan jumlah aksara dalam sel sebagai angka, yang digunakan sebagai ganti 100. Ini membolehkan formula untuk meningkatkan jumlah watak secara automatik.
Mengeluarkan ruang tambahan
Apabila anda melucutkan aksara angka, anda mungkin mempunyai baki ruang tambahan. Untuk melucutkan ruang depan dan belakang, dan menormalkan ruang antara kata, anda boleh membungkus formula yang ditunjukkan di halaman ini di dalam fungsi TRIM:
=TRIM(formula)
Dengan KEBERSIHAN
Dalam Excel 365, fungsi SEQUENCE baru dapat menggantikan kod 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 membina susunan panjang yang betul dalam satu langkah.
Dengan LET
Kita dapat memperkemaskan formula ini dengan fungsi LET. Oleh kerana array dibuat dua kali di atas dengan SEQUENCE dan LEN, kita dapat menentukan array sebagai pemboleh ubah, dan membuatnya hanya sekali:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Di sini nilai array ditetapkan hanya sekali, kemudian digunakan dua kali di dalam fungsi MID.
