Tách họ tên hoặc địa chỉ bằng LEFT, MID và FIND thường cần lồng nhiều hàm trong một công thức. Bài viết này hướng dẫn bạn dùng 3 hàm TEXTBEFORE, TEXTAFTER, TEXTSPLIT trong Excel để làm cùng việc đó bằng công thức ngắn hơn.
Cả 3 hàm đều cắt chuỗi chữ tại một ký tự bạn chọn. Ký tự đó gọi là ký tự phân cách, ví dụ dấu cách, dấu gạch, dấu phẩy hoặc cả một cụm chữ.
Hàm TEXTBEFORE lấy phần đứng trước ký tự phân cách. Ví dụ, với "Nguyễn Văn An" và ký tự phân cách là dấu cách, hàm lấy phần đứng trước dấu cách đầu tiên và trả về họ "Nguyễn".
Hàm TEXTAFTER lấy phần đứng sau ký tự phân cách. Ví dụ, với "Nguyễn Văn An" và ký tự phân cách là dấu cách, hàm lấy phần đứng sau dấu cách cuối cùng và trả về tên "An".
Hàm TEXTSPLIT cắt chuỗi tại mọi ký tự phân cách. Ví dụ, với "Số 15 Lý Thường Kiệt, Hoàn Kiếm, Hà Nội" và ký tự phân cách là dấu phẩy kèm một dấu cách, hàm tách chuỗi thành 3 phần: "Số 15 Lý Thường Kiệt", "Hoàn Kiếm" và "Hà Nội".
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Mỗi bảng ví dụ nằm ở một sheet riêng và bắt đầu từ ô A1, nên bạn tạo sheet mới cho mỗi bảng để công thức không ghi đè lên bảng trước. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =TEXTBEFORE(A2;" ").
Excel nào có 3 hàm này?
Ba hàm này chỉ có trên Microsoft 365 (Windows và Mac) và Excel 2024 (Windows và Mac). Excel 2021, 2019, 2016 không có các hàm này. Nếu bạn gõ công thức trên các bản đó, ô sẽ báo lỗi #NAME?.
Hàm TEXTBEFORE dùng như thế nào?
Hàm TEXTBEFORE có cú pháp thế nào?
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Tham số | Ý nghĩa |
|---|---|
text | Tham số này là ô chứa chuỗi cần lấy. |
delimiter | Tham số này là ký tự phân cách, viết trong ngoặc kép. |
instance_num | Tham số này cho biết hàm cắt ở ký tự phân cách thứ mấy. Bạn bỏ trống thì hàm hiểu là 1, tức ký tự phân cách đầu tiên. Số âm nghĩa là hàm đếm từ cuối chuỗi, ví dụ -1 là ký tự phân cách cuối cùng. |
match_mode | Tham số này bằng 0 thì hàm phân biệt chữ hoa, chữ thường. Tham số bằng 1 thì hàm không phân biệt. Bạn bỏ trống thì hàm hiểu là 0. |
match_end | Tham số này bằng 1 thì hàm coi cuối chuỗi như một ký tự phân cách. Bạn bỏ trống thì hàm hiểu là 0. |
if_not_found | Tham số này là giá trị hàm trả về khi không tìm thấy ký tự phân cách. Bạn bỏ trống thì hàm báo lỗi |
Bạn chỉ bắt buộc điền 2 tham số đầu. Bốn tham số sau nằm trong ngoặc vuông nên bạn bỏ trống được.
Ví dụ 1: Bạn lấy họ từ họ tên
Bảng ví dụ có họ tên ở cột A, từ ô A2 đến A5. Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:
=TEXTBEFORE(A2," ")

Hàm tìm dấu cách đầu tiên trong "Nguyễn Văn An" và lấy phần đứng trước nó, nên kết quả là "Nguyễn".
Tiếp theo, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô B2. Khi con trỏ thành dấu cộng đen mảnh, bạn kéo xuống ô B5.

Ví dụ 2: Bạn lấy họ và tên đệm bằng instance_num -1
Bạn muốn lấy mọi chữ trừ tên cuối, ví dụ "Trần Thị Thu" từ "Trần Thị Thu Hà". Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:
=TEXTBEFORE(A2," ",-1)

Số -1 làm hàm đếm dấu cách từ cuối chuỗi. Hàm cắt ở dấu cách cuối cùng và lấy toàn bộ phần đứng trước nó. Sau đó, bạn kéo công thức từ ô C2 xuống ô C5.

Ví dụ 3: Bạn lấy mã chi nhánh từ mã đơn
Bảng thứ hai nằm ở sheet Mã đơn, có mã đơn dạng "HN-2026-0158" ở cột A. Phần trước dấu gạch đầu tiên là mã chi nhánh. Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:
=TEXTBEFORE(A2,"-")

Sau đó, bạn kéo công thức từ ô B2 xuống ô B4. Cột B trả về HN, HCM và DN. Mã chi nhánh dài 2 hay 3 ký tự thì công thức vẫn lấy đúng, vì hàm cắt theo dấu gạch chứ không đếm số ký tự.
Hàm TEXTAFTER dùng như thế nào?
Hàm TEXTAFTER có cú pháp thế nào?
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Hàm TEXTAFTER có đúng 6 tham số như TEXTBEFORE và mỗi tham số có cùng ý nghĩa. Điểm khác duy nhất là hàm TEXTAFTER lấy phần đứng sau ký tự phân cách.
Ví dụ 4: Bạn lấy tên từ họ tên
Bạn quay lại sheet Họ tên. Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:
=TEXTAFTER(A2," ",-1)

Số -1 làm hàm cắt ở dấu cách cuối cùng, nên kết quả là chữ cuối của họ tên. Sau đó, bạn kéo công thức từ ô D2 xuống ô D5.

Ví dụ 5: Bạn lấy số thứ tự từ mã đơn
Bạn quay lại sheet Mã đơn. Phần đứng sau dấu gạch thứ hai là số thứ tự của đơn. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:
=TEXTAFTER(A2,"-",2)

Sau đó, bạn kéo công thức từ ô C2 xuống ô C4.

Trong mã đơn này, =TEXTAFTER(A2,"-",-1) cũng trả về "0158", vì dấu gạch thứ hai cũng là dấu gạch cuối cùng.
Ví dụ 6: Bạn ghép 2 hàm để lấy tên đệm
Tên đệm nằm giữa chữ đầu và chữ cuối. Bạn lấy tên đệm theo 2 bước:
Hàm
TEXTAFTER(A2," ")bỏ họ và trả về "Văn An".Hàm TEXTBEFORE với -1 bỏ tên cuối của "Văn An" và trả về "Văn".
Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:
=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1)

Sau đó, bạn kéo công thức từ ô E2 xuống ô E5.

Ô E4 báo lỗi #N/A vì sau khi bỏ họ, ô chỉ còn "Hoa" và không còn dấu cách nào để cắt. Bạn dùng tham số thứ 6 là if_not_found để thay lỗi bằng ô trống. Bạn chọn lại ô E2, gõ công thức sau rồi nhấn Enter:
=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,"")

Sau đó, bạn kéo lại công thức từ ô E2 xuống ô E5.

Tham số if_not_found chỉ xử lý trường hợp như "Lê Hoa". Nếu cột họ tên có người chỉ có một chữ, ví dụ "Hoa", hoặc có ô trống, hàm TEXTAFTER bên trong báo lỗi trước nên công thức vẫn báo lỗi. Với dữ liệu như vậy, bạn bọc cả công thức bằng IFERROR:
=IFERROR(TEXTBEFORE(TEXTAFTER(A2," ")," ",-1),"")
Bài 3 công thức tách tên lót trong Excel có thêm cách tách tên lót bằng LEFT, MID, FIND cho Excel bản cũ.
Hàm TEXTSPLIT dùng như thế nào?
Hàm TEXTSPLIT có cú pháp thế nào?
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Tham số | Ý nghĩa |
|---|---|
text | Tham số này là ô chứa chuỗi cần tách. |
col_delimiter | Tham số này là ký tự phân cách để tách sang các cột. |
row_delimiter | Tham số này là ký tự phân cách để tách xuống các dòng. |
ignore_empty | Tham số này bằng TRUE thì hàm bỏ qua phần rỗng khi có 2 ký tự phân cách liền nhau. Bạn bỏ trống thì hàm hiểu là FALSE và giữ lại ô rỗng. |
match_mode | Tham số này bằng 1 thì hàm không phân biệt chữ hoa, chữ thường. Bạn bỏ trống thì hàm hiểu là 0 và có phân biệt. |
pad_with | Tham số này là giá trị điền vào ô bị thiếu khi các dòng có số phần khác nhau. Bạn bỏ trống thì hàm điền lỗi |
Hàm TEXTSPLIT trả về nhiều ô từ một công thức. Excel gọi cách trả kết quả này là tràn (spill): bạn chỉ gõ công thức ở ô đầu, và kết quả tự tràn sang các ô bên cạnh.
Ví dụ 7: Bạn tách địa chỉ ra 3 cột
Bảng địa chỉ nằm ở sheet Địa chỉ, có địa chỉ đầy đủ ở cột A. Mỗi địa chỉ gồm số nhà và đường, quận, thành phố, ngăn cách bằng dấu phẩy và một dấu cách. Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:
=TEXTSPLIT(A2,", ")


Viền xanh mảnh bao quanh B2:D2 cho biết đây là vùng tràn của một công thức. Nếu bạn bấm vào ô C2, thanh công thức hiện công thức của ô B2 bằng chữ màu xám, vì bạn chỉ sửa được công thức ở ô B2.
Sau đó, bạn kéo công thức từ ô B2 xuống ô B4. Mỗi dòng tự tràn sang 3 cột của dòng đó.

Bạn lưu ý 2 điểm khi tách địa chỉ:
Hàm TEXTSPLIT cần ô trống cho vùng tràn. Nếu ô
C2hoặcD2đã có dữ liệu, ôB2báo lỗi#SPILL!.Mỗi dòng phải có một công thức riêng. Nếu bạn gõ
=TEXTSPLIT(A2:A4,", ")cho cả vùng, hàm chỉ trả về phần đầu tiên của mỗi địa chỉ.
Nếu bạn chỉ cần thành phố, là phần cuối của địa chỉ, bạn gõ =TEXTAFTER(A2,", ",-1) vào một ô trống và nhận "Hà Nội". Công thức này vẫn đúng khi địa chỉ có thêm phần phường.
Ví dụ 8: Bạn tách một chuỗi thành bảng có dòng và cột
Ở sheet Đơn hàng, ô A2 chứa nội dung đơn hàng "Bút bi:20;Vở kẻ ngang:15;Thước kẻ:8". Dấu chấm phẩy ngăn các mặt hàng, còn dấu hai chấm ngăn tên hàng với số lượng. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:
=TEXTSPLIT(A2,":",";")


Hàm cắt tại mỗi dấu chấm phẩy để xuống dòng mới, rồi cắt tại mỗi dấu hai chấm để sang cột mới.
Ví dụ 9: Bạn cộng số lượng đã tách bằng SUM và VALUE
Ô D6 cần tính tổng số lượng. Nếu bạn gõ =SUM(D2:D4), kết quả là 0.

Kết quả bằng 0 vì 3 hàm TEXTBEFORE, TEXTAFTER, TEXTSPLIT luôn trả về chữ, kể cả khi chữ đó trông như số. Hàm SUM bỏ qua các ô chữ. Bạn dùng hàm VALUE để đổi chữ thành số trước khi cộng. Bạn chọn lại ô D6, gõ công thức sau rồi nhấn Enter:
=SUM(VALUE(D2:D4))


Bài chuyển text thành số trong Excel giải thích thêm các cách đổi chữ thành số.
Ví dụ 10: Bạn tách theo nhiều ký tự phân cách cùng lúc
Ở sheet Số điện thoại, khách hàng ghi số điện thoại không theo một kiểu. Có chỗ khách dùng dấu chấm phẩy, có chỗ dùng dấu phẩy, có chỗ có dấu cách sau ký tự phân cách và có chỗ gõ thừa 2 dấu chấm phẩy liền nhau. Bạn đặt các ký tự phân cách trong ngoặc nhọn {} để hàm cắt tại bất kỳ ký tự nào trong đó. Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:
=TRIM(TEXTSPLIT(A2,{";",","},,TRUE))

Công thức này có 3 điểm cần hiểu:
Ngoặc nhọn
{";",","}cho hàm biết dấu chấm phẩy và dấu phẩy đều là ký tự phân cách.Hai dấu phẩy liền nhau
,,bỏ trống tham số row_delimiter. Tham số TRUE sau đó là ignore_empty, giúp hàm bỏ phần rỗng nằm giữa 2 dấu chấm phẩy liền nhau.Hàm TRIM xóa dấu cách thừa ở đầu mỗi số, ví dụ dấu cách sau dấu chấm phẩy trong "0912345678; 0987654321".
Sau đó, bạn kéo công thức từ ô B2 xuống ô B3.

Nếu bạn bỏ TRUE, dòng 2 có một ô rỗng nằm giữa số thứ ba và số thứ tư.
Công thức dùng 3 hàm mới khác công thức dùng LEFT, MID, FIND thế nào?
Bảng sau đặt công thức dùng hàm cơ bản cạnh công thức dùng 3 hàm mới cho cùng một việc. Với dữ liệu trong bài, hai cách cho cùng kết quả. Khi chuỗi không có ký tự phân cách, cách cơ bản báo lỗi #VALUE!, còn cách mới báo lỗi #N/A.
Việc cần làm | Cách dùng hàm cơ bản | Cách dùng hàm mới |
|---|---|---|
Lấy họ |
|
|
Lấy tên |
|
|
Lấy số thứ tự sau dấu gạch thứ hai |
|
|
Tách địa chỉ ra 3 cột | 3 công thức LEFT, MID, FIND riêng cho 3 cột |
|
Cách dùng hàm cơ bản chạy được trên mọi phiên bản Excel. Nếu bạn gửi file cho người dùng Excel 2021 trở về trước, bạn nên giữ cách cơ bản. Bài hàm xử lý chuỗi trong Excel giải thích LEFT, MID, FIND, SUBSTITUTE từng bước.
Bạn hay gặp lỗi gì khi dùng 3 hàm này?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Ô báo | Excel 2021 trở về trước không có 3 hàm này. | Bạn dùng LEFT, MID, FIND như bảng so sánh ở trên. |
Ô báo | Chuỗi không có ký tự phân cách, ví dụ ô chỉ có một chữ "Hoa". | Bạn điền tham số if_not_found, ví dụ |
Ô báo | instance_num lớn hơn số ký tự phân cách có trong chuỗi, ví dụ | Bạn đếm lại số ký tự phân cách, hoặc dùng số âm để đếm từ cuối. |
Ô báo | Tham số instance_num bằng 0, lớn hơn số ký tự của chuỗi, hoặc ô gốc trống. Tham số if_not_found không thay được lỗi này. | Bạn dùng instance_num từ 1 trở lên hoặc số âm. Với cột có ô trống, bạn bọc công thức bằng IFERROR. |
Kết quả rỗng hoặc sai dù ô có chữ | Ô gốc có dấu cách thừa ở đầu, ở cuối hoặc 2 dấu cách liền nhau. Ví dụ, ô "Nguyễn Văn An " có dấu cách ở cuối nên | Bạn bọc ô bằng TRIM: |
TEXTSPLIT báo | Vùng tràn có ô đã chứa dữ liệu. | Bạn xóa dữ liệu trong các ô bên phải hoặc bên dưới ô công thức. |
TEXTSPLIT chỉ trả về phần đầu tiên | Bạn đưa cả vùng | Bạn gõ |
SUM cộng kết quả ra 0 | Ba hàm này luôn trả về chữ. | Bạn dùng |
Hàm không cắt được dù chuỗi có ký tự phân cách | Ký tự phân cách khác chữ hoa, chữ thường, ví dụ bạn tìm "x" trong "3X4". | Bạn đặt match_mode bằng 1 để hàm không phân biệt chữ hoa, chữ thường. |
Nếu bạn dùng Excel bản cũ và cần tách họ, tên, bạn có thể đọc bài tách tên trong Excel để xem cách dùng TRIM, RIGHT, SUBSTITUTE và Ctrl + E.
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.