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 #N/A.

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," ")

Bạn đang gõ vào ô B2 công thức =TEXTBEFORE(A2," ")
Công thức lấy phần đứng trước dấu cách đầu tiên trong ô 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.

Cột B có 4 họ: Nguyễn, Trần, Lê, Phạm, ô B3 đang chọn
Mỗi dòng lấy đúng chữ đầu tiên của họ tên

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)

Bạn đang gõ vào ô C2 công thức =TEXTBEFORE(A2," ",-1)
Số -1 bảo hàm cắt ở dấu cách cuối cùng

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.

Cột C có Nguyễn Văn, Trần Thị Thu, Lê, Phạm Minh, ô C3 đang chọn
Họ tên có 2, 3 hay 4 chữ thì công thức vẫn lấy đúng phần trước tên

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,"-")

Bạn đang gõ vào ô B2 của sheet Mã đơn công thức =TEXTBEFORE(A2,"-")
Ký tự phân cách ở đây là dấu gạch "-"

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)

Bạn đang gõ vào ô D2 công thức =TEXTAFTER(A2," ",-1)
Hàm lấy phần đứng sau dấu cách cuối cùng

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.

Cột D có 4 tên: An, Hà, Hoa, Đức, ô D3 đang chọn
Ô D3 lấy được "Hà" dù họ tên có 4 chữ

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)

Bạn đang gõ vào ô C2 công thức =TEXTAFTER(A2,"-",2), cột B đã có HN, HCM, DN
Số 2 bảo hàm cắt ở dấu gạch thứ hai tính từ đầu chuỗi

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

Cột C có 0158, 0042, 1203, ô C2 đang chọn
Kết quả giữ nguyên số 0 ở đầu vì hàm trả về chữ, không trả về số

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:

  1. Hàm TEXTAFTER(A2," ") bỏ họ và trả về "Văn An".

  2. 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)

Bạn đang gõ vào ô E2 công thức =TEXTBEFORE(TEXTAFTER(A2," ")," ",-1)
Hàm TEXTAFTER chạy trước, rồi TEXTBEFORE cắt tiếp trên kết quả đó

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

Cột E có Văn, Thị Thu, Minh, riêng ô E4 của Lê Hoa báo lỗi #N/A
Ô E4 báo lỗi #N/A vì "Lê Hoa" không có tên đệm

Ô 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,,,"")

Bạn đang gõ vào ô E2 công thức có thêm ,,,"" ở cuối
Ba dấu phẩy liền nhau bỏ trống match_mode và match_end, còn "" là giá trị trả về khi không tìm thấy

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

Cột E có Văn, Thị Thu, ô E4 trống, Minh
Ô E4 để trống thay vì báo lỗi

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 #N/A.

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,", ")

Bạn đang gõ vào ô B2 công thức =TEXTSPLIT(A2,", ")
Ký tự phân cách gồm dấu phẩy và một dấu cách, nên kết quả không có dấu cách thừa ở đầu
Ô B2, C2, D2 hiện Số 15 Lý Thường Kiệt, Hoàn Kiếm, Hà Nội, có viền xanh bao quanh vùng tràn
Công thức chỉ nằm ở ô B2, còn ô C2 và D2 là phần tràn ra

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ảng địa chỉ có 3 dòng đã tách thành 3 cột, ô C3 đang chọn và thanh công thức hiện chữ xám =TEXTSPLIT(A3,", ")
Ô C3 nằm trong vùng tràn của công thức ở ô B3

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 ô C2 hoặc D2 đã có dữ liệu, ô B2 bá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,":",";")

Bạn đang gõ vào ô C2 công thức =TEXTSPLIT(A2,":",";")
Tham số thứ hai tách sang cột, tham số thứ ba tách xuống dòng
Vùng C2:D4 có Bút bi 20, Vở kẻ ngang 15, Thước kẻ 8
Một công thức trả về bảng 3 dòng, 2 cột

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.

Ô D6 có công thức =SUM(D2:D4) và kết quả bằng 0
Số 20, 15, 8 nằm sát lề trái vì đây là chữ, không phải số

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ạn đang gõ vào ô D6 công thức =SUM(VALUE(D2:D4))
Hàm VALUE đổi cả 3 ô D2, D3, D4 thành số, rồi SUM cộng lại
Ô D6 hiện tổng 43
Tổng số lượng là 20 + 15 + 8 = 43

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))

Bạn đang gõ vào ô B2 công thức =TRIM(TEXTSPLIT(A2,{";",","},,TRUE))
Ngoặc nhọn chứa 2 ký tự phân cách là dấu chấm phẩy và dấu phẩy

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.

Dòng 2 tách được 4 số điện thoại vào B2:E2, dòng 3 tách được 2 số vào B3:C3
Số 0 ở đầu số điện thoại được giữ nguyên vì kết quả là chữ

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ọ

=LEFT(A2,FIND(" ",A2)-1)

=TEXTBEFORE(A2," ")

Lấy tên

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

=TEXTAFTER(A2," ",-1)

Lấy số thứ tự sau dấu gạch thứ hai

=MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,100)

=TEXTAFTER(A2,"-",2)

Tách địa chỉ ra 3 cột

3 công thức LEFT, MID, FIND riêng cho 3 cột

=TEXTSPLIT(A2,", ")

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 #NAME?

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 #N/A

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ụ =TEXTAFTER(A2," ",-1,,,""). Nếu muốn lấy nguyên chữ "Hoa", bạn đặt match_end bằng 1: =TEXTAFTER(A2," ",-1,,1).

Ô báo #N/A khi dùng số thứ tự lớn

instance_num lớn hơn số ký tự phân cách có trong chuỗi, ví dụ =TEXTAFTER(A2,"-",3) với mã chỉ có 2 dấu gạch.

Bạn đếm lại số ký tự phân cách, hoặc dùng số âm để đếm từ cuối.

Ô báo #VALUE!

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 TEXTAFTER(A2," ",-1) lấy phần rỗng sau dấu cách cuối.

Bạn bọc ô bằng TRIM: =TEXTAFTER(TRIM(A2)," ",-1).

TEXTSPLIT báo #SPILL!

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 A2:A4 vào một công thức.

Bạn gõ =TEXTSPLIT(A2,", ") vào ô B2 rồi kéo xuống, để mỗi dòng có một công thức riêng.

SUM cộng kết quả ra 0

Ba hàm này luôn trả về chữ.

Bạn dùng =SUM(VALUE(D2:D4)).

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.