Cara membuang / memecah teks dan nombor dalam sel excel

Tutorial ini menunjukkan beberapa kaedah untuk memisahkan teks dan nombor dalam sel Excel, menggunakan kedua-dua fungsi terbina dalam dan fungsi VBA tersuai. Anda akan belajar bagaimana untuk mengekstrak nombor semasa mengeluarkan teks, mengasingkan teks semasa membuang nombor, dan akhirnya, memecah data ke dalam dua lajur yang berbeza.

Katakan data anda mempunyai lajur di mana teks dan nombor intermingled. Untuk data yang konsisten, fungsi mudah seperti LEFT , RIGHT , dan MID mungkin cukup. Walau bagaimanapun, data dunia sebenar sering tidak konsisten, dengan nombor yang muncul sebelum, selepas, atau dalam teks. Penyelesaian di bawah menangani kerumitan ini.

Mengeluarkan teks untuk menyimpan nombor (Excel 365, 2021, 2019):

Microsoft Excel 2019 memperkenalkan fungsi TEXTJOIN , yang memudahkan tugas ini. Formula di bawah leverage TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE , dan IFERROR untuk mencapai ini.

Untuk Excel 365 dan 2021:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Untuk Excel 365 - 2019:

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

(Dalam Excel 2019, ini memerlukan CTRL Shift Enter untuk entri formula array. Dalam array dinamik Excel, mudah masuk mudah.)

Formula ini melangkah melalui setiap watak, mendarab dengan 1 untuk menukar nombor; Watak bukan angka menjadi kesilapan, dikendalikan oleh IFERROR untuk meninggalkan rentetan kosong. TEXTJOIN kemudian menggabungkan bilangan yang tinggal.

Fungsi VBA tersuai (semua versi Excel):

Untuk versi Excel yang lebih lama atau pendekatan yang lebih mudah, fungsi VBA tersuai menawarkan penyelesaian yang diperkemas. Dua versi disediakan:

Kod VBA 1 (watak demi watak):

 Fungsi RemoveXt (str as string)
  Dim sres sebagai rentetan
  sres = ""
  Untuk i = 1 hingga len (str)
    Jika isnumeric (mid (str, i, 1)) maka sres = sres & mid (str, i, 1)
  Seterusnya saya
  Membuangetext = sres
Fungsi akhir

Kod VBA 2 (ungkapan biasa):

 Fungsi ReveText (str as string) sebagai rentetan
  Dengan createObject ("vbscript.regexp")
    .Global = benar
    .Pattern = "[^0-9]"
    MembuangText = .Replace (str, "")
  Berakhir dengan
Fungsi akhir

Gunakan fungsi ini dengan =RemoveText(A2) . Kod 2 (menggunakan ungkapan biasa) secara amnya melakukan lebih baik pada dataset yang besar.

Mengeluarkan nombor untuk menyimpan teks (Excel 365, 2021, 2019):

Formula yang sama boleh mengekstrak teks semasa mengeluarkan nombor. Sekali lagi, menggunakan TEXTJOIN , MID , ROW , INDIRECT , SEQUENCE , IFERROR , dan ISERROR :

Untuk Excel 365 dan 2021:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1),MID(A2,SEQUENCE(LEN(A2)),1),"")))

Untuk Excel 365 - 2019:

=TRIM(TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")))

Fungsi TRIM menghilangkan ruang utama/trailing.

Fungsi VBA tersuai untuk mengeluarkan nombor (semua versi Excel):

Sama dengan fungsi VBA sebelumnya, berikut adalah dua versi untuk mengeluarkan nombor dan menyimpan teks:

Kod VBA 1 (watak demi watak):

 Fungsi penyingkiran (str as string)
  Dim sres sebagai rentetan
  sres = ""
  Untuk i = 1 hingga len (str)
    Sekiranya tidak ada (pertengahan (str, i, 1)) maka sres = sres & pertengahan (str, i, 1)
  Seterusnya saya
  Penyingkiran = sres
Fungsi akhir

Kod VBA 2 (ungkapan biasa):

 Fungsi penyingkiran (str as string) sebagai rentetan
  Dengan createObject ("vbscript.regexp")
    .Global = benar
    .Pattern = "[0-9]"
    Penyingkiran = .replace (str, "")
  Berakhir dengan
Fungsi akhir

Gunakan ini dengan =TRIM(RemoveNumbers(A2)) .

Memisahkan teks dan nombor ke lajur berasingan:

Fungsi VBA tersuai tunggal boleh mengendalikan kedua -dua teks dan pengekstrakan nombor:

Kod VBA 1 (watak demi watak):

 Fungsi splittextNumbers (str as string, is_remove_text sebagai boolean) sebagai rentetan
  Dim Snum, Stext, Schar sebagai String
  snum = stext = ""
  Untuk i = 1 hingga len (str)
    schar = pertengahan (str, i, 1)
    Jika isnumeric (schar) maka snum = snum & schar else stext = stext & schar
  Seterusnya saya
  Jika is_remove_text kemudian spitextNumbers = snum lain splittextNumbers = stext
Fungsi akhir

Kod VBA 2 (ungkapan biasa):

 Fungsi splittextNumbers (str as string, is_remove_text sebagai boolean) sebagai rentetan
  Dengan createObject ("vbscript.regexp")
    .Global = benar
    Jika is_remove_text kemudian .pattern = "[^0-9]" else .pattern = "[0-9]"
    SplitTextNumbers = .Replace (str, "")
  Berakhir dengan
Fungsi akhir

Penggunaan =SplitTextNumbers(A2,TRUE) untuk menyimpan nombor, dan =TRIM(SplitTextNumbers(A2,FALSE)) untuk menyimpan teks.

Menggunakan alat khusus:

Pertimbangkan untuk menggunakan tambahan Excel khusus untuk penyingkiran watak jika anda lebih suka pendekatan yang lebih visual dan mesra pengguna. Tutorial menyebutkan "suite muktamad" tambahan dengan ciri "keluarkan aksara".

Panduan komprehensif ini menyediakan pelbagai penyelesaian yang memenuhi versi Excel yang berbeza dan keutamaan pengguna. Ingat untuk menyesuaikan rujukan sel seperti yang diperlukan untuk data khusus anda.

Atas ialah kandungan terperinci Cara membuang / memecah teks dan nombor dalam sel excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!