Chia sẻ
"Hướng dẫn 3 hàm xử lý text mới trong Excel 365: TEXTBEFORE lấy phần trước, TEXTAFTER lấy phần sau, TEXTSPLIT tách chuỗi thành mảng. Thay thế hoàn toàn LEFT/MID/RIGHT/FIND phức tạp."
Excel 365 bổ sung 3 hàm xử lý text cực mạnh: TEXTBEFORE, TEXTAFTER, TEXTSPLIT. Chúng thay thế hàng loạt công thức LEFT/MID/RIGHT/FIND lồng nhau phức tạp. Bài viết hướng dẫn chi tiết từng hàm, từ cơ bản đến nâng cao, kèm ví dụ thực tế.
TEXTBEFORE — Lấy Phần Text Trước Ký Tự Phân Cách
Cú pháp
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])Tham số | Ý nghĩa | Mặc định |
|---|---|---|
| Chuỗi gốc cần xử lý | Bắt buộc |
| Ký tự phân cách | Bắt buộc |
| Lần xuất hiện thứ mấy (âm = đếm từ cuối) | 1 |
| 0 = phân biệt hoa/thường, 1 = không | 0 |
| 0 = không khớp cuối chuỗi, 1 = có | 0 |
| Giá trị trả về nếu không tìm thấy delimiter | #N/A |
Ví dụ cơ bản
Dữ liệu A1: "Nguyễn Văn An - Kế toán"
=TEXTBEFORE(A1, " - ")
→ "Nguyễn Văn An"
=TEXTBEFORE("192.168.1.100", ".")
→ "192"
=TEXTBEFORE("192.168.1.100", ".", 3)
→ "192.168.1" (trước dấu chấm thứ 3)Đếm ngược với instance_num âm
Dữ liệu: "Hà Nội > Đà Nẵng > Hồ Chí Minh > Cần Thơ"
=TEXTBEFORE(A1, " > ", -1)
→ "Hà Nội > Đà Nẵng > Hồ Chí Minh" (trước > cuối cùng)
=TEXTBEFORE(A1, " > ", -2)
→ "Hà Nội > Đà Nẵng" (trước > thứ 2 từ cuối)Nhiều delimiter cùng lúc
Truyền mảng delimiter để khớp bất kỳ cái nào xuất hiện trước:
=TEXTBEFORE("abc-def/ghi", {"-", "/"})
→ "abc" (dấu - xuất hiện trước /)Xử lý khi không tìm thấy
=TEXTBEFORE("Nguyễn Văn An", "-", 1, 0, 0, "Không tìm thấy")
→ "Không tìm thấy"
// So với cách cũ phức tạp:
=IFERROR(LEFT(A1, FIND("-", A1)-1), "Không tìm thấy")TEXTAFTER — Lấy Phần Text Sau Ký Tự Phân Cách
Cú pháp hoàn toàn giống TEXTBEFORE, chỉ khác là trả về phần text SAU delimiter:
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])Ví dụ thực tế
Dữ liệu A1: "Nguyễn Văn An - Kế toán"
=TEXTAFTER(A1, " - ")
→ "Kế toán"
// Tách domain từ email
=TEXTAFTER("user@company.com", "@")
→ "company.com"
// Tách phần mở rộng file
=TEXTAFTER("report-2024.final.xlsx", ".", -1)
→ "xlsx" (sau dấu chấm cuối cùng)Kết hợp TEXTBEFORE + TEXTAFTER
Sức mạnh thực sự là khi dùng cả hai cùng nhau:
Dữ liệu: "INV-2024-00123-HCM"
// Tách năm (phần giữa dấu - thứ 1 và thứ 2)
=TEXTAFTER(TEXTBEFORE(A1, "-", 2), "-")
→ "2024"
// Tách mã khu vực (sau dấu - cuối)
=TEXTAFTER(A1, "-", -1)
→ "HCM"
// So sánh với cách cũ:
=MID(A1, FIND("-",A1)+1, FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)TEXTSPLIT — Tách Chuỗi Thành Mảng
Đây là hàm mạnh nhất trong bộ 3: tách chuỗi thành mảng theo cả hàng lẫn cột.
Cú pháp
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])Tham số | Ý nghĩa |
|---|---|
| Ký tự tách theo cột (ngang) |
| Ký tự tách theo hàng (dọc) |
| TRUE = bỏ qua giá trị rỗng |
| Giá trị lấp chỗ trống (mặc định #N/A) |
Tách theo cột
// Tách họ tên
=TEXTSPLIT("Nguyễn Văn An", " ")
→ Nguyễn | Văn | An (tràn sang 3 ô)
// Tách danh sách CSV
=TEXTSPLIT("Kế toán,Tài chính,Kiểm toán,Thuế", ",")
→ Kế toán | Tài chính | Kiểm toán | ThuếTách theo hàng
// Tách dữ liệu xuống dòng (dùng CHAR(10) cho line break)
=TEXTSPLIT(A1, , CHAR(10))
→ Mỗi dòng thành 1 hàng riêng
// Tách đường dẫn file
=TEXTSPLIT("C:\Users\trung\Documents\report.xlsx", , "\\")
→ C: | Users | trung | Documents | report.xlsxTách 2 chiều (ma trận)
Điểm đặc biệt của TEXTSPLIT — tách cả hàng và cột cùng lúc:
Dữ liệu: "An,90,85;Bình,78,92;Cường,88,91"
=TEXTSPLIT(A1, ",", ";")
→ Ma trận 3x3:
An | 90 | 85
Bình | 78 | 92
Cường | 88 | 91Nhiều delimiter
// Tách bằng nhiều ký tự khác nhau
=TEXTSPLIT("An-Bình,Cường;Dũng", {"-",",",";"})
→ An | Bình | Cường | Dũng
// Bỏ qua phần tử rỗng
=TEXTSPLIT("An,,Bình,,,Cường", ",", , TRUE)
→ An | Bình | Cường (bỏ qua các ô trống)So Sánh Với Cách Làm Cũ
Tác vụ | Cách cũ | Cách mới |
|---|---|---|
Tách tên trước @ |
|
|
Tách domain sau @ |
|
|
Tách CSV thành ô | Text to Columns / VBA |
|
Tách phần mở rộng | SUBSTITUTE + REPT + MID phức tạp |
|
Ứng Dụng Thực Tế Trong Kế Toán
1. Tách mã chứng từ
Chứng từ: "PC-2024-03-00156"
PC = Phiếu chi, 2024 = năm, 03 = tháng, 00156 = số
// Loại chứng từ
=TEXTBEFORE(A1, "-")
→ "PC"
// Số chứng từ
=TEXTAFTER(A1, "-", -1)
→ "00156"
// Năm-tháng
=TEXTAFTER(TEXTBEFORE(A1, "-", 3), "-")
→ "2024-03"2. Tách địa chỉ
Địa chỉ: "123 Nguyễn Huệ, Phường Bến Nghé, Quận 1, TP.HCM"
=TEXTSPLIT(A1, ", ")
→ 123 Nguyễn Huệ | Phường Bến Nghé | Quận 1 | TP.HCM
// Chỉ lấy quận
=INDEX(TEXTSPLIT(A1, ", "), 1, 3)
→ "Quận 1"3. Parse mã tài khoản kế toán
Tài khoản: "6411.01.001" (Chi phí bán hàng, tiểu khoản, chi tiết)
=TEXTSPLIT(A1, ".")
→ 6411 | 01 | 001
// Tài khoản cấp 1
=TEXTBEFORE(A1, ".")
→ "6411"Kỹ Thuật Nâng Cao
Kết hợp với LAMBDA
// Hàm tách tên file từ đường dẫn
=LAMBDA(path, TEXTAFTER(path, "\", -1))
// Áp dụng:
=TEXTAFTER("C:\Reports\2024\Q1.xlsx", "\", -1)
→ "Q1.xlsx"Kết hợp với MAP/REDUCE
// Tách và đảo ngược thứ tự tên
// "Nguyễn Văn An" → "An Văn Nguyễn"
=TEXTJOIN(" ", TRUE,
SORTBY(
TEXTSPLIT(A1, " "),
SEQUENCE(1, LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1),
-1
)
)Lưu Ý Quan Trọng
Chỉ có trên Excel 365 / Office 2024 — Excel 2021 trở xuống không hỗ trợ
TEXTSPLIT trả về Dynamic Array — cần đủ không gian cho ô tràn (Spill)
Case-sensitive mặc định — đặt match_mode=1 nếu muốn không phân biệt hoa/thường
Dùng if_not_found để tránh lỗi #N/A khi delimiter không tồn tại
Delimiter có thể là mảng — truyền {";",","} để khớp nhiều ký tự
Câu Hỏi Thường Gặp (FAQ)
TEXTBEFORE và LEFT khác nhau thế nào?
LEFT yêu cầu biết chính xác số ký tự cần lấy. TEXTBEFORE chỉ cần biết ký tự phân cách — linh hoạt hơn nhiều khi độ dài text thay đổi.
TEXTSPLIT có thay thế Text to Columns không?
Có, và tốt hơn: kết quả là Dynamic Array (tự cập nhật khi dữ liệu nguồn thay đổi), không ghi đè dữ liệu hiện có, và hỗ trợ nhiều delimiter cùng lúc.
Cách xử lý khi chuỗi không chứa delimiter?
Dùng tham số if_not_found để trả về giá trị thay thế. Ví dụ: =TEXTBEFORE(A1, "-", 1, 0, 0, A1) sẽ trả về toàn bộ chuỗi gốc nếu không có dấu "-".
Ba hàm này có hoạt động trên Google Sheets không?
Hiện tại (2024) Google Sheets chưa hỗ trợ TEXTBEFORE/TEXTAFTER. Tuy nhiên Google Sheets đã có TEXTSPLIT từ năm 2022 với cú pháp tương tự.
Tổng Kết
TEXTBEFORE— lấy phần trước delimiter, thay thế LEFT+FINDTEXTAFTER— lấy phần sau delimiter, thay thế MID+FINDTEXTSPLIT— tách chuỗi thành mảng 2D, thay thế Text to ColumnsCả 3 đều hỗ trợ: nhiều delimiter, instance_num âm, case-insensitive, fallback value
Kết hợp 3 hàm này giải quyết mọi bài toán tách/trích xuất text trong Excel
Bình luận
Đăng nhập để tham gia bình luận
Đăng nhậpNhận bài viết mới nhất
Đăng ký để nhận thông báo khi có bài viết mới. Không spam, chỉ kiến thức chất lượng.
Bài viết liên quan
Khám phá thêm các bài viết cùng chủ đề
