Danh sách nhân viên, học viên hay khách hàng thường ghi cả họ và tên trong một ô, ví dụ "Nguyễn Văn An". Khi cần sắp xếp danh sách theo tên hoặc cần chữ "An" để ghép vào lời chào trong email, bạn phải tách chữ cuối cùng ra một cột riêng, và việc gõ tay với vài trăm người vừa lâu vừa dễ sót.

Đây là bài thứ hai trong chuỗi 3 bài về tách họ tên, sau bài cách tách họ trong Excel. Bài này hướng dẫn cách tách tên 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 hàm mới của Excel 365 và Flash Fill. Các công thức đều chạy đúng với họ tên 2, 3, 4 hay 5 chữ, và kết quả trong bài đã được kiểm tra bằng Excel 365.

Vì sao tách tên khó hơn tách họ?

Họ đứng trước dấu cách đầu tiên, nên bạn chỉ cần hàm FIND tìm vị trí dấu cách đó rồi dùng hàm LEFT cắt phần phía trước. Tên lại đứng sau dấu cách cuối cùng, và việc này gặp hai trở ngại:

  • Hàm FIND chỉ tìm được dấu cách đầu tiên. Các phiên bản Excel trước Excel 365 không có hàm nào tìm thẳng dấu cách cuối cùng.

  • Mỗi người có số chữ khác nhau. "Lê Na" có 1 dấu cách, còn "Nguyễn Lê Hoàng Bảo Ngọc" có 4 dấu cách, nên bạn không thể cắt ở một vị trí cố định.

Dù khó tách hơn, cột Tên lại rất hay được dùng. Danh sách lớp và danh sách nhân viên ở Việt Nam thường được sắp xếp theo tên ABC, trùng tên thì xét tới họ. Email và thư mời cũng thường chào người nhận bằng tên: khi tên nằm ở ô B2, công thức ="Chào "&B2&"," dùng dấu & để nối chữ và cho ra "Chào An,".

Dữ liệu mẫu cần được làm sạch bằng TRIM trước khi tách

Cả chuỗi bài dùng chung một bảng dữ liệu để bạn làm theo. Bạn gõ tiêu đề "Họ và tên" vào ô A1, rồi gõ 8 họ tên sau vào các ô A2 tới A9:

Ô

Họ và tên

Số chữ

A2

Nguyễn Văn An

3

A3

Lê Na

2

A4

Trần Thị Thu Hà

4

A5

Phạm Minh Tuấn

3

A6

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

4

A7

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

5

A8

Võ Thanh Tùng (gõ kèm dấu cách thừa)

3

A9

Đỗ Khánh Linh

3

Ô A8 được cố ý gõ thừa dấu cách để giống dữ liệu thật: bạn gõ 2 dấu cách, chữ "Võ", 3 dấu cách, chữ "Thanh", 2 dấu cách, chữ "Tùng" và 1 dấu cách ở cuối. Dữ liệu dán từ phần mềm khác hay mắc lỗi này, và lỗi rất khó nhìn thấy bằng mắt.

Mọi cách tách tên đều dựa vào dấu cách, nên dấu cách thừa làm kết quả sai. Hàm TRIM xóa 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 một. Trong ảnh dưới đây, bạn gõ =TRIM(A2) vào ô C2 rồi kéo xuống C9, và ô C8 cho ra "Võ Thanh Tùng". Cột B dùng =LEN(A2) để đếm số ký tự, nên bạn thấy ô A8 dài 19 ký tự trước khi làm sạch và còn 13 ký tự sau khi làm sạch. Bạn có thể đọc thêm về hàm LEN trong bài đếm ký tự trong Excel.

Bảng dữ liệu mẫu 8 họ tên từ 2 đến 5 chữ, hàm TRIM làm sạch ô có dấu cách thừa
Bảng mẫu có họ tên từ 2 đến 5 chữ, TRIM đưa ô A8 từ 19 ký tự về 13 ký tự

Cột D đếm số chữ bằng =LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1, tức số dấu cách cộng thêm 1. Các cột B, C, D ở đây chỉ để bạn quan sát dữ liệu. Mỗi cách bên dưới đặt kết quả bắt đầu từ cột B, nên bạn có thể làm mỗi cách trên một sheet riêng có cùng dữ liệu ở cột A.

Cách 1: Công thức TRIM, RIGHT, SUBSTITUTE, REPT tách tên trên mọi phiên bản

Đây là công thức tách tên phổ biến nhất và dùng được trên mọi phiên bản Excel. Bạn gõ tiêu đề "Tên" vào ô B1, gõ công thức sau vào ô B2, rồi kéo ô vuông nhỏ ở góc dưới bên phải ô B2 xuống tới B9:

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

Cột B cho ra An, Na, Hà, Tuấn, Châu, Ngọc, Tùng và Linh, đúng với cả 8 người. Công thức trông dài nhưng chỉ gồm 4 hàm lồng vào nhau:

  • REPT lặp lại một chuỗi nhiều lần, nên REPT(" ",100) tạo ra 100 dấu cách liền nhau.

  • SUBSTITUTE thay chữ cũ bằng chữ mới, ở đây là thay mỗi dấu cách bằng 100 dấu cách.

  • RIGHT lấy một số ký tự tính từ bên phải, ở đây là 100 ký tự cuối.

  • TRIM bên trong làm sạch họ tên trước khi tách, còn TRIM bên ngoài xóa các dấu cách còn sót quanh tên.

Công thức kéo giãn khoảng cách giữa các chữ

Bạn có thể hình dung công thức này như việc kéo giãn khoảng cách giữa các chữ. Mỗi dấu cách được kéo dài thành một khoảng trống 100 ký tự, nên tên bị đẩy ra xa hẳn các chữ phía trước. Lúc này, bạn chỉ cần cắt 100 ký tự cuối là lấy được trọn tên. Ảnh dưới đây minh họa 4 bước với "Nguyễn Văn An" ở ô A2.

Minh họa công thức tách tên kéo giãn mỗi dấu cách thành 100 dấu cách rồi lấy 100 ký tự cuối
Công thức kéo giãn khoảng cách giữa các chữ, nhờ đó tên đứng riêng trong 100 ký tự cuối
  1. TRIM(A2) làm sạch họ tên. Ô A2 vốn đã sạch nên vẫn dài 13 ký tự.

  2. SUBSTITUTE thay 2 dấu cách bằng 2 khoảng trống, mỗi khoảng dài 100 dấu cách. Chuỗi mới dài 211 ký tự, gồm 11 chữ cái và 200 dấu cách.

  3. RIGHT lấy 100 ký tự cuối, gồm chữ "An" và 98 dấu cách đứng trước. Khoảng trống cuối cùng dài đúng 100 ký tự, nên phần cắt ra không thể chạm tới chữ "Văn".

  4. TRIM xóa 98 dấu cách ở đầu và trả về "An".

Họ tên dài hay ngắn không làm thay đổi kết quả, vì tên luôn đứng sau khoảng trống cuối cùng. Với "Nguyễn Lê Hoàng Bảo Ngọc" ở ô A7, chuỗi sau khi giãn dài 420 ký tự, nhưng 100 ký tự cuối vẫn chỉ gồm 96 dấu cách và chữ "Ngọc". Với ô A8, TRIM bên trong đã xóa dấu cách thừa trước khi giãn, nên kết quả vẫn là "Tùng".

Công thức TRIM, RIGHT, SUBSTITUTE, REPT tách tên cho 8 họ tên từ 2 đến 5 chữ trong Excel
Cách 1 cho ra đúng tên của cả 8 người, kể cả ô có dấu cách thừa

Vì sao công thức dùng con số 100?

Con số 100 chỉ cần lớn hơn hoặc bằng số ký tự của tên. Nếu con số nhỏ hơn, RIGHT cắt thiếu và tên bị mất chữ cái đầu, như bảng so sánh dưới đây:

Họ và tên

Dùng số 3

Dùng số 100

Nguyễn Văn An

An

An

Phạm Minh Tuấn

uấn

Tuấn

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

gọc

Ngọc

Công thức dùng số 3 là =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",3)),3)). Tên "An" có 2 ký tự nên vẫn đúng, còn "Tuấn" và "Ngọc" có 4 ký tự nên bị cắt. Để khỏi đếm độ dài từng tên, bạn chọn một con số lớn hơn họ tên dài nhất trong danh sách, vì tên luôn ngắn hơn cả họ tên. Họ tên dài nhất trong bảng mẫu chỉ có 24 ký tự, nên 100 là con số an toàn và dễ nhớ. Với những đoạn chữ rất dài như địa chỉ, bạn thay 100 bằng LEN(A2) để con số tự lớn theo từng ô: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(A2))),LEN(A2))).

Cách 2: Công thức tìm dấu cách cuối cùng rồi lấy phần phía sau

FIND không tìm được dấu cách cuối, nhưng SUBSTITUTE có thể điền thêm giá trị thứ tư trong ngoặc, gọi là instance_num, để chỉ thay lần xuất hiện thứ mấy. Nếu họ tên có 3 dấu cách, bạn thay riêng dấu cách thứ 3 bằng dấu #, rồi dùng FIND tìm vị trí dấu # đó. Để dễ theo dõi, bạn gõ các công thức sau vào dòng 2, chọn vùng B2:F2 rồi kéo xuống tới dòng 9:

  1. Ô B2 làm sạch họ tên: =TRIM(A2).

  2. Ô C2 đếm số dấu cách: =LEN(B2)-LEN(SUBSTITUTE(B2," ","")). Con số này cũng là thứ tự của dấu cách cuối cùng.

  3. Ô D2 thay dấu cách cuối cùng bằng dấu #: =SUBSTITUTE(B2," ","#",C2).

  4. Ô E2 tìm vị trí dấu #: =FIND("#",D2).

  5. Ô F2 lấy các ký tự đứng sau dấu #: =RIGHT(B2,LEN(B2)-E2).

Tách tên bằng cách đếm dấu cách, thay dấu cách cuối bằng dấu # rồi dùng FIND và RIGHT
Cách 2 thay dấu cách cuối bằng dấu # để FIND tìm được vị trí của nó

Ở dòng 6, "Hoàng Ngọc Bảo Châu" dài 19 ký tự và còn 16 ký tự khi xóa hết dấu cách, nên ô C6 ra 3. SUBSTITUTE thay dấu cách thứ 3 bằng dấu # và tạo ra "Hoàng Ngọc Bảo#Châu". FIND thấy dấu # ở vị trí 15, nên tên là 19 - 15 = 4 ký tự cuối, tức "Châu". Với "Lê Na" ở dòng 3, dấu # nằm ở vị trí 3 và RIGHT lấy 5 - 3 = 2 ký tự là "Na".

Khi đã hiểu các bước, bạn gộp các cột phụ thành một công thức trong ô B2:

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

Công thức này dài hơn Cách 1, đổi lại cột E cho bạn biết vị trí dấu cách cuối cùng. Mọi ký tự đứng trước vị trí đó là họ và tên lót, nên con số này có ích khi bạn đọc tới bài tách tên lót trong Excel. Ký tự thay thế phải là ký tự không xuất hiện trong họ tên, và dấu # đáp ứng được điều kiện này.

Cách 3: Hàm TEXTAFTER tách tên bằng công thức ngắn trên Excel 365

Trên Excel 365 và Excel 2024, hàm TEXTAFTER lấy phần đứng sau một ký tự phân cách. Cú pháp rút gọn là =TEXTAFTER(text, delimiter, [instance_num]), trong đó text là chuỗi gốc, delimiter là ký tự phân cách và instance_num cho biết bạn tính theo ký tự phân cách thứ mấy. Số -1 nghĩa là dấu cách đầu tiên tính từ phải sang, tức dấu cách đứng ngay trước tên. Bạn gõ vào ô B2 rồi kéo xuống B9:

=TEXTAFTER(TRIM(A2)," ",-1)

Cột B cho đúng 8 tên như hai cách trước. Ảnh có thêm ô A10 chỉ có một chữ "Hoa" và ô A11 để trống.

Hàm TEXTAFTER với số -1 tách tên trên Excel 365, IFERROR xử lý ô một chữ và ô trống
TEXTAFTER với số -1 lấy phần sau dấu cách cuối, IFERROR xử lý ô một chữ và ô trống

Với ô một chữ, TEXTAFTER không tìm thấy dấu cách nên báo lỗi #N/A. Với ô trống, TEXTAFTER báo lỗi #VALUE!. Hàm IFERROR trả về một giá trị thay thế khi công thức bên trong bị lỗi, nên công thức sau xử lý được cả hai trường hợp:

=IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2))

Bạn gõ công thức này vào ô C2 rồi kéo xuống C11. Cột C trả về "Hoa" cho ô A10 và ô trống cho ô A11. Excel 2021 trở về trước chưa có TEXTAFTER và sẽ báo lỗi #NAME?, nên với file gửi cho nhiều người, bạn nên dùng Cách 1.

Cách 4: Flash Fill (Ctrl + E) tách tên mà không cần công thức

Flash Fill có từ Excel 2013. Tính năng này nhìn ví dụ bạn gõ ở dòng đầu, tự đoán quy luật rồi điền cho các dòng còn lại:

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

  2. Bạn gõ tên của người đầu tiên vào ô B2, tức chữ "An".

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

Với bảng mẫu, Flash Fill điền đúng cả 8 tên, kể cả ô A8 có dấu cách thừa. Tuy vậy, kết quả của Flash Fill là chữ cố định, nên khi bạn sửa hoặc thêm họ tên ở cột A, cột Tên không tự cập nhật. Flash Fill cũng có thể đoán sai khi dữ liệu không đều, nên bạn cần lướt kiểm tra kết quả.

Tên kép như "Bảo Châu", "Bảo Ngọc" cần xử lý riêng

Một số người có tên kép gồm 2 chữ, ví dụ chị Hoàng Ngọc Bảo Châu có thể muốn được gọi là "Bảo Châu". Cả 4 cách ở trên chỉ lấy chữ cuối, và không công thức nào tự biết chữ "Bảo" là tên lót hay một phần của tên, vì điều này phụ thuộc vào cách gọi của từng người. Nếu công ty quy định lấy 2 chữ cuối cho người có tên kép, bạn dùng một trong hai công thức sau:

  • Trên Excel 365 và Excel 2024, bạn đổi số -1 thành -2: =TEXTAFTER(TRIM(A2)," ",-2).

  • Trên mọi phiên bản, bạn đổi số 100 cuối cùng của Cách 1 thành 200: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),200)). Khi đó, 200 ký tự cuối chứa trọn hai chữ cuối và khoảng trống giữa chúng, rồi TRIM gộp khoảng trống đó thành một dấu cách.

Lấy 2 chữ cuối cho tên kép Bảo Châu, Bảo Ngọc bằng TEXTAFTER và công thức cho Excel cũ
Công thức lấy 2 chữ cuối đúng với tên kép nhưng sai với họ tên chỉ có 2 chữ

Cả hai công thức đều lấy đúng "Bảo Châu" ở dòng 6 và "Bảo Ngọc" ở dòng 7. Tuy vậy, bạn không nên dùng công thức này cho cả cột, vì kết quả sai với những người còn lại:

  • Người có tên một chữ bị lấy thêm tên lót, ví dụ "Nguyễn Văn An" cho ra "Văn An".

  • Họ tên 2 chữ như "Lê Na" chỉ có một dấu cách, nên TEXTAFTER với số -2 báo lỗi #N/A, còn công thức 200 ký tự trả về nguyên "Lê Na".

Cách làm thực tế là dùng công thức lấy 1 chữ cuối cho cả cột, rồi sửa tay riêng những người đã xác nhận có tên kép.

Cột Tên giúp sắp xếp danh sách theo tên ABC kiểu Việt Nam

Phần này thêm hai người để có trường hợp trùng tên: ô A10 là "Bùi Thị Hà" và ô A11 là "Lê Hoàng An". Cột B từ B2 tới B11 vẫn dùng công thức Cách 1.

Lệnh Sort sắp xếp theo tên trên mọi phiên bản Excel

  1. Bạn bấm vào một ô bất kỳ trong bảng, ví dụ ô A2.

  2. Bạn vào thẻ Data, bấm Sort và đánh dấu ô My data has headers để Excel hiểu dòng 1 là tiêu đề.

  3. Ở dòng Sort by, bạn chọn cột Tên với thứ tự A to Z.

  4. Bạn bấm Add Level, chọn cột Họ và tên ở dòng Then by với thứ tự A to Z, rồi bấm OK.

Excel sắp xếp theo tên trước, rồi xét cột Họ và tên cho những người trùng tên. Vì vậy, Lê Hoàng An đứng trước Nguyễn Văn An, và Bùi Thị Hà đứng trước Trần Thị Thu Hà. Công thức ở cột B di chuyển theo từng dòng, nên tên vẫn khớp đúng người. Bạn không nên bôi đen riêng cột Tên rồi sắp xếp, vì cột Tên sẽ bị lệch khỏi cột Họ và tên.

Hàm SORTBY tạo bảng đã sắp xếp trên Excel 365

Excel 365 và Excel 2021 có hàm SORTBY, giúp bạn tạo một bảng đã sắp xếp mà không đụng tới bảng gốc. Bạn gõ vào ô D2:

=SORTBY(A2:B11,B2:B11,1,A2:A11,1)

Công thức lấy vùng A2:B11, sắp theo cột B (Tên) rồi theo cột A (Họ và tên), với số 1 nghĩa là tăng dần từ A tới Z. Bạn chỉ gõ vào một ô, còn Excel tự đổ kết quả ra vùng D2:E11 theo cơ chế spill. Nếu bỏ phần A2:A11,1 ở cuối, người trùng tên giữ thứ tự như bảng gốc, nên Nguyễn Văn An sẽ đứng trước Lê Hoàng An.

Sắp xếp danh sách theo tên rồi theo họ bằng hàm SORTBY trong Excel
SORTBY sắp xếp theo tên trước, người trùng tên được sắp tiếp theo họ tên

Ô D11 vẫn mang dấu cách thừa của ô A8, vì SORTBY chỉ sắp xếp chứ không làm sạch dữ liệu. Bạn nên làm sạch cột A bằng TRIM rồi dán giá trị đè lên cột A trước khi sắp xếp.

Excel sắp xếp chữ có dấu theo thứ tự nào?

Trên máy Windows cài vùng tiếng Anh dùng để kiểm tra bài viết, Excel coi Ă, Â như A, Đ như D, Ô, Ơ như O, Ư như U, và chỉ xét dấu khi hai tên giống hệt nhau sau khi bỏ dấu. Vì vậy, "Ân" đứng trước "Anh" và "Đức" đứng trước "Dung", khác với bảng chữ cái tiếng Việt. Nếu cần đúng thứ tự từ điển, bạn nên kiểm tra lại các tên bắt đầu bằng Ă, Â, Đ, Ô, Ơ, Ư.

Những lỗi bạn thường gặp khi tách tên trong Excel

Bạn thấy

Nguyên nhân

Cách sửa

Cột Tên ra ô trống ở một vài dòng

Ô họ tên có dấu cách thừa ở cuối, trong khi công thức thiếu TRIM bên trong. Ví dụ, =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) ra ô trống ở dòng 8.

Đặt TRIM(A2) bên trong SUBSTITUTE như Cách 1

Ô Tên trông như trống hoặc chữ bị đẩy sang phải

Công thức thiếu TRIM ngoài cùng, nên tên còn dính dấu cách ở phía trước

Bọc thêm TRIM ở ngoài cùng

Tên bị mất chữ cái đầu, ví dụ "Ngọc" thành "gọc"

Con số trong REPT và RIGHT nhỏ hơn độ dài tên

Dùng số 100 hoặc LEN(A2)

Cách 2 báo #VALUE!, TEXTAFTER báo #N/A hoặc #VALUE!

Ô chỉ có một chữ hoặc ô trống

Bọc công thức trong IFERROR(công thức,TRIM(A2))

Cột Tên hiện nguyên cả họ tên 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ã ký tự 160), loại mà TRIM và SUBSTITUTE không coi là dấu cách thường

Thay TRIM(A2) bằng TRIM(SUBSTITUTE(A2,CHAR(160)," "))

Câu hỏi thường gặp về tách tên trong Excel

Có cách nào tách tên mà không cần cột phụ không?

Bạn tách được tên chỉ trong một ô, vì công thức Cách 1 và công thức gộp của Cách 2 đều có TRIM bên trong. Trên Excel 365, =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2)) là công thức ngắn nhất.

Làm sao giữ lại tên và bỏ công thức?

Bạn chọn vùng chứa tên, nhấn Ctrl + C, bấm chuột phải vào chính vùng đó rồi chọn Paste Values, là biểu tượng có số 123. Cột Tên lúc này chỉ còn chữ, nên bạn xóa cột họ tên gốc cũng không mất kết quả.

Excel có tự nhận ra tên kép không?

Excel không tự nhận ra tên kép, vì Excel chỉ tách theo dấu cách. Phần tên kép ở trên có công thức lấy 2 chữ cuối và cách dùng an toàn.

Bài tiếp theo hướng dẫn tách tên lót trong Excel

Phần còn lại của họ tên là tên lót, tức các chữ nằm giữa họ và tên. Tên lót có thể không có như "Lê Na", có một chữ như "Văn" trong "Nguyễn Văn An", hoặc có ba chữ như "Lê Hoàng Bảo" trong "Nguyễn Lê Hoàng Bảo Ngọc". Bài tiếp theo, cách tách tên lót trong Excel, dùng lại chính bảng dữ liệu này để bạn làm tiếp trên cùng file.

Bạn muốn xử lý dữ liệu chữ trong Excel thành thạo hơn?

TRIM, LEN, RIGHT, FIND, SUBSTITUTE và TEXTAFTER là nhóm hàm bạn sẽ dùng mỗi khi cần làm sạch và tách dữ liệu chữ như họ tên, mã hàng hay địa chỉ. Bạn có thể ôn lại nhóm hàm cắt chữ từ nền tảng trong bài hàm LEFT trong Excel.

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.