Khi nhập danh sách nhân viên vào phần mềm nhân sự, khai biểu mẫu bảo hiểm hay in giấy khen, bạn có thể được yêu cầu tách họ tên thành 3 ô Họ, Tên lót và Tên, trong khi danh sách gốc lại gộp cả họ tên vào một ô như "Nguyễn Văn An". Họ luôn là chữ đầu và tên luôn là chữ cuối, nên phần khó tách nhất là tên lót nằm ở giữa.
Bài viết này là bài cuối trong chuỗi 3 bài về tách họ tên, sau hai bài cách tách họ trong Excel và cách tách tên trong Excel. Bài này đi qua 3 công thức tách tên lót từ dễ đến khó, Flash Fill, cách xử lý tên kép và cách kiểm tra kết quả. Mọi công thức đều chạy đúng với họ tên từ 2 đến 5 chữ, đã được kiểm tra bằng Excel 365.
Tên lót là gì và vì sao tên lót khó tách nhất?
Tên lót, còn gọi là tên đệm, là phần nằm giữa họ và tên. Với "Nguyễn Văn An", họ là Nguyễn, tên là An và tên lót là Văn. Tên lót khác với "họ và tên lót" (có nơi gọi là họ đệm), vì họ và tên lót gồm cả họ, ví dụ "Nguyễn Văn". Trong bài, mỗi tiếng trong họ tên được gọi là một chữ, nên "Nguyễn Văn An" là họ tên 3 chữ.
Tên lót khó tách vì hai lý do. Thứ nhất, số chữ của tên lót thay đổi: "Lê Na" không có tên lót, "Nguyễn Văn An" có 1 chữ, còn "Nguyễn Lê Hoàng Bảo Ngọc" có tới 3 chữ. Thứ hai, tên lót nằm ở giữa, nên bạn không thể lấy từ đầu chuỗi (tức đoạn chữ trong ô) như họ hay từ cuối chuỗi như tên. Công thức tách tên lót phải tìm được cả điểm bắt đầu lẫn điểm kết thúc, và phải trả về ô trống khi người đó không có tên lót.
Bộ dữ liệu mẫu dùng chung cho cả chuỗi bài
Cả chuỗi 3 bài dùng chung danh sách 8 nhân viên dưới đây. Bạn gõ tiêu đề "Họ và tên" vào ô A1 và gõ họ tên vào các ô từ A2 tới A9. Cột cuối là tên lót bạn cần lấy được.
Ô | Họ và tên | Số chữ | Tên lót cần lấy |
|---|---|---|---|
A2 | Nguyễn Văn An | 3 | Văn |
A3 | Lê Na | 2 | (ô trống) |
A4 | Trần Thị Thu Hà | 4 | Thị Thu |
A5 | Phạm Minh Tuấn | 3 | Minh |
A6 | Hoàng Ngọc Bảo Châu | 4 | Ngọc Bảo |
A7 | Nguyễn Lê Hoàng Bảo Ngọc | 5 | Lê Hoàng Bảo |
A8 | Võ Thanh Tùng (có dấu cách thừa) | 3 | Thanh |
A9 | Đỗ Khánh Linh | 3 | Khánh |
Ô A8 được cố ý gõ thừa dấu cách: 2 dấu cách ở đầu, 3 dấu cách giữa "Võ" và "Thanh", 2 dấu cách giữa "Thanh" và "Tùng", 1 dấu cách ở cuối. Ảnh dưới đây là kết quả cuối cùng khi bạn tách xong cả 3 cột, trong đó ô C3 trống vì "Lê Na" không có tên lót.

Bước chuẩn bị: làm sạch họ tên bằng hàm TRIM
Mọi công thức tách tên lót đều dựa vào vị trí dấu cách, nên chỉ một dấu cách thừa cũng đủ làm kết quả sai. Hàm TRIM xóa dấu cách ở đầu và cuối chuỗi, đồng thời gộp nhiều dấu cách liền nhau thành một. Ví dụ, =TRIM(A8) biến ô A8 thành "Võ Thanh Tùng". Các công thức trong bài đều viết TRIM(A2) ở mọi chỗ cần đọc họ tên, nên bạn không cần cột phụ.
Cách 1: Lấy phần ở giữa bằng MID khi đã có cột Họ và cột Tên
Cách này dễ hiểu nhất nếu bạn đã làm theo hai bài trước. Bạn gõ công thức tách họ =LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1) vào ô B2 và công thức tách tên =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),100)) vào ô D2, rồi kéo cả hai xuống tới dòng 9. Kéo xuống nghĩa là bạn nắm ô vuông nhỏ ở góc dưới bên phải ô rồi kéo để chép công thức xuống các dòng dưới. Tên lót là phần còn lại sau khi bỏ họ ở đầu và bỏ tên ở cuối, nên bạn gõ vào ô C2 công thức sau rồi kéo xuống tới ô C9:
=TRIM(MID(TRIM(A2),LEN(B2)+1,LEN(TRIM(A2))-LEN(B2)-LEN(D2)))
Hàm LEN đếm số ký tự của một chuỗi, kể cả dấu cách. Hàm MID có cú pháp =MID(text, start_num, num_chars), nghĩa là lấy num_chars ký tự của chuỗi text, bắt đầu từ ký tự thứ start_num. Ba giá trị trong ngoặc được gọi là đối số của hàm. Với "Nguyễn Văn An" ở dòng 2, công thức chạy qua 4 bước:
Excel đo độ dài cả họ tên.
LEN(TRIM(A2))trả về 13, gồm 11 chữ cái và 2 dấu cách.Excel chọn điểm bắt đầu. Họ "Nguyễn" dài 6 ký tự, nên
LEN(B2)+1bằng 7. Ký tự thứ 7 chính là dấu cách đứng ngay sau họ.Excel tính số ký tự cần lấy.
LEN(TRIM(A2))-LEN(B2)-LEN(D2)bằng 13 - 6 - 2 = 5, nên MID lấy được " Văn ", tức chữ Văn kèm một dấu cách ở mỗi bên.TRIM dọn dấu cách. TRIM xóa hai dấu cách ở hai đầu, nên ô C2 chỉ còn "Văn".

Các cột từ E tới H trong ảnh ghi lại 3 bước đầu và phần MID lấy ra cho cả 8 dòng. Bạn cần chú ý nhất tới dòng 3: "Lê Na" dài 5 ký tự, họ và tên đều dài 2 ký tự, nên MID lấy 5 - 2 - 2 = 1 ký tự, chính là dấu cách giữa Lê và Na. TRIM xóa dấu cách đó, nên ô C3 trống đúng như mong muốn. Ở dòng 7, vẫn công thức đó lấy đủ 3 chữ "Lê Hoàng Bảo".
Công thức cố ý lấy dư hai dấu cách rồi để TRIM dọn. Nếu bạn viết =MID(TRIM(A2),LEN(B2)+2,LEN(TRIM(A2))-LEN(B2)-LEN(D2)-2) để bỏ dấu cách ngay từ đầu, số ký tự cần lấy của "Lê Na" thành 5 - 2 - 2 - 2 = -1 và MID báo lỗi #VALUE!. Bạn có thể đọc thêm về hàm LEN trong bài 6 cách đếm ký tự trong Excel người đi làm nên biết.
Cách 2: Một công thức tìm dấu cách đầu tiên và dấu cách cuối cùng
Cách thứ hai không cần cột Họ, cột Tên và chạy được trên mọi phiên bản Excel. Ý tưởng là tên lót luôn nằm giữa dấu cách đầu tiên và dấu cách cuối cùng. Hàm FIND tìm được ngay dấu cách đầu tiên, còn dấu cách cuối cùng cần một mẹo: bạn thay riêng dấu cách cuối bằng dấu #, một ký tự không có trong tên người, rồi tìm dấu # đó. Ảnh dưới đây tách mẹo này thành từng cột.

Với "Nguyễn Văn An" ở dòng 2, các cột được tính như sau:
Ô B2 tìm dấu cách đầu tiên bằng
=FIND(" ",TRIM(A2))và trả về 7.Ô C2 đếm số dấu cách bằng
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")). Hàm SUBSTITUTE(chuỗi, chữ cũ, chữ mới) thay chữ cũ bằng chữ mới. Ở đây chữ mới là "" (rỗng), nên SUBSTITUTE xóa hết dấu cách và chuỗi ngắn đi đúng bằng số dấu cách: 13 - 11 = 2.Ô D2 dùng
=SUBSTITUTE(TRIM(A2)," ","#",C2). Đối số thứ tư của SUBSTITUTE cho biết chỉ thay lần xuất hiện thứ mấy, nên Excel chỉ thay dấu cách thứ 2, cũng là dấu cách cuối cùng, và trả về "Nguyễn Văn#An".Ô E2 tìm dấu # bằng
=FIND("#",D2)và trả về 11, là vị trí dấu cách cuối cùng.Ô F2 dùng
=TRIM(MID(TRIM(A2),B2,E2-B2)). MID bắt đầu từ dấu cách đầu tiên, lấy 11 - 7 = 4 ký tự là " Văn", rồi TRIM xóa dấu cách ở đầu.
"Lê Na" chỉ có 1 dấu cách, nên dấu cách đầu và dấu cách cuối cùng nằm ở vị trí 3. MID lấy 3 - 3 = 0 ký tự, và ô F3 trống mà không báo lỗi. Nếu MID bắt đầu từ ký tự kế tiếp như =MID(TRIM(A2),B2+1,E2-B2-1), số ký tự cần lấy của "Lê Na" thành -1 và công thức báo #VALUE!.
Khi thay B2, C2, D2, E2 bằng chính công thức của các ô đó, bạn có một công thức duy nhất chỉ cần cột A. Trên một trang tính chỉ có cột họ tên, bạn gõ công thức này vào ô B2 rồi kéo xuống:
=TRIM(MID(TRIM(A2),FIND(" ",TRIM(A2)),FIND("#",SUBSTITUTE(TRIM(A2)," ","#",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-FIND(" ",TRIM(A2))))
Trong công thức, phần FIND(" ",TRIM(A2)) là dấu cách đầu tiên, còn phần bắt đầu bằng FIND("#",SUBSTITUTE( là dấu cách cuối cùng.
Hàm LET giúp công thức của Cách 2 dễ đọc hơn
Hàm LET có trên Excel 365 và Excel 2021 trở lên, cho phép bạn đặt tên cho một phép tính rồi dùng lại tên đó:
=LET(t,TRIM(A2),dau,FIND(" ",t),cuoi,FIND("#",SUBSTITUTE(t," ","#",LEN(t)-LEN(SUBSTITUTE(t," ","")))),TRIM(MID(t,dau,cuoi-dau)))
Công thức đặt 3 tên: t là họ tên đã làm sạch, dau là vị trí dấu cách đầu tiên, cuoi là vị trí dấu cách cuối cùng, rồi trả về kết quả của TRIM(MID(t,dau,cuoi-dau)). Bạn nên tránh đặt tên trùng địa chỉ ô như C1, vì Excel sẽ không nhận công thức.
Cách 3: TEXTAFTER kết hợp TEXTBEFORE trên Excel 365
Excel 365 và Excel 2024 có hai hàm TEXTAFTER lấy phần đứng sau một ký tự phân cách và TEXTBEFORE lấy phần đứng trước ký tự đó. Excel 2021 trở về trước chưa có hai hàm này và sẽ báo lỗi #NAME?. Tên lót đứng sau họ và đứng trước tên, nên bạn lồng hai hàm vào nhau trong ô C2 rồi kéo xuống:
=TEXTBEFORE(TEXTAFTER(TRIM(A2)," ")," ",-1,,,"")
Phần bên trong
TEXTAFTER(TRIM(A2)," ")lấy phần đứng sau dấu cách đầu tiên, tức bỏ họ. "Nguyễn Văn An" còn lại "Văn An".TEXTBEFORE bên ngoài lấy phần đứng trước dấu cách cuối cùng của "Văn An", tức bỏ tên, nên chỉ còn "Văn". Số -1 ở đối số thứ ba nghĩa là Excel đếm dấu cách từ phải sang.
Ba dấu phẩy liền nhau sau số -1 bỏ trống đối số thứ tư và thứ năm, rồi tới đối số thứ sáu là if_not_found, tức giá trị trả về khi không tìm thấy dấu cách. Với "Lê Na", TEXTAFTER chỉ còn "Na", không còn dấu cách nào, nên TEXTBEFORE trả về chuỗi rỗng "". Nếu thiếu đối số này, ô C3 báo lỗi #N/A như cột D ở phần trên của ảnh dưới đây.

TEXTSPLIT không cho ngay một cột tên lót
Hàm TEXTSPLIT tách mỗi chữ của họ tên ra một ô. Khi bạn gõ =TEXTSPLIT(TRIM(A2)," ") vào ô B2, Excel tự đổ kết quả sang các ô bên phải, và bạn kéo công thức xuống cho các dòng khác. Phần dưới của ảnh cho thấy tên lót (các ô tô cam) nằm ở số cột khác nhau tùy từng người, còn "Lê Na" không có ô nào. Vì vậy bạn không thể chọn sẵn một cột làm cột Tên lót, và công thức của Cách 3 vẫn là lựa chọn gọn hơn.
Flash Fill tách tên lót nhanh nhưng dễ đoán sai
Flash Fill là tính năng có từ Excel 2013, tự đoán quy luật từ ví dụ bạn gõ rồi điền tiếp các dòng còn lại. Bạn gõ tên lót của người đầu tiên vào ô B2, chọn ô B3, rồi nhấn Ctrl + E hoặc vào thẻ Data và bấm Flash Fill. Bạn có thể xem thêm các phím tắt khác trong bài Phím tắt Excel thông dụng cho dân văn phòng. Ảnh dưới đây là kết quả thật của hai lần chạy Flash Fill trên bộ dữ liệu mẫu:

Ở lần thử thứ nhất, bạn chỉ gõ "Văn" vào ô B2. Flash Fill lấy chữ đứng ngay trước tên, nên kết quả sai ở 4 dòng: "Lê Na" ra Lê, "Trần Thị Thu Hà" ra Thu, còn dòng 6 và dòng 7 đều ra Bảo.
Ở lần thử thứ hai, bạn gõ thêm "Thị Thu" vào dòng 4. Lúc này Flash Fill lấy mọi chữ nằm giữa họ và tên, nên điền đúng cả 8 dòng, kể cả ô trống cho "Lê Na".
Flash Fill không báo khi đoán sai, nên bạn nên chọn ngay một ví dụ có tên lót 2 chữ và kiểm tra lại bằng cách ghép ngược ở phần sau. Kết quả của Flash Fill cũng không tự cập nhật khi danh sách thay đổi.
Tên kép và họ kép: Excel tách đúng quy tắc nhưng có thể sai ý
Mọi cách ở trên đều coi chữ đầu là họ và chữ cuối là tên, nhưng Excel không biết ai có tên kép hay họ kép. Với "Hoàng Ngọc Bảo Châu", Excel cho tên lót là "Ngọc Bảo". Nếu người này thực sự tên là "Bảo Châu", tên lót đúng chỉ là "Ngọc". Tương tự, "Nguyễn Lê Hoàng Bảo Ngọc" có thể mang họ ghép "Nguyễn Lê" từ họ của cả bố và mẹ. Không công thức nào tự đoán được những trường hợp này, nên bạn kiểm tra tay theo 3 bước:
Bạn thêm cột E đếm số chữ bằng
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1, tức số dấu cách cộng thêm 1.Bạn bật bộ lọc bằng Ctrl + Shift + L rồi lọc cột E để xem các dòng có từ 4 chữ trở lên, vì họ tên càng dài thì càng có nhiều cách chia.
Bạn đối chiếu với hồ sơ gốc, rồi gõ đè giá trị đúng vào ô Họ hoặc ô Tên của dòng cần sửa.
Ở bước cuối, Cách 1 có lợi thế vì cột Tên lót được tính từ cột Họ và cột Tên. Khi bạn gõ đè "Bảo Châu" vào ô D6, ô C6 tự đổi thành "Ngọc". Khi bạn gõ "Nguyễn Lê" vào ô B7 và "Bảo Ngọc" vào ô D7, ô C7 tự đổi thành "Hoàng".

Ghép ngược để lấy cột Họ và tên lót, rồi kiểm tra kết quả
Một số phần mềm chỉ có hai ô "Họ và tên lót" và "Tên". Cột Họ và tên lót gồm mọi chữ đứng trước tên, nên bạn gõ một trong hai công thức sau vào ô E2 rồi kéo xuống:
Trên mọi phiên bản Excel,
=LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(D2)-1)lấy phần bên trái của họ tên, bỏ tên và dấu cách đứng trước tên. Với "Nguyễn Văn An", công thức lấy 13 - 2 - 1 = 10 ký tự là "Nguyễn Văn".Trên Excel 365,
=TEXTBEFORE(TRIM(A2)," ",-1)lấy phần đứng trước dấu cách cuối cùng.
Với "Lê Na", cả hai công thức đều trả về "Lê" vì người này không có tên lót.
Sau khi tách, bạn nên ghép 3 cột lại để kiểm tra bằng công thức =TEXTJOIN(" ",TRUE,B2:D2)=TRIM(A2) ở ô F2. TEXTJOIN nối các ô từ B2 tới D2 bằng dấu cách và bỏ qua ô trống nhờ đối số TRUE, nên dòng "Lê Na" ghép lại không bị dư dấu cách. Dấu bằng ở giữa công thức so sánh chuỗi vừa ghép với họ tên gốc đã làm sạch: TRUE nghĩa là tách khớp, FALSE nghĩa là dòng đó cần xem lại.

Hàm TEXTJOIN có từ Excel 2019. Trên Excel 2016 trở về trước, bạn dùng =TRIM(B2&" "&C2&" "&D2)=TRIM(A2), trong đó dấu & nối các chuỗi, còn TRIM gộp hai dấu cách liền nhau ở dòng không có tên lót. Khi danh sách dài, bạn lọc cột F theo FALSE để tìm nhanh dòng cần sửa.
Những lỗi thường gặp khi tách tên lót
Bạn thấy | Nguyên nhân | Cách sửa |
|---|---|---|
Dòng họ tên 2 chữ báo lỗi #VALUE! | MID bỏ dấu cách bằng cách cộng 1 và trừ 2, nên số ký tự cần lấy bị âm | Lấy dư dấu cách rồi bọc TRIM như Cách 1 và Cách 2 |
Dòng họ tên 2 chữ báo lỗi #N/A | TEXTBEFORE thiếu đối số if_not_found | Thêm |
Dòng 8 ra "Võ Thanh T" hoặc ra cả họ tên | Công thức đọc thẳng ô A2 mà quên TRIM | Thay |
Ô chỉ có 1 chữ như "An" báo lỗi #VALUE! hoặc #N/A | Ô không có dấu cách nào để công thức cắt | Bọc công thức trong IFERROR, ví dụ |
Công thức báo lỗi dù vẫn thấy khoảng trống giữa các chữ | Dữ liệu dán từ web chứa dấu cách không ngắt dòng (mã 160) mà TRIM không xóa được | Thay |
Câu hỏi thường gặp về tách tên lót trong Excel
Công thức nào tách tên lót gọn nhất?
Trên Excel 365, công thức gọn nhất là =TEXTBEFORE(TEXTAFTER(TRIM(A2)," ")," ",-1,,,""). Trên phiên bản cũ hơn, bạn dùng Cách 1 nếu đã có cột Họ và cột Tên, hoặc dùng Cách 2 nếu chỉ cần cột Tên lót.
Họ tên có 6 chữ trở lên thì công thức còn đúng không?
Công thức vẫn đúng, vì cả 3 cách đều cắt theo dấu cách đầu tiên và dấu cách cuối cùng chứ không đếm cố định số chữ. Với họ tên 6 chữ "Trần Lê Ngọc Bảo Khánh Vy", cả 3 cách đều cho tên lót "Lê Ngọc Bảo Khánh".
Làm sao bỏ công thức và chỉ giữ lại chữ trước khi nhập vào phần mềm?
Bạn chọn các cột kết quả, nhấn Ctrl + C, rồi nhấn Ctrl + Alt + V và chọn Values để dán đè dưới dạng giá trị. Sau bước này, các cột kết quả không còn phụ thuộc vào cột họ tên gốc.
Tổng kết chuỗi 3 bài tách họ tên trong Excel
Bảng dưới đây đặt cạnh nhau công thức của cả chuỗi 3 bài, với họ tên ở ô A2, họ ở cột B, tên lót ở cột C, tên ở cột D.
Cột cần lấy | Mọi phiên bản Excel | Excel 365 | Kết quả với Nguyễn Lê Hoàng Bảo Ngọc |
|---|---|---|---|
Họ (bài 1) |
|
| Nguyễn |
Tên lót (bài này) |
|
| Lê Hoàng Bảo |
Tên (bài 2) |
|
| Ngọc |
Họ và tên lót |
|
| Nguyễn Lê Hoàng Bảo |
Kiểm tra |
|
| TRUE |
Với danh sách thật, bạn đi theo thứ tự: tách họ, tách tên, tách tên lót, ghép lại để kiểm tra, rồi đối chiếu các dòng từ 4 chữ trở lên. Bạn có thể ôn lại hàm LEFT trong bài Cách dùng hàm LEFT trong Excel để tách họ và tên.
Bạn muốn xử lý dữ liệu chữ trong Excel nhanh và chắc tay hơn?
Khi nắm vững nhóm hàm xử lý chữ như LEFT, MID, LEN, FIND, SUBSTITUTE và TRIM, bạn sẽ tự làm sạch được danh sách khách hàng, mã hàng hay địa chỉ trước khi đưa vào báo cáo.
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.