Formula umum
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Ringkasan
Untuk memisahkan teks dengan pembatas dan mengubah hasilnya menjadi larik, Anda dapat menggunakan fungsi FILTERXML dengan bantuan dari fungsi SUBSTITUTE dan TRANSPOSE. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Catatan: FILTERXML tidak tersedia di Excel di Mac, atau di Excel Online.
Catatan: Saya mempelajari trik ini dari Bill Jelen dalam video MrExcel.
Penjelasan
Excel tidak memiliki fungsi yang didedikasikan untuk memisahkan teks ke array, mirip dengan fungsi ledakan PHP, atau metode pemisahan Python. Sebagai solusinya, Anda bisa menggunakan fungsi FILTERXML, setelah terlebih dahulu menambahkan markup XML ke teks.
Dalam contoh yang ditunjukkan, kami memiliki beberapa string teks yang dipisahkan koma seperti ini:
"Jim,Brown,33,Seattle,WA"
Tujuannya adalah untuk membagi informasi menjadi kolom terpisah menggunakan koma sebagai pembatas.
Tugas pertama adalah menambahkan markup XML ke teks ini, sehingga dapat diurai sebagai XML dengan fungsi FILTERXML. Kami akan membuat setiap bidang dalam teks menjadi elemen, diapit dengan elemen induk. Kami mulai dengan fungsi SUBSTITUTE di sini:
SUBSTITUTE(B5,",","")
Hasil dari SUBSTITUTE adalah string teks seperti ini:
"JimBrown33SeattleWA"
Untuk memastikan tag XML yang dibentuk dengan baik dan untuk membungkus semua elemen dalam elemen induk, kami menambahkan dan menambahkan lebih banyak tag XML seperti ini:
""&SUBSTITUTE(B5,",","")&""
Ini menghasilkan string teks seperti ini (jeda baris ditambahkan agar terbaca)
" Jim Brown 33 Seattle WA "
Teks ini dikirim langsung ke fungsi FILTERXML sebagai argumen xml, dengan ekspresi Xpath "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath adalah bahasa parsing dan "// y" memilih semua elemen. Hasil dari FILTERXML berupa larik vertikal seperti ini:
("Jim";"Brown";33;"Seattle";"WA")
Karena kami menginginkan array horizontal dalam contoh ini, kami membungkus fungsi TRANSPOSE di sekitar FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Hasilnya adalah larik horizontal seperti ini:
("Jim","Brown",33,"Seattle","WA")
yang tumpah ke kisaran D5: H5 di Excel 365.
