Công thức chung
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Tóm lược
Để chia văn bản tại một dấu phân cách tùy ý (dấu phẩy, dấu cách, dấu gạch ngang, v.v.), bạn có thể sử dụng công thức dựa trên các hàm TRIM, MID, SUBSTITUTE, REPT và LEN. Trong ví dụ được hiển thị, công thức trong C5 là:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Lưu ý: các tham chiếu đến B5 và C4 là các tham chiếu hỗn hợp để cho phép sao chép công thức qua lại.
Giải trình
Ý chính của công thức này là thay thế một dấu phân cách đã cho bằng một số lượng lớn khoảng trắng bằng cách sử dụng SUBSTITUTE và REPT, sau đó sử dụng hàm MID để trích xuất văn bản liên quan đến "lần xuất hiện thứ n" và hàm TRIM để loại bỏ khoảng trống thừa.
Trong đoạn mã này, dấu phân tách (delim) được thay thế bằng một số khoảng trắng bằng tổng độ dài của chuỗi:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Sau đó, công thức sử dụng hàm MID để trích xuất chuỗi con thứ n. Điểm bắt đầu được tính bằng mã bên dưới, trong đó N đại diện cho "thứ n":
(N-1)*LEN(A1)+1
Tổng số ký tự được trích xuất bằng độ dài của chuỗi văn bản đầy đủ. Sau đó, hàm TRIM loại bỏ tất cả các khoảng trắng thừa và chỉ trả về chuỗi thứ n.
Chỉ trích xuất một phiên bản
Mặc dù ví dụ được thiết lập để trích xuất 5 chuỗi con từ văn bản trong cột B, bạn có thể dễ dàng trích xuất chỉ 1 phiên bản. Ví dụ: để chỉ trích xuất mục thứ 4 (thành phố), bạn có thể sử dụng:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Tính năng Text to Columns
Đối với chuyển đổi thủ công, một lần, Excel có một tính năng tích hợp được gọi là "Văn bản thành Cột" có thể tách văn bản trong các ô bằng dấu phân cách bạn chọn. Bạn sẽ tìm thấy tính năng này trên tab Dữ liệu của dải băng trong phần Công cụ dữ liệu.
