Bạn vừa nhận danh sách học viên từ form đăng ký hoặc danh sách nhân viên từ phần mềm chấm công, và cả họ lẫn tên nằm chung trong cột "Họ và tên". Khi cần lọc người họ Nguyễn, đếm số người họ Trần hay ghép họ với tên thành "Nguyễn An" để in thẻ tên, bạn cần một cột Họ riêng. Với vài trăm người, việc gõ tay vừa lâu vừa dễ sai dấu.

Bài viết này hướng dẫn tách họ trong Excel bằng 4 cách, từ công thức dùng được trên mọi phiên bản tới các công cụ không cần công thức. Mọi cách đều chạy đúng với họ tên dài 2, 3, 4 hay 5 chữ. Đây là bài đầu tiên trong chuỗi 3 bài về tách họ tên của Nghề Excel, hai bài sau hướng dẫn tách tên và tách tên lót. Dữ liệu trong bài là giả định, còn mọi kết quả đã được kiểm tra lại bằng Excel 365.

Họ luôn nằm trước dấu cách đầu tiên trong ô

Với họ tên người Việt, họ là chữ đầu tiên, tức phần đứng trước dấu cách đầu tiên trong ô. Dù họ tên có 2 chữ như "Lê Na" hay 5 chữ như "Nguyễn Lê Hoàng Bảo Ngọc", phần phía sau dài hay ngắn cũng không ảnh hưởng tới họ. Vì vậy, bạn chỉ cần cho Excel tìm dấu cách đầu tiên rồi lấy phần chữ đứng trước nó. Nguyên tắc này chỉ không đúng với họ kép như Tôn Thất, và bài có phần riêng cho trường hợp đó.

Các ví dụ trong bài dùng chung bộ dữ liệu dưới đây. Bạn gõ "Họ và tên" vào ô A1 rồi gõ 8 họ tên vào các ô từ A2 tới A9 để làm theo:

Ô

Họ và tên

Số chữ

Họ cần tách

A2

Nguyễn Văn An

3

Nguyễn

A3

Lê Na

2

Lê

A4

Trần Thị Thu Hà

4

Trần

A5

Phạm Minh Tuấn

3

Phạm

A6

Hoàng Ngọc Bảo Châu

4

Hoàng

A7

Nguyễn Lê Hoàng Bảo Ngọc

5

Nguyễn

A8

··Võ···Thanh··Tùng·

3

Võ

A9

Đỗ Khánh Linh

3

Đỗ

Ô A8 được cố ý gõ dư dấu cách ở đầu, ở giữa và ở cuối để giống dữ liệu thực tế. Trang web không hiển thị được nhiều dấu cách liền nhau, nên bảng và ảnh dùng mỗi dấu · thay cho một dấu cách. Khi gõ vào Excel, bạn gõ dấu cách thật ở chỗ có dấu ·.

Hàm TRIM dọn dấu cách thừa trước khi tách họ

Dữ liệu dán từ phần mềm khác thường dính dấu cách thừa khó nhận ra. Vì mọi cách tách họ đều dựa vào vị trí dấu cách, một dấu cách thừa ở đầu ô cũng đủ làm kết quả sai. Hàm TRIM, với cú pháp =TRIM(text), xóa mọi dấu cách ở đầu và cuối ô, đồng thời gộp các dấu cách liền nhau ở giữa thành đúng một dấu cách.

Bạn gõ =TRIM(A2) vào ô B2, đưa chuột tới góc dưới bên phải của ô cho tới khi con trỏ thành dấu cộng màu đen, rồi nhấp đúp để Excel tự điền công thức xuống tới B9. Cột C và cột D dùng hàm LEN, là hàm đếm số ký tự trong ô, để so độ dài trước và sau khi làm sạch: ô C2 chứa =LEN(A2), ô D2 chứa =LEN(B2).

Hàm TRIM xóa dấu cách thừa trong cột họ và tên, ô A8 giảm từ 19 xuống 13 ký tự
TRIM xóa dấu cách ở đầu, ở cuối và gộp các dấu cách liền nhau ở giữa thành một

Ở các dòng dữ liệu sạch, độ dài không đổi. Riêng ô A8 giảm từ 19 xuống 13 ký tự vì 6 dấu cách thừa đã bị xóa. Các công thức tách họ ở phần sau đặt TRIM ngay bên trong, nên bạn không bắt buộc phải tạo cột B này.

Cách 1: LEFT kết hợp FIND tách họ trên mọi phiên bản Excel

Bạn nên học cách này đầu tiên, vì công thức chạy trên mọi phiên bản Excel và tự cập nhật khi bạn sửa họ tên. Công thức tách họ cho ô A2 là:

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

Bạn đọc công thức từ trong ra ngoài theo 3 bước:

  1. TRIM(A2) làm sạch họ tên. Với ô A2, kết quả vẫn là "Nguyễn Văn An". Với ô A8, kết quả là "Võ Thanh Tùng".

  2. FIND tìm vị trí dấu cách đầu tiên. Cú pháp =FIND(find_text, within_text) có hai đối số, tức hai giá trị bạn điền trong ngoặc. Đối số find_text là ký tự cần tìm, ở đây là dấu cách đặt trong ngoặc kép " ", còn within_text là chuỗi, tức đoạn chữ, cần tìm trong đó. Chữ "Nguyễn" có 6 ký tự, nên FIND trả về vị trí 7.

  3. LEFT lấy các ký tự đứng trước dấu cách. Cú pháp =LEFT(text, num_chars) lấy num_chars ký tự tính từ bên trái. Công thức lấy 7 - 1 = 6 ký tự, ra "Nguyễn".

Phép trừ 1 giúp kết quả không dính dấu cách. Nếu bỏ phần -1, LEFT lấy 7 ký tự, tức "Nguyễn" kèm một dấu cách vô hình ở cuối, và khi đếm hay tra cứu theo cột Họ, Excel coi "Nguyễn " và "Nguyễn" là hai giá trị khác nhau.

Trong ảnh dưới đây, cột B và cột C là hai cột phụ giúp bạn hiểu công thức, còn công thức tách họ nằm ở ô D2 và được điền xuống tới D9. Khi làm thật, bạn chỉ cần một cột trống cho công thức.

Tách họ trong Excel bằng công thức LEFT kết hợp FIND và TRIM cho họ tên có 2 đến 5 chữ
FIND tìm dấu cách đầu tiên, LEFT lấy các ký tự đứng trước dấu cách đó

Cột C trong ảnh chứa riêng phần =FIND(" ",TRIM(A2)), cho thấy công thức chạy đúng với mọi độ dài họ tên:

  • Họ tên 2 chữ "Lê Na" có dấu cách đầu tiên ở vị trí 3, nên LEFT lấy 2 ký tự là "Lê".

  • Họ tên 5 chữ "Nguyễn Lê Hoàng Bảo Ngọc" có dấu cách đầu tiên ở vị trí 7, nên kết quả là "Nguyễn".

  • Ô A8 sau khi TRIM có dấu cách đầu tiên ở vị trí 3, nên kết quả là "Võ".

Nếu muốn hiểu kỹ hơn về hàm LEFT, bạn có thể đọc bài Cách dùng hàm LEFT trong Excel để tách họ và tên.

Cách 2: Hàm TEXTBEFORE tách họ bằng công thức ngắn trên Excel 365

Trên Excel 365 và Excel 2024, hàm TEXTBEFORE lấy phần chữ đứng trước một ký tự phân cách, nên bạn không cần tự tìm vị trí rồi trừ 1. Bạn gõ vào ô B2 công thức sau rồi kéo xuống B9:

=TEXTBEFORE(TRIM(A2)," ")

Hàm có cú pháp =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]), trong đó các đối số trong ngoặc vuông không bắt buộc. Bạn chỉ cần text là chuỗi cần tách và delimiter là dấu cách. Khi bỏ trống các đối số còn lại, Excel lấy phần trước dấu cách thứ nhất, nên kết quả giống hệt cách 1.

Excel 2021 trở về trước chưa có TEXTBEFORE nên công thức báo lỗi #NAME?, vì vậy file gửi cho nhiều người nên dùng cách 1.

Cách 3: Flash Fill tách họ từ một ví dụ bạn gõ

Flash Fill là tính năng có từ Excel 2013. Tính năng này nhìn kết quả mẫu bạn gõ, tự đoán quy luật rồi điền tiếp cho các dòng còn lại. Bạn làm theo 3 bước:

  1. Bạn gõ tiêu đề "Họ" vào ô B1, ngay bên phải cột Họ và tên.

  2. Bạn gõ họ của người đầu tiên vào ô B2, ở đây là "Nguyễn".

  3. Bạn chọn ô B3 rồi nhấn Ctrl + E, hoặc vào thẻ Data và bấm Flash Fill.

Flash Fill tách họ trong Excel sau khi gõ một ví dụ ở ô B2 và nhấn Ctrl + E
Flash Fill điền họ cho cả cột chỉ từ một ví dụ bạn gõ

Với bộ dữ liệu trong bài, Flash Fill điền đúng cả 8 họ, kể cả ô A8. Tuy vậy, bạn cần nhớ hai điểm:

  • Kết quả của Flash Fill là giá trị cố định, nên khi bạn sửa họ tên ở cột A, cột B không tự cập nhật.

  • Flash Fill có thể đoán sai khi dữ liệu không đồng đều, nên bạn cần lướt kiểm tra cột kết quả sau khi nhấn Ctrl + E.

Bạn có thể xem thêm các phím tắt thông dụng khác trong bài Phím tắt Excel thông dụng cho dân văn phòng.

Cách 4: Text to Columns tách mỗi chữ của họ tên ra một cột

Text to Columns tách nội dung một ô thành nhiều cột theo ký tự phân cách. Công cụ này tách mọi chữ trong họ tên, và họ nằm ở cột đầu tiên. Bạn làm như sau:

  1. Bạn chọn vùng A2:A9, vào thẻ Data và bấm Text to Columns.

  2. Ở bước 1, bạn chọn Delimited, tức tách theo ký tự phân cách, rồi bấm Next.

  3. Ở bước 2, bạn bỏ chọn Tab, chọn Space và đánh dấu Treat consecutive delimiters as one để Excel coi nhiều dấu cách liền nhau là một, rồi bấm Next.

  4. Ở bước 3, bạn đổi ô Destination thành $B$2, tức ô B2, rồi bấm Finish.

Text to Columns tách họ tên thành nhiều cột, ô có dấu cách ở đầu bị lệch sang cột C
Text to Columns tách mỗi chữ ra một cột, số cột thay đổi theo độ dài họ tên

Kết quả trong ảnh cho thấy ba nhược điểm của cách này:

  • Số cột thay đổi theo độ dài họ tên: "Lê Na" chiếm 2 cột, còn "Nguyễn Lê Hoàng Bảo Ngọc" chiếm 5 cột, trong khi bạn chỉ cần cột B.

  • Dấu cách ở đầu ô vẫn làm lệch kết quả. Với ô A8, Excel để trống ô B8 và đẩy chữ "Võ" sang ô C8, nên bạn cần TRIM dữ liệu trước.

  • Công cụ này ghi đè dữ liệu. Nếu quên đổi ô Destination, kết quả đè lên chính cột A. Nếu các cột bên phải đang có dữ liệu, Excel hỏi trước khi ghi đè, và dữ liệu đó mất nếu bạn đồng ý.

Kết quả của Text to Columns cũng là giá trị cố định, nên cách này chỉ hợp với việc tách nhanh một lần.

Bạn nên chọn cách nào trong 4 cách tách họ?

Cách

Phiên bản Excel

Tự cập nhật

Phù hợp khi

LEFT kết hợp FIND

Mọi phiên bản

Có

File dùng lâu dài, gửi cho nhiều người

TEXTBEFORE

Excel 365, Excel 2024

Có

Mọi người cùng dùng Excel 365

Flash Fill

Excel 2013 trở lên

Không

Bạn cần tách nhanh một lần

Text to Columns

Mọi phiên bản

Không

Bạn cần tách mọi chữ ra cột riêng

Nếu chưa biết chọn cách nào, bạn nên bắt đầu với cách 1, vì cách này chạy ở mọi nơi và là nền tảng cho hai bài tiếp theo.

Những lỗi thường gặp khi tách họ trong Excel

Ảnh dưới đây gom 3 trường hợp làm công thức của cách 1 ở cột C báo lỗi #VALUE!. Các cột D, E, F là ba cách sửa.

Lỗi #VALUE! khi tách họ với ô một chữ, ô trống, dấu cách CHAR(160) và ba cách sửa
Ba trường hợp làm công thức tách họ báo lỗi và ba cách sửa tương ứng

Ô chỉ có một chữ hoặc ô trống làm công thức báo lỗi #VALUE!

Ô chỉ có một chữ như "Hoa" hoặc ô trống không có dấu cách nào, nên FIND báo lỗi #VALUE!. Bạn có hai cách sửa:

  • Bạn bọc công thức trong hàm IFERROR, là hàm trả về giá trị thay thế khi công thức lỗi: =IFERROR(LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1),TRIM(A2)). Khi có lỗi, công thức trả về nội dung ô sau khi TRIM, nên "Hoa" ra "Hoa" và ô trống ra ô trống.

  • Bạn nối thêm một dấu cách vào cuối chuỗi trước khi tìm: =LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1). Với "Hoa", chuỗi tìm kiếm thành "Hoa " nên FIND trả về 4 và LEFT lấy 3 ký tự là "Hoa". Họ tên bình thường vẫn cho kết quả như cũ.

Với TEXTBEFORE, hai trường hợp này báo lỗi #N/A. Bạn điền đối số thứ sáu if_not_found và để trống các đối số thứ ba tới thứ năm, nên giữa " " và TRIM(A2) có 4 dấu phẩy: =TEXTBEFORE(TRIM(A2)," ",,,,TRIM(A2)).

Quên TRIM làm cột Họ ra ô trống

Công thức =LEFT(A2,FIND(" ",A2)-1) không có TRIM vẫn ra đúng ở các dòng sạch. Riêng ô A8, dấu cách đầu tiên nằm ở vị trí 1, nên LEFT lấy 1 - 1 = 0 ký tự và trả về ô trống, và =TEXTBEFORE(A2," ") cũng vậy. Lỗi này không hiện thông báo, nên bạn thường chỉ phát hiện khi đếm theo họ và thấy thiếu người.

Dấu cách CHAR(160) làm công thức báo lỗi dù vẫn thấy khoảng trống

Dữ liệu sao chép từ trang web đôi khi dùng dấu cách không ngắt dòng, có mã ký tự 160. Ký tự này trông giống hệt dấu cách, nhưng TRIM không xóa được và FIND không tìm thấy, nên công thức báo #VALUE!. Ở dòng 4 trong ảnh, hai ô tô màu cam cho thấy IFERROR và cách nối thêm dấu cách không báo lỗi nhưng trả về nguyên "Nguyễn Văn An", tức kết quả sai mà không có dấu hiệu gì. Để kiểm tra, bạn dùng =LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(160),"")) để đếm số ký tự 160 trong ô A2. Khi áp dụng cho ô A4, công thức cho ra 2, ứng với 2 khoảng trống giữa ba từ.

Hàm SUBSTITUTE thay một ký tự bằng ký tự khác, còn CHAR(160) tạo ra ký tự có mã 160. Vì vậy, SUBSTITUTE(A2,CHAR(160)," ") đổi mọi ký tự 160 thành dấu cách thường trước khi TRIM. Công thức ở cột F là:

=LEFT(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),FIND(" ",TRIM(SUBSTITUTE(A2,CHAR(160)," "))&" ")-1)

Để dễ đọc, bạn có thể chia làm hai bước: ô B2 chứa =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) để làm sạch, rồi ô C2 chứa =LEFT(B2,FIND(" ",B2&" ")-1) để tách họ.

Họ kép như Tôn Thất hay Âu Dương cần một danh sách riêng

Các cách ở trên đều coi họ là chữ đầu tiên, nên họ kép như Tôn Thất, Âu Dương hay Tôn Nữ chỉ ra "Tôn" hoặc "Âu". Excel không thể tự biết ai mang họ kép, vì "Tôn Thất Minh Quân" và "Nguyễn Văn An" có cấu trúc giống hệt nhau. Cách xử lý thực tế là bạn lập danh sách họ kép, ở đây là các ô E2 tới E4, rồi cho công thức đối chiếu:

  1. Ô B2 lấy 2 chữ đầu bằng =IFERROR(LEFT(TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2))+1)-1),TRIM(A2)). Đối số thứ ba của FIND là vị trí bắt đầu tìm, nên FIND tìm từ ngay sau dấu cách đầu tiên và trả về vị trí dấu cách thứ hai. Với "Tôn Thất Minh Quân", vị trí này là 9, nên LEFT lấy 8 ký tự là "Tôn Thất". Họ tên 2 chữ như "Lê Na" không có dấu cách thứ hai, nên IFERROR trả về cả họ tên.

  2. Ô C2 chọn kết quả bằng =IF(COUNTIF($E$2:$E$4,B2)>0,B2,LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)). COUNTIF đếm số lần 2 chữ đầu xuất hiện trong danh sách E2:E4. Hàm IF chọn kết quả theo điều kiện: nếu số đếm lớn hơn 0, công thức lấy cả 2 chữ, nếu không thì lấy chữ đầu tiên như cách 1.

Tách họ kép Tôn Thất, Âu Dương, Tôn Nữ bằng COUNTIF đối chiếu với danh sách họ kép
Công thức lấy 2 chữ đầu khi cụm đó nằm trong danh sách họ kép

Các dấu $ trong $E$2:$E$4 giữ cố định vùng danh sách khi bạn kéo công thức xuống. Trong ảnh, "Nguyễn Văn" và "Hoàng Ngọc" không có trong danh sách, nên cột Họ chỉ lấy "Nguyễn" và "Hoàng". Khi gặp họ kép mới, bạn thêm vào cuối danh sách và mở rộng vùng trong công thức, ví dụ thành $E$2:$E$10. Bạn chỉ nên đưa vào những họ kép đã chắc chắn, vì công thức coi mọi họ tên bắt đầu bằng 2 chữ đó là họ kép.

Cột Họ giúp bạn thống kê và chuẩn hóa danh sách

Hàm COUNTIF đếm số người theo từng họ

Hàm COUNTIF có cú pháp =COUNTIF(range, criteria), trong đó range là vùng cần đếm và criteria là giá trị cần khớp. Trong ảnh dưới đây, cột B chứa họ đã tách bằng cách 1, cột D là danh sách họ bạn tự gõ. Bạn gõ vào ô E2 công thức =COUNTIF($B$2:$B$9,D2) rồi kéo xuống E8.

Đếm số người theo họ bằng hàm COUNTIF sau khi tách họ trong Excel
COUNTIF đếm số người mang từng họ dựa trên cột Họ đã tách

Họ Nguyễn có 2 người, các họ còn lại mỗi họ 1 người, và ô E9 tính =SUM(E2:E8) ra đúng 8 người.

Nếu đếm thẳng trên cột họ tên bằng =COUNTIF($A$2:$A$9,"Võ *"), tức đếm ô bắt đầu bằng "Võ" và một dấu cách, với dấu * đại diện cho mọi ký tự phía sau, bạn nhận về 0 vì ô A8 bắt đầu bằng dấu cách thừa. Vì vậy, bạn nên đếm trên cột Họ đã TRIM. Bạn có thể tìm hiểu thêm trong bài hướng dẫn đếm tần suất xuất hiện trong Excel.

Hàm PROPER viết hoa chữ cái đầu của họ

Danh sách nhập từ form thường lẫn chữ hoa, chữ thường. Hàm PROPER viết hoa chữ cái đầu mỗi chữ và viết thường các chữ còn lại, nên =PROPER(LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)) cho ra "Nguyễn" từ "NGUYỄN VĂN AN" và "Đỗ" từ "đỗ khánh linh". PROPER xử lý đúng chữ có dấu, kể cả chữ Đ.

Ngoại lệ là dữ liệu gõ bằng bảng mã Unicode tổ hợp, trong đó dấu thanh là một ký tự riêng. PROPER không coi ký tự dấu rời là chữ cái, nên biến "nguyễn" thành "NguyễN". Dữ liệu loại này làm LEN đếm dư ký tự, ví dụ "nguyễn văn an" dài 14 thay vì 13 ký tự. Bạn sửa bằng cách chuyển sang bảng mã Unicode dựng sẵn, ví dụ với công cụ chuyển mã của Unikey.

Câu hỏi thường gặp khi tách họ trong Excel

Công thức tách họ có dùng được cho họ tên 2 chữ hoặc 5 chữ không?

Công thức dùng được, vì công thức chỉ dựa vào dấu cách đầu tiên. Cả 8 dòng dữ liệu trong bài, gồm họ tên dài từ 2 tới 5 chữ, đều ra đúng họ.

Làm sao để cột Họ không còn là công thức?

Bạn chọn cột Họ, nhấn Ctrl + C, bấm chuột phải vào chính vùng đó và chọn Paste Values, là biểu tượng có số 123. Sau đó, bạn có thể xóa cột Họ và tên gốc mà cột Họ không bị lỗi #REF!.

Vì sao Excel báo lỗi #NAME? khi dùng TEXTBEFORE?

Lỗi #NAME? cho biết phiên bản Excel của bạn chưa có hàm TEXTBEFORE. Hàm này chỉ có trên Excel 365, Excel 2024 và Excel cho web, nên bạn dùng cách 1 để thay thế.

Hai bài tiếp theo hướng dẫn tách tên và tách tên lót

Hai bài tiếp theo dùng lại bộ dữ liệu 8 họ tên trong bài này:

  • Bài tách tên trong Excel hướng dẫn lấy chữ cuối cùng như An, Na, Hà. Bài này khó hơn một chút, vì tên nằm sau dấu cách cuối cùng chứ không phải dấu cách đầu tiên.

  • Bài tách tên lót trong Excel hướng dẫn lấy phần giữa họ và tên, ví dụ "Thị Thu" trong "Trần Thị Thu Hà", và xử lý trường hợp không có tên lót như "Lê Na".

Khi ô B2 chứa họ và ô C2 chứa tên, công thức =B2&" "&C2 dùng toán tử & để nối chuỗi và cho ra "Nguyễn An".

Bạn muốn xử lý danh sách họ tên và dữ liệu văn phòng thành thạo hơn?

TRIM, LEFT, FIND, SUBSTITUTE và COUNTIF là nhóm hàm bạn sẽ dùng thường xuyên khi làm sạch danh sách nhân viên, học viên hay khách hàng ở công ty.

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.