Tách họ và tên trong Excel: 5 cách có hàm và không dùng hàm

Q: Muốn ghép họ và tên lại thành một cột thì làm thế nào?

Dùng =B2&" "&C2, trong đó B2 là họ đệm, C2 là tên. Dấu " " thêm một dấu cách ở giữa.

Cách tách họ và tên trong Excel nhanh nhất là dùng Flash Fill: gõ mẫu tên của dòng đầu, rồi nhấn Ctrl + E, Excel tự điền phần còn lại. Nếu muốn kết quả tự cập nhật khi sửa dữ liệu, dùng hàm: tên lấy bằng =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)), họ và đệm lấy bằng LEFT và LEN. Excel trong Microsoft 365 và Excel 2024 còn có hàm TEXTAFTER, TEXTBEFORE ngắn gọn hơn nhiều. Bài này hướng dẫn đủ 5 cách, có ví dụ với tên tiếng Việt và cách sửa lỗi thường gặp.

Vì sao tách họ tên tiếng Việt khó hơn tên tiếng Anh?

Tên người Việt có số chữ không cố định: "Lê Na" có 2 chữ, "Nguyễn Văn An" có 3 chữ, "Tôn Nữ Thị Minh Châu" có 5 chữ. Phần tên luôn là chữ cuối cùng, còn họ là chữ đầu, phần ở giữa là tên đệm.

Vì vậy, công thức của Microsoft viết cho tên tiếng Anh (tên trước, họ sau) không dùng nguyên được. Mẹo chung là: tìm dấu cách cuối cùng để lấy tên, rồi lấy phần còn lại làm họ đệm.

Trong các ví dụ dưới đây, cột A chứa họ tên đầy đủ từ ô A2, cột B là Họ đệm, cột C là Tên.

Cách 1: Tách họ và tên trong Excel không dùng hàm (Flash Fill, Ctrl + E)

Flash Fill nhận ra quy luật từ vài ô bạn gõ mẫu rồi điền tiếp cho cả cột. Đây là cách tách họ tên không dùng hàm nhanh nhất.

Ở ô C2, gõ tên của người ở dòng 2, ví dụ A2 là "Nguyễn Văn An" thì gõ An. Nhấn Enter.
Ở ô C3, bắt đầu gõ tên của dòng 3. Excel sẽ hiện chữ mờ gợi ý cho cả cột, nhấn Enter để nhận.
Nếu không thấy gợi ý, chọn ô C3 rồi nhấn Ctrl + E, hoặc vào Data > Flash Fill.
Làm tương tự ở cột B: gõ Nguyễn Văn vào B2, rồi nhấn Ctrl + E.

Lưu ý:

Flash Fill chỉ điền một lần. Sửa họ tên ở cột A thì cột B, C không tự đổi theo.
Nếu Flash Fill không chạy, có thể tính năng đã bị tắt. Vào File > Options > Advanced, tích Automatically Flash Fill, bấm OK rồi mở lại Excel.
Flash Fill có từ Excel 2013. Excel 2010 không có Flash Fill, hãy dùng Cách 2 hoặc Cách 3.

Cách 2: Tách họ tên bằng Find & Replace (Ctrl + H)

Cách này dùng ký tự đại diện * (một chuỗi ký tự bất kỳ) để xóa phần trước dấu cách cuối cùng. Chạy được trên mọi phiên bản Excel, kể cả Excel 2010.

Sao chép cột A sang cột C.
Chọn cột C, nhấn Ctrl + H (hoặc Home > Find & Select > Replace).
Ở ô Find what, gõ dấu sao rồi một dấu cách: * . Để trống ô Replace with.
Bấm Replace All. Cột C chỉ còn lại tên.
Ở ô B2, nhập =TRIM(LEFT(A2,LEN(A2)-LEN(C2))) để lấy họ đệm, rồi kéo xuống.

Nếu gõ ngược thành dấu cách rồi dấu sao ( *), Excel sẽ xóa mọi thứ sau dấu cách đầu tiên và chỉ giữ lại họ. Mẹo này tiện khi bạn cần tách riêng họ.

Lưu ý: làm trên cột đã sao chép, đừng làm trực tiếp trên cột họ tên gốc. Lỡ tay thì nhấn Ctrl + Z để hoàn tác.

Cách 3: Tách họ và tên trong Excel bằng hàm (mọi phiên bản)

Dùng hàm thì kết quả luôn khớp với cột A: sửa họ tên, cột tách tự cập nhật. Các hàm RIGHT, LEFT, MID, LEN, FIND, SUBSTITUTE, REPT, TRIM đều có trong Excel 2010, 2016, 2019 và mới hơn.

Công thức lấy tên (chữ cuối cùng)

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Cách hoạt động:

SUBSTITUTE(A2," ",REPT(" ",100)) thay mỗi dấu cách bằng 100 dấu cách, đẩy các chữ cách xa nhau.
RIGHT(...,100) lấy 100 ký tự cuối, chắc chắn chỉ chứa chữ cuối cùng và một đống dấu cách.
TRIM xóa dấu cách thừa, còn lại đúng tên.

Ví dụ "Tôn Nữ Thị Minh Châu" cho kết quả "Châu". Số 100 đủ dùng cho tên dài dưới 100 ký tự.

Công thức lấy họ đệm

Khi đã có tên ở C2:

=TRIM(LEFT(A2,LEN(A2)-LEN(C2)))

LEN(A2)-LEN(C2) là số ký tự phía trước tên. LEFT lấy phần đó, TRIM bỏ dấu cách ở cuối.

Tách riêng họ, tên đệm và tên thành 3 cột

Nếu cần 3 cột riêng (D là Họ, E là Đệm, C vẫn là Tên):

Họ: =LEFT(A2,FIND(" ",A2)-1)
Đệm: =TRIM(MID(A2,FIND(" ",A2),LEN(A2)-LEN(D2)-LEN(C2)))

Với "Nguyễn Văn An", D2 là "Nguyễn", E2 là "Văn". Với tên chỉ 2 chữ như "Lê Na", cột đệm để trống.

Kéo nút điền ở góc dưới phải ô xuống hết danh sách để áp dụng cho các dòng còn lại.

Cách 4: Dùng TEXTAFTER, TEXTBEFORE, TEXTSPLIT (Microsoft 365, Excel 2024)

Nếu bạn dùng Excel trong Microsoft 365 hoặc Excel 2024, có ba hàm mới giúp công thức ngắn hơn hẳn:

Tên: =TEXTAFTER(A2," ",-1)
Họ đệm: =TEXTBEFORE(A2," ",-1)
Họ: =TEXTBEFORE(A2," ")

Số -1 ở đối số instance_num nghĩa là tìm từ cuối chuỗi lên, tức lấy theo dấu cách cuối cùng. Theo Microsoft, số âm sẽ đếm vị trí dấu phân cách từ cuối văn bản.

Hàm TEXTSPLIT tách mỗi chữ ra một cột:

=TEXTSPLIT(A2," ")

Với "Nguyễn Văn An", kết quả tràn sang 3 ô: Nguyễn, Văn, An. Vì tên tiếng Việt có số chữ khác nhau, các dòng sẽ ra số cột khác nhau, nên TEXTSPLIT hợp để xem nhanh hơn là để lập danh sách chuẩn.

Ba hàm này không có trong Excel 2021, 2019, 2016 và 2010. Mở file trên các bản đó, ô sẽ báo lỗi #NAME?.

Cách 5: Tách họ tên bằng Text to Columns

Text to Columns tách nội dung theo dấu cách thành nhiều cột, có từ các bản Excel cũ.

Chèn đủ cột trống bên phải cột họ tên, vì kết quả sẽ ghi đè lên các cột kế bên.
Chọn cột họ tên.
Vào tab Data, nhóm Data Tools, bấm Text to Columns.
Chọn Delimited, bấm Next.
Tích Space. Khung Data preview cho thấy cách tách. Bấm Next.
Ở ô Destination, chọn ô bắt đầu ghi kết quả (ví dụ $B$2) để giữ nguyên cột gốc.
Bấm Finish.

Nhược điểm với tên tiếng Việt: mỗi chữ thành một cột, nên tên 4 chữ sẽ lấn sang cột của tên 3 chữ. Cách này chỉ hợp khi mọi tên có cùng số chữ. Excel cho web không có Text to Columns.

Lỗi thường gặp khi tách họ và tên trong Excel

Kết quả bị lệch, thừa chữ: dữ liệu có dấu cách thừa ở đầu, cuối hoặc hai dấu cách liền nhau. Tạo cột phụ =TRIM(A2) để làm sạch rồi tách từ cột đó. Lưu ý TRIM không xóa dấu cách không ngắt (non-breaking space) hay gặp khi sao chép từ web.
Lỗi #VALUE! ở công thức lấy họ: ô chỉ có một chữ, không có dấu cách để FIND tìm. Bọc công thức bằng IFERROR, ví dụ =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2).
Lỗi #NAME?: dùng TEXTAFTER, TEXTBEFORE trên bản Excel cũ. Chuyển sang công thức ở Cách 3.
Công thức báo sai cú pháp: máy đặt định dạng vùng Việt Nam phải dùng dấu chấm phẩy, ví dụ =TEXTAFTER(A2;" ";-1).
Flash Fill tách sai vài dòng: gõ thêm mẫu ở dòng bị sai rồi nhấn Ctrl + E lại, Excel sẽ học thêm quy luật.
Replace All xóa mất cả cột: gõ thiếu dấu cách sau dấu *. Nhấn Ctrl + Z rồi làm lại.

Sau khi tách xong, bạn có thể sắp xếp tên theo ABC trong Excel theo cột Tên, hoặc dùng hàm VLOOKUP trong Excel để ghép thông tin từ bảng khác.

Câu hỏi thường gặp

Tách họ và tên trong Excel 2010 dùng cách nào?

Excel 2010 không có Flash Fill và các hàm TEXTAFTER, TEXTBEFORE. Hãy dùng Find & Replace (Cách 2) hoặc công thức RIGHT, SUBSTITUTE, LEFT, LEN ở Cách 3.

Excel 2016 có hàm TEXTSPLIT không?

Không. Theo Microsoft, TEXTSPLIT chỉ có trong Excel cho Microsoft 365 và Excel 2024 (Windows và Mac). Excel 2016 vẫn dùng được Flash Fill và các hàm cũ.

Làm sao xóa cột họ tên gốc mà không mất kết quả?

Kết quả công thức phụ thuộc cột gốc. Hãy chọn các cột kết quả, sao chép, rồi dán bằng Paste Special > Values để chuyển thành giá trị trước khi xóa cột gốc.

Muốn ghép họ và tên lại thành một cột thì làm thế nào?

Dùng =B2&" "&C2, trong đó B2 là họ đệm, C2 là tên. Dấu " " thêm một dấu cách ở giữa.

Tách họ tên có dấu tiếng Việt có bị lỗi font không?

Không, nếu dữ liệu dùng font Unicode (Arial, Times New Roman...). Nếu file cũ gõ bằng bảng mã TCVN3 hoặc VNI, ký tự có dấu có thể hiển thị sai, nên chuyển dữ liệu sang Unicode trước khi tách.