Trong môi trường công việc, dữ liệu tên người thường được lưu trữ dưới dạng một chuỗi duy nhất (ví dụ: “Nguyễn Văn A”). Khi cần phân loại, thống kê hoặc phân tích dữ liệu, việc tách riêng phần họ (surname) và tên đệm (middle name) ra thành các cột riêng biệt sẽ giúp công việc trở nên chính xác và hiệu quả hơn. Dưới đây là một hướng dẫn chi tiết, từ những phương pháp đơn giản nhất dùng công thức cơ bản, tới những cách tiên tiến hơn như Power Query, VBA và macro, để bạn có thểминать tách tên họ và tên đệm trong Excel một cách nhanh chóng và chính xác.
Có thể bạn quan tâm: Cách Đánh Số Thứ Tự Không Liên Tục Trong Excel: Hướng Dẫn Chi Tiết
1. Hiểu rõ cấu trúc tên tiếng Việt
Trước khi áp dụng công thức, chúng ta cần nắm rõ quy ước đặt tên trong tiếng Việt:
| Thành phần | Thông thường | Ví dụ |
|---|---|---|
| Họ | Đặt trước thành phần còn lại | Nguyễn, Trần, Lê |
| Tên đệm | Hoặc “tên giữa” | Văn, Thị, Hiếu |
| Tên | Thành phần cuối cùng | A, B, C |
- Có trường hợp có nhiều tên đệm (ví dụ: “Nguyễn Văn An Hòa”).
- Có trường hợp không có tên đệm (ví dụ: “Nguyễn A”).
- Các từ “Thị”, “Văn”, “Anh”, “Bình” thường được coi là tên đệm.
Hiểu được các khía cạnh này giúp chúng ta chọn công thức phù hợp.
Có thể bạn quan tâm: Cách Chuyển File Pdf Sang Excel Không Cần Phần Mềm
2. Phương pháp 1: Sử dụng công thức Excel cơ bản
2.1. Tách họ (sang cột A)
Giả sử tên đầy đủ nằm trong ô B2. Công thức tách họ:
=TRIM(LEFT(B2, FIND(" ", B2)-1))
FIND(" ", B2)tìm vị trí khoảng trắng đầu tiên.LEFT(B2, …)lấy phần trước dấu cách.TRIMloại bỏ dấu cách thừa.
Nếu ô có nhiều dấu cách, công thức trên vẫn trả về phần đầu tiên (đều là họ).
2.2. Tách tên (sang cột C)
=TRIM(RIGHT(B2, LEN(B2)-FIND("@", SUBSTITUTE(B2, " ", "@", LEN(B2)-LEN(SUBSTITUTE(B2, " ", ""))))))
Công thức này:
SUBSTITUTE(..., " ", "@", n)thay dấu cách cuối cùng bằng ký tự đặc biệt “@”.FIND("@", …)lấy vị trí của ký tự đó.RIGHTlấy phần sau dấu cách cuối cùng (đó là tên).
2.3. Tách tên đệm (sang cột D)
Sau khi đã có họ và tên, tên đệm là phần còn lại giữa họ và tên:
=TRIM(MID(B2, FIND(" ", B2)+1, FIND("@", SUBSTITUTE(B2, " ", "@", LEN(B2)-LEN(SUBSTITUTE(B2, " ", ""))))-FIND(" ", B2)-1))
Giải thích:
MIDlấy đoạn giữa họ và tên.FIND(" ", B2)+1là vị trí bắt đầu tên đệm.FIND("@", …)-FIND(" ", B2)-1là độ dài của đoạn tên đệm.
2.4. Kiểm tra và xử lý lỗi
- Nếu ô
B2rỗng hoặc chỉ chứa một từ, công thức sẽ trả về lỗi. Bạn có thể sử dụngIFERROR:
=IFERROR(TRIM(LEFT(B2, FIND(" ", B2)-1)), "")
- Đối với dữ liệu không tuân theo quy tắc chuẩn (ví dụ: “Bình Ngô Thị Huyền”), hãy lưu ý rằng công thức này sẽ tách “Bình” như là họ, điều này không chính xác. Để xử lý dữ liệu đa dạng hơn, hãy xem phần Phương pháp 3.
Có thể bạn quan tâm: Cách Tạo Và Quản Lý Form Giấy Tomy Thường Dùng Trong Word Và Excel
3. Phương pháp 2: Dùng Power Query (Get & Transform)
Power Query là công cụ mạnh mẽ để thao tác dữ liệu, hỗ trợ xử lý văn bản linh hoạt hơn.
3.1. Nhập dữ liệu vào Power Query
- Chọn vùng dữ liệu (cột chứa tên đầy đủ).
- Vào tab Data → Get Data → From Table/Range.
- Nếu dữ liệu chưa là bảng, Excel sẽ tạo bảng cho bạn.
3.2. Tách theo dấu cách
- Trên Power Query, chọn cột tên.
- Vào Transform → Split Column → By Delimiter → Space → At the left-most delimiter.
- Bước nàytsx “Nguyễn Văn A” thành “Nguyễn” và “Văn A”.
- Lặp lại: chọn cột “Văn A”, Split Column → By Delimiter → Space → At the right-most delimiter.
- Bây giờ bạn có “Nguyễn”, “Văn”, “A”.
3.3. Xử lý tên đệm nhiều từ
- Nếu có nhiều tên đệm (ví dụ: “Thị Bảo Anh”), bạn có thể Merge Columns:
- Chọn các cột tên đệm (trừ cột tên cuối cùng), chọn Merge Columns → Space → đặt tên “Tên đệm”.
- Hoặc dùng Custom Column với M code:
=Text.Combine(List.Skip(List.FirstN(Text.Split(Tên, " "), List.Count(Text.Split(Tên, " "))-1), 1), " ")
3.4. Hoàn thiện và tải dữ liệu về Excel
Sau khi hoàn thành, chọn Close & Load → Close & Load To → Table. Dữ liệu sẽ xuất ra một bảng mới với các cột họ, tên đệm, tên.
Lưu ý: Power Query không thể tự động nhận dạng tên đệm “Thị” hay “Văn” mà không có dấu cách. Nếu dữ liệu không tuân theo cấu trúc chuẩn, bạn cần xử lý thủ công hoặc dùng VBA.
4. Phương pháp 3: Sử dụng VBA (Macro)
VBA cho phép tùy biến linh hoạt, đặc biệt hữu ích khi xử lý dữ liệu lớn hoặc quy tắc phức tạp.
4.1. Macro tách tên theo quy tắc chuẩn
Sub SplitName() Dim rng As Range, cell As Range Dim parts() As String Dim i As Long, lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B2:B" & lastRow) 'Giả sử dữ liệu ở cột B For Each cell In rng If Trim(cell.Value) <> "" Then parts = Split(Trim(cell.Value), " ") If UBound(parts) >= 2 Then cell.Offset(0, -1).Value = parts(0) 'Họ cell.Offset(0, 1).Value = parts(UBound(parts)) 'Tên If UBound(parts) > 2 Then Dim middle As String For i = 1 To UBound(parts) - 1 middle = middle & parts(i) & " " Next i cell.Offset(0, 0).Value = Trim(middle) 'Tên đệm Else cell.Offset(0, 0).Value = "" 'Không có tên đệm End If End If End If Next cell
End Sub
- Giải thích: Macro chia chuỗi bằng
Split, sau đó gán phần đầu là họ, phần cuối là tên, phần còn lại là tên đệm. - Để chạy macro, nhấn
Alt + F8, chọnSplitName, rồiRun.
4.2. Macro nâng cao: nhận diện tên đệm phổ biến
Có thể bạn quan tâm: Cách Tìm Sheet Nhanh Trong File Excel Có Nhiều Sheet
Đôi khi dữ liệu không tuân theo quy tắc chuẩn (ví dụ: “Bình Ngô Thị Huyền”). Bạn có thể lập danh sách các từ thường là tên đệm, và dùng chúng để xác định:
Dim middleWords As Variant
middleWords = Array("Thị", "Văn", "Anh", "Bình", "Hải", "Quang", "Huyền") Function IsMiddleWord(word As String) As Boolean Dim i As Long For i = LBound(middleWords) To UBound(middleWords) If StrComp(word, middleWords(i), vbTextCompare) = 0 Then IsMiddleWord = True Exit Function End If Next i IsMiddleWord = False
End Function
Bằng cách kết hợp hàm IsMiddleWord, bạn có thể viết logic phức tạp hơn để tách tên đệm một cách chính xác.
5. Phương pháp 4: Sử dụng hàm TEXTJOIN (Excel 2026/365)
Nếu bạn muốn giữ nguyên cột gốc và tạo cột phụ mới, hàm TEXTJOIN rất tiện dụng.
5.1. Tách tên đệm
=IF(COUNTIF(A2:D2, "")=3, "", TEXTJOIN(" ", TRUE, IF(ROW(A2:D2)=ROW(A2)+1ಇ, A2:D2, "")))
Công thức này sử dụng
TEXTJOINđể gộp các phần giữa họ và tên, bỏ qua các ô trống.
5.2. Sửa lỗi khi không có tên đệm
=IF(COUNTIF(A2:D2, परेको)="", "", TEXTJOIN(" ", TRUE, A2:D2))
6. Kiểm tra và làm sạch dữ liệu
Sau khi tách tên, bạn cần kiểm tra:
- Độ dài: Họ thường ngắn hơn 4 ký tự, tên đệm có thể dài hơn.
- Kiểm tra chữ hoa: Sử dụng
PROPERđể chuẩn hóa:
=PROPER(A2) 'Họ
=PROPER(B2) 'Tên đệm
=PROPER(C2)ętr
- Loại bỏ自己的: Nếu có ký tự đặc biệt hoặc dấu cách thừa, dùng
TRIM,CLEAN.
7. Thực hành: Tạo bảng tách tên tự động
- Bước 1: Chuẩn bị dữ liệu trong cột
A(tên đầy đủ). - Bước 2: Tạo cột
B– Họ:
=IFERROR(TRIM(LEFT(A2, FIND(" ", A2)-1)), "")
- Bước 3: Tạo cột
C– Tên:
=IFERROR(TRIM(RIGHT(A2, LEN(A2)-FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2)-LEN(SUBSTITUTE(A2, " ", "")))))), "")
- Bước 4: Tạo cột
D– Tên đệm (nếu có):
=IFERROR(TRIM(MID(A2, FIND(" ", A2)+1, FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))))-FIND(" ", A2)-1)), "")
- Bước 5: Kiểm tra bằng
PROPER:
=PROPER(B2) 'HọHunter
=PROPER(D2) 'Tên đệm fiancé(C2) 'Tên
- Bước 6: Sử dụng
IFERRORđể tránh lỗi khi tên không tuân quy tắc.
8. Ứng dụng thực tế
8.1. Phân tích dữ liệu khách hàng
Nếu hệ thống CRM chứa tên khách hàng không tách, việc phân loại họ, tên đệm giúp:
- Tìm kiếm nhanh: Tìm kiếm theo họ (chủ yếu dùng trong báo cáo).
- Tạo email ملکی: Dựa vào họ và tên để tạo địa chỉ email chuẩn.
- Tính thống kê: Đếm số lượng khách hàng theo họ (đánh dấu nhóm).
8.2. Xử lý dữ liệu nhân viên
- Bảng lương: Tách tên để chuẩn hóa đối chiếu dữ liệu.
- Bản ghi công việc: Đánh dấu nhân viên theo họ để dễ dàng sắp xếp.
8.3. Phân tích dữ liệu giáo dục
- Sinh viên: Phân loại theo họ để thống kê số lượng sinh viên trong từng khoa.
- Đơn đăng ký: Kiểm tra tính hợp lệ của tên, tránh nhập sai.
9. Thủ thuật nâng cao
9.1. Dùng hàm TEXTSPLIT (Excel 2026/365)
Nếu bạn có phiên bản mới nhất, TEXTSPLIT hỗ trợ chia chuỗi theo ký tự:
=TEXTSPLIT(A2, " ", , 1) 'Phân tách toàn bộ từ
Kết hợp với INDEX:
=INDEX(TEXTSPLIT(A2, " "), 1) 'Họ
=INDEX(TEXTSPLIT(A2, " "), COUNTA(TEXTSPLIT(A2, " "))) 'Tên
=TEXTJOIN(" ", TRUE, INDEX(TEXTSPLIT(A2, " "), 2, COUNTA(TEXTSPLIT(A2, " "))-1)) 'Tên đệm
9.2. Xử lý dữ liệu đa ngôn ngữ
Nếu có dữ liệu tiếng Anh (“Smith John”), bạn có thể viết macro để phân biệt từ ngữ:
If InStr(1, parts(0), " ") > 0 Then 'Xử lý đặc biệt
End If
9.3. Sử dụng hàm FILTERXML
Đôi khi dữ liệu có dấu phẩy hoặc dạng XML, FILTERXML giúp tách:
=FILTERXML("<t><s>" & SUBSTITUTE(A2, " ", "</s><s>") & "</s></t>", "//s2")
10. Kết luận
Việc tách tên họ và tên đệm trong Excel không chỉ giúp dữ liệu trở nên rõ ràng hơn, mà còn mở ra nhiều khả năng phân tích và xử lý dữ liệu hiệu quả. Tùy thuộc vào quy mô và tính chất dữ liệu, bạn có thể lựa chọn:
- Công thức cơ bản (đơn giản, nhanh).
- Power Query (đối với dữ liệu lớn, linh hoạt).
- VBA/Macro (đối với quy tắc tùy chỉnh, tự động hoá).
- Hàm mới (
TEXTJOIN,TEXTSPLIT) (đối với Excel 365/2026).
Bằng cách kết hợp các phương pháp trên, bạn sẽ có thể tách tên người một cách chính xác, giảm thiểu lỗi, và tăng tính nhất quán trong toàn bộ hệ thống dữ liệu của mình. Hãy thử áp dụng ngay hôm nay để cảm nhận sự khác biệt!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.