Tiada fungsi terbina dalam Excel untuk mengekstrak nombor daripada rentetan dalam sel (dan sebaliknya - keluarkan bahagian berangka dan ekstrak bahagian teks daripada rentetan abjad angka).
Walau bagaimanapun, ini boleh dilakukan menggunakan fungsi Excel atau beberapa kod VBA mudah.
Izinkan saya memberitahu anda apa yang saya bicarakan.
Katakan anda mempunyai set data seperti di bawah dan anda ingin mengekstrak nombor daripada rentetan (seperti di bawah):
Kaedah yang anda pilih juga bergantung pada versi Excel yang anda gunakan:
- Untuk versi Excel sebelum 2016, anda perlu menggunakan formula yang lebih panjang sedikit
- Untuk Excel 2016 anda boleh menggunakan fungsi TEXTJOIN yang baru diperkenalkan
- Kaedah VBA tersedia untuk semua versi Excel
Contents [show]
Ekstrak nombor daripada rentetan dalam Excel (formula Excel 2016)
Formula ini hanya berfungsi dalam Excel 2016 kerana ia menggunakan fungsi TEXTJOIN yang baru diperkenalkan.
Selain itu, formula ini boleh mengekstrak nombor pada permulaan, akhir atau tengah rentetan teks.
Ambil perhatian bahawa formula TEXTJOIN yang diterangkan dalam bahagian ini meletakkan semua aksara angka bersama-sama.Contohnya, jika teks ialah "10 tiket ialah $200", hasilnya ialah 10200.
Katakan anda mempunyai set data seperti berikut dan anda ingin mengekstrak nombor daripada rentetan dalam setiap sel:
Formula di bawah akan memberi anda bahagian berangka rentetan dalam Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Ini ialah formula tatasusunan, jadi anda perlu menggunakan " Control + Shift + Enter " daripada menggunakan Enter.
Formula ini akan kembali kosong (rentetan kosong) jika tiada nombor dalam rentetan teks.
Bagaimanakah formula ini berfungsi?
Biar saya pecahkan formula ini dan cuba terangkan cara ia berfungsi:
- ROW(INDIRECT(“1:”&LEN(A2))) – Bahagian formula ini akan memberikan satu siri nombor bermula dari satu.Fungsi LEN dalam formula mengembalikan jumlah bilangan aksara dalam rentetan.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Bahagian formula ini akan mengembalikan tatasusunan #VALUE!Ralat atau nombor berasaskan rentetan.Semua aksara teks dalam rentetan menjadi #VALUE!Ralat dan semua nilai kekal seperti sedia ada.Ini berlaku apabila kita mendarabkan fungsi MID dengan 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Apabila menggunakan fungsi IFERROR, semua #VALUEs dialih keluar!Ralat, hanya nombor yang akan kekal.Output bahagian ini kelihatan seperti ini - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – Fungsi TEXTJOIN kini hanya menggabungkan aksara rentetan Masih ada (nombor sahaja) dan mengabaikan rentetan kosong.
Petua pro:Jika anda ingin menyemak output formula separa, pilih sel, tekan F2 untuk memasuki mod edit, pilih bahagian formula yang anda ingin keluarkan dan tekan F9.Anda akan melihat hasilnya serta-merta.Kemudian ingat untuk menekan Control + Z atau tekan Escape.Jangan tekan enter.
Anda juga boleh menggunakan logik yang sama untuk mengekstrak bahagian teks daripada rentetan alfanumerik.Berikut ialah formula untuk mendapatkan bahagian teks daripada rentetan:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Perubahan kecil kepada formula ini ialah fungsi IF digunakan untuk menyemak tatasusunan yang kami dapat daripada fungsi MID untuk ralat.Jika ia ralat, simpan nilai itu, jika tidak gantikannya dengan kosong.
Kemudian gunakan TEXTJOIN untuk menggabungkan semua aksara teks.
注意: Walaupun formula ini sah, ia menggunakan fungsi yang tidak menentu (fungsi TIDAK LANGSUNG).Ini bermakna jika anda menggunakannya dengan set data yang besar, ia mungkin mengambil sedikit masa untuk memberikan anda hasil.Adalah lebih baik untuk membuat sandaran sebelum menggunakan formula ini dalam Excel.
Ekstrak nombor daripada rentetan dalam Excel (untuk Excel 2013/2010/2007)
Jika anda mempunyai Excel 2013. 2010. atau 2007, anda tidak boleh menggunakan formula TEXTJOIN, jadi anda perlu menggunakan formula kompleks untuk melakukan ini.
Katakan anda mempunyai set data seperti di bawah dan anda ingin mengekstrak semua nombor dalam rentetan dalam setiap sel.
Formula di bawah akan melakukan ini:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Formula ini akan kembali kosong (rentetan kosong) jika tiada nombor dalam rentetan teks.
Walaupun ini adalah formula tatasusunan, andaTidak perluGunakan "Control-Shift-Enter" untuk menggunakannya.Input mudah berfungsi untuk formula ini.
Kredit untuk formula ini sangat mengagumkanForum Encik Excel.
Sekali lagi, formula ini akan mengekstrak semua nombor dalam rentetan tanpa mengira kedudukan.Contohnya, jika teks ialah "10 tiket ialah $200", hasilnya ialah 10200.
注意: Walaupun formula ini sah, ia menggunakan fungsi yang tidak menentu (fungsi TIDAK LANGSUNG).Ini bermakna jika anda menggunakannya dengan set data yang besar, ia mungkin mengambil sedikit masa untuk memberikan anda hasil.Adalah lebih baik untuk membuat sandaran sebelum menggunakan formula ini dalam Excel.
Asingkan teks dan nombor dalam Excel dengan VBA
Jika anda kerap perlu memisahkan teks dan nombor (atau mengekstrak nombor daripada teks), anda juga boleh menggunakan kaedah VBA.
Apa yang anda perlu lakukan ialah mencipta fungsi takrif pengguna tersuai (UDF) dalam Excel menggunakan kod VBA mudah, dan kemudian gunakan formula VBA itu dan bukannya formula yang panjang dan kompleks.
Biar saya tunjukkan kepada anda cara mencipta dua formula dalam VBA - satu untuk mengekstrak nombor dan satu untuk mengekstrak teks daripada rentetan.
Ekstrak nombor daripada rentetan dalam Excel (menggunakan VBA)
Dalam bahagian ini, saya akan menunjukkan kepada anda cara mencipta fungsi tersuai untuk mendapatkan hanya bahagian berangka daripada rentetan.
Berikut ialah kod VBA yang akan kami gunakan untuk mencipta fungsi tersuai ini:
Fungsi GetNumeric(CellRef As String) Malapkan StringLength Sebagai Integer StringLength = Len(CellRef) Untuk i = 1 Hingga StringLength Jika IsNumeric(Mid(CellRef, i, 1)) Kemudian Result = Result & Mid(CellRef, i, 1) Seterusnya i GetNumeric = Fungsi Tamat Hasil
Berikut ialah langkah-langkah untuk mencipta fungsi ini dan kemudian menggunakannya dalam lembaran kerja:
Anda kini boleh menggunakan fungsi GetText pada lembaran kerja.Memandangkan kami telah melakukan semua pengangkatan berat dalam kod itu sendiri, anda hanya perlu menggunakan formula =GetNumeric(A2).
Ini akan memberi anda bahagian berangka rentetan dengan serta-merta.
Ambil perhatian bahawa memandangkan buku kerja kini mengandungi kod VBA, anda perlu menyimpannya dengan sambungan .xls atau .xlsm.
Jika anda mesti menggunakan formula ini dengan kerap, anda juga boleh menyimpannya ke buku kerja makro peribadi anda.Ini akan membolehkan anda menggunakan formula tersuai ini dalam mana-mana buku kerja Excel yang anda gunakan.
Ekstrak teks daripada rentetan dalam Excel (menggunakan VBA)
Dalam bahagian ini, saya akan menunjukkan kepada anda cara mencipta fungsi tersuai untuk mendapatkan hanya bahagian teks daripada rentetan.
Berikut ialah kod VBA yang akan kami gunakan untuk mencipta fungsi tersuai ini:
Fungsi GetText(CellRef Sebagai Rentetan) Malapkan StringLength Sebagai Integer StringLength = Len(CellRef) Untuk i = 1 Kepada StringLength Jika Tidak (IsNumeric(Mid(CellRef, i, 1))) Kemudian Result = Result & Mid(CellRef, i, 1 ) Seterusnya i GetText = Fungsi Tamat Hasil
Berikut ialah langkah-langkah untuk mencipta fungsi ini dan kemudian menggunakannya dalam lembaran kerja:
Anda kini boleh menggunakan fungsi GetNumeric dalam lembaran kerja.Memandangkan kami telah melakukan semua pengangkatan berat dalam kod itu sendiri, anda hanya perlu menggunakan formula =GetText(A2).
Ini akan memberi anda bahagian berangka rentetan dengan serta-merta.
Ambil perhatian bahawa memandangkan buku kerja kini mengandungi kod VBA, anda perlu menyimpannya dengan sambungan .xls atau .xlsm.
Jika anda mesti menggunakan formula ini dengan kerap, anda juga boleh menyimpannya ke buku kerja makro peribadi anda.Ini akan membolehkan anda menggunakan formula tersuai ini dalam mana-mana buku kerja Excel yang anda gunakan.
