Formula generik
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Ringkasan
Untuk memisahkan teks dengan pembatas dan mengubah hasilnya menjadi larik, anda boleh menggunakan fungsi FILTERXML dengan bantuan fungsi SUBSTITUTE dan TRANSPOSE. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Catatan: FILTERXML tidak terdapat di Excel pada Mac, atau di Excel Online.
Catatan: Saya belajar helah ini dari Bill Jelen dalam video MrExcel.
Penjelasan
Excel tidak mempunyai fungsi yang didedikasikan untuk memisahkan teks ke array, serupa dengan fungsi meletup PHP, atau kaedah perpecahan Python. Sebagai penyelesaian, anda boleh menggunakan fungsi FILTERXML, setelah pertama kali menambahkan markup XML ke teks.
Dalam contoh yang ditunjukkan, kami mempunyai beberapa rentetan teks yang dipisahkan koma seperti ini:
"Jim,Brown,33,Seattle,WA"
Tujuannya adalah untuk memisahkan maklumat menjadi lajur terpisah menggunakan koma sebagai pembatas.
Tugas pertama adalah menambahkan markup XML ke teks ini, sehingga dapat diurai sebagai XML dengan fungsi FILTERXML. Kami akan dengan sewenang-wenang menjadikan setiap bidang dalam teks sebagai elemen, disertakan dengan elemen induk. Kita mulakan dengan fungsi SUBSTITUTE di sini:
SUBSTITUTE(B5,",","")
Hasil dari SUBSTITUTE adalah rentetan teks seperti ini:
"JimBrown33SeattleWA"
Untuk memastikan tag XML yang terbentuk dengan baik dan membungkus semua elemen dalam elemen induk, kami menyiapkan dan menambahkan lebih banyak tag XML seperti ini:
""&SUBSTITUTE(B5,",","")&""
Ini menghasilkan rentetan teks seperti ini (jeda baris ditambahkan untuk dibaca)
" Jim Brown 33 Seattle WA "
Teks ini dihantar terus ke fungsi FILTERXML sebagai argumen xml, dengan ungkapan Xpath "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath adalah bahasa penghuraian dan "// y" memilih semua elemen. Hasil dari FILTERXML adalah susunan menegak seperti ini:
("Jim";"Brown";33;"Seattle";"WA")
Kerana kami mahukan susunan mendatar dalam contoh ini, kami membungkus fungsi TRANSPOSE di sekitar FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Hasilnya ialah susunan mendatar seperti ini:
("Jim","Brown",33,"Seattle","WA")
yang meluas ke julat D5: H5 di Excel 365.
