Công thức chung
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Tóm lược
Để tách văn bản bằng dấu phân cách và chuyển đổi kết quả thành một mảng, bạn có thể sử dụng hàm FILTERXML với sự trợ giúp từ các hàm SUBSTITUTE và TRANSPOSE. Trong ví dụ được hiển thị, công thức trong D5 là:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Lưu ý: FILTERXML không khả dụng trong Excel trên Mac hoặc trong Excel Online.
Lưu ý: Tôi đã học được mẹo này từ Bill Jelen trong một video MrExcel.
Giải trình
Excel không có một chức năng dành riêng cho việc tách văn bản thành một mảng, tương tự như chức năng bùng nổ trong PHP hoặc phương pháp tách trong Python. Để giải quyết vấn đề này, bạn có thể sử dụng hàm FILTERXML, sau khi thêm đánh dấu XML vào văn bản lần đầu tiên.
Trong ví dụ được hiển thị, chúng tôi có một số chuỗi văn bản được phân tách bằng dấu phẩy như sau:
"Jim,Brown,33,Seattle,WA"
Mục đích là chia thông tin thành các cột riêng biệt bằng cách sử dụng dấu phẩy làm dấu phân cách.
Nhiệm vụ đầu tiên là thêm đánh dấu XML vào văn bản này, để nó có thể được phân tích cú pháp dưới dạng XML bằng hàm FILTERXML. Chúng ta sẽ tùy ý biến mỗi trường trong văn bản thành một phần tử, kèm theo một phần tử cha. Chúng ta bắt đầu với hàm SUBSTITUTE ở đây:
SUBSTITUTE(B5,",","")
Kết quả từ SUBSTITUTE là một chuỗi văn bản như sau:
"JimBrown33SeattleWA"
Để đảm bảo các thẻ XML được định dạng tốt và để bọc tất cả các phần tử trong một phần tử mẹ, chúng tôi thêm trước và nối thêm các thẻ XML như sau:
""&SUBSTITUTE(B5,",","")&""
Điều này tạo ra một chuỗi văn bản như thế này (ngắt dòng được thêm vào để dễ đọc)
" Jim Brown 33 Seattle WA "
Văn bản này được gửi trực tiếp đến hàm FILTERXML dưới dạng đối số xml, với biểu thức Xpath là "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath là một ngôn ngữ phân tích cú pháp và "// y" chọn tất cả các phần tử. Kết quả từ FILTERXML là một mảng dọc như thế này:
("Jim";"Brown";33;"Seattle";"WA")
Bởi vì chúng tôi muốn một mảng ngang trong trường hợp này, chúng tôi bọc hàm TRANSPOSE xung quanh FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Kết quả là một mảng ngang như thế này:
("Jim","Brown",33,"Seattle","WA")
tràn vào phạm vi D5: H5 trong Excel 365.
