Đánh giá lequocthai.com:
Cú pháp
Cú pháp của hàm TEXTSPLIT như sau.
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])Đối số
Hàm TEXTSPLIT có các đối số sau, trong đó chỉ có hai là bắt buộc.
text – Đối số bắt buộc, nhận giá trị văn bản cần tách. Có thể là chuỗi ký tự trong dấu ngoặc kép hoặc là tham chiếu tới ô chứa văn bản.
col_delimiter – Đối số bắt buộc, là ký tự, chuỗi hoặc dấu cách dùng để tách văn bản thành các cột. Văn bản sẽ được chia ra các cột tại vị trí của col_delimiter.
row_delimiter – Đối số tùy chọn, nhận ký tự, chuỗi hoặc dấu cách dùng để tách văn bản thành các hàng. Cả col_delimiter và row_delimiter đều có thể truyền dưới dạng mảng trong ngoặc nhọn nếu muốn dùng nhiều dấu phân tách.
ignore_empty – Đối số tùy chọn, nhận giá trị TRUE hoặc FALSE (cũng có thể dùng 1 và 0). Khi đặt TRUE hoặc 1, hàm sẽ bỏ qua các dấu phân tách liên tiếp, tức là không tạo ra ô trống.
match_mode – Đối số tùy chọn, nhận 0 hoặc 1. Mặc định là 0 (phân biệt chữ hoa và chữ thường). Đặt thành 1 sẽ khiến hàm thực hiện so sánh không phân biệt chữ hoa/chữ thường.
pad_with – Đối số tùy chọn, mặc định là #N/A. Nếu trong mảng kết quả có ô bị thiếu, ô trống sẽ được thay thế bằng giá trị của pad_with.
Đặc điểm quan trọng của hàm TEXTSPLIT
- Vì thuộc nhóm hàm xử lý văn bản, các giá trị trả về luôn ở định dạng text.
- Hàm sẽ trả về lỗi #VALUE! nếu đối số text để trống.
- Cần ít nhất một đối số phân tách, nếu không sẽ nhận lỗi #VALUE!.
- Nếu mảng kết quả không có đủ không gian để rải, Excel sẽ trả về lỗi #SPILL!.
- Khi match_mode = 1, văn bản trả về luôn được chuyển thành chữ thường.
Ví dụ về hàm TEXTSPLIT
Vì hàm TEXTSPLIT có nhiều đối số, việc nắm rõ cách sử dụng từng đối số là rất quan trọng. Dưới đây là một loạt ví dụ minh họa cho từng đối số.
Ví dụ 1 – Cơ bản
Trong ví dụ này, chúng ta dùng các bộ dữ liệu khác nhau để hiểu cách sử dụng hàm TEXTSPLIT.
- Ở ví dụ đầu tiên, các ngày trong tuần được tách bằng dấu phẩy (dấu phân tách) trong ô B3. Dùng dấu phẩy làm col_delimiter, văn bản sẽ được chia thành các cột riêng, kết quả xuất hiện từ ô C3 tới G3.
- Ở ví dụ tiếp theo, chúng ta muốn tách văn bản thành các hàng, vì vậy dùng row_delimiter và để col_delimiter trống; kết quả hiển thị từ ô C5 tới C8. Lưu ý rằng dù dữ liệu gốc là số, giá trị trả về vẫn ở dạng text (được thể hiện bằng căn lề trái).
- Tiếp theo, chúng ta sẽ tách đồng thời thành cả cột và hàng, tạo một mảng hai chiều. Dùng dấu gạch ngang (-) làm col_delimiter và dấu gạch chéo (/) làm row_delimiter; kết quả xuất hiện trong vùng C10:E11.
- Hai ví dụ sau minh họa cách sử dụng ignore_empty. Khi không chỉ định, mặc định là FALSE, vì vậy ô trống sẽ xuất hiện trong kết quả (ví dụ 4). Khi đặt ignore_empty = TRUE, các ô trống sẽ bị bỏ qua.
Ví dụ 1.1 – Tách dữ liệu thành cột
Ở ví dụ này, chúng ta có danh sách các vận động viên Olympic, quốc gia và môn thể thao. Mỗi phần thông tin được ngăn cách bằng dấu phẩy, vì vậy chúng ta dùng dấu phẩy làm col_delimiter.
=TEXTSPLIT(B3,",")
Dữ liệu được tách thành các cột, nhưng cột D và E còn dư khoảng trắng vì sau dấu phẩy có một dấu cách. Để loại bỏ khoảng trắng thừa, chúng ta có thể dùng hàm TRIM kết hợp với TEXTSPLIT:
=TRIM(TEXTSPLIT(B3,","))
Hoặc dùng chuỗi “, ” (dấu phẩy + dấu cách) làm col_delimiter:
=TEXTSPLIT(B3,", ")
Kết quả đã sạch sẽ và sẵn sàng cho các bước xử lý tiếp theo.
Ví dụ 2 – Tạo mảng 2‑D từ dữ liệu nhân viên
Dữ liệu nhân viên được đưa vào một chuỗi, cần sắp xếp thành bảng để tạo thẻ ID. Dùng dấu gạch ngang (-) làm col_delimiter, dấu phẩy + dấu cách làm row_delimiter:
=TEXTSPLIT(B3,"-",",")
Nếu một số thông tin thiếu (ví dụ ID hoặc vị trí văn phòng), hàm sẽ trả về lỗi #N/A. Để thay thế lỗi bằng thông báo tùy chỉnh, chúng ta dùng đối số pad_with:
=TEXTSPLIT(B3,"-",", ",,,"Data Missing")
Bây giờ dù có dữ liệu thiếu, bảng vẫn đầy đủ và hiển thị thông báo “Data Missing”.
Ví dụ 3 – Tách họ và tên
Với danh sách tên, dùng dấu cách làm col_delimiter:
=TEXTSPLIT(B3," ")
Đối với các tiêu đề như “Mr.”, “Mrs.”, “Dr.”, chúng ta có thể đưa chúng vào mảng phân tách:
=TEXTSPLIT(B3,{" ","Dr. ","Mrs. ","Mr. "})
Sau đó bật ignore_empty = TRUE để bỏ qua các ô trống do dấu phân tách liên tiếp:
=TEXTSPLIT(B3,{" ","Dr. ","Mrs. ","Mr. "},,TRUE)
Kết quả đã sạch sẽ.
Ví dụ 4 – Tách ngày tháng
Lịch nghỉ năm 2023 có dạng ngày‑tháng‑năm (dd‑mm‑yyyy). Dùng dấu gạch ngang (-) làm col_delimiter:
=TEXTSPLIT(B3,"-")
Kết quả trả về là số sê-ri ngày vì định dạng ngày đã bị mất. Để giữ nguyên định dạng, chuyển ngày sang chuỗi bằng hàm TEXT trước khi tách:
=TEXTSPLIT(TEXT(B3,"dd-mmm-yyyy"),"-")
Ví dụ 5 – Hiểu đối số “match_mode”
Dữ liệu kích thước khu vực có dạng “12FT x 8ft”. Dùng “x” làm col_delimiter:
=TEXTSPLIT(B3,"x")
Vì dữ liệu có cả chữ hoa và chữ thường, để không phân biệt, đặt match_mode = 1:
=TEXTSPLIT(B3,"x",,,1)
Kết quả luôn ở dạng chữ thường.
Ví dụ 6 – Lấy từ thứ n trong câu
Muốn lấy từ thứ ba trong mỗi câu trích dẫn, dùng TEXTSPLIT tách bằng dấu cách rồi kết hợp với hàm INDEX:
=INDEX(TEXTSPLIT(B3," "),3)
Kết quả là từ thứ ba của mỗi câu.
TEXTSPLIT là hàm mới, chỉ có trong Excel 365. Hãy tự thực hành để khám phá thêm nhiều ứng dụng thú vị khác.
