Excel

Tanggalkan aksara angka dari sel

Strip Numeric Characters From Cell

Rumus Excel: Keluarkan aksara angka dari selFormula 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 susunan secara 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 ialah kita membuat susunan dari semua watak dalam B5, dan uji setiap watak untuk melihat apakah itu nombor. Sekiranya demikian, kami membuang nilainya dan menggantinya dengan tali kosong (''). Sekiranya tidak, kami menambah watak bukan angka ke array 'diproses'. Akhirnya, kami menggunakan fungsi TEXTJOIN (baru di Excel 2019) untuk menggabungkan semua watak bersama-sama, mengabaikan nilai kosong.

cemerlang mendapat nombor pertandingan

Bekerja dari dalam ke luar, Fungsi MID digunakan untuk mengekstrak teks dalam B5, satu watak pada satu masa.Kuncinya adalah ROW dan LANGSUNG coretan 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 permulaan_num hujah. Untuk bilangan_chars , kami menggunakan 1.

cara meletakkan root square di excel

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 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 JIKA berfungsi dengan Fungsi ISERR untuk menangkap kesilapan ini. Apabila kita melihat ralat, kita tahu bahawa kita mempunyai watak bukan angka, jadi kita memasukkan watak tersebut ke dalam proses susunan dengan fungsi MID lain:

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

Sekiranya jangan 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 teks1 hujah. Untuk pembatas , kami menggunakan tali kosong ('') dan untuk abaikan_pendam kami membekalkan BENAR. TEXTJOIN ketika itu berganding semua nilai bukan kosong dalam array dan mengembalikan hasilnya.

Panjang susunan tepat

Daripada menggunakan nombor keras seperti nombor 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 :

menerapkan pemformatan bersyarat ke sel yang dipilih
 
= TRIM (formula)

Dengan KEBERSIHAN

Dalam Excel 365 , baru Fungsi SEQUENCE boleh 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 susunan diatur hanya sekali, kemudian digunakan dua kali di dalam fungsi MID.

Pengarang Dave Bruns


^