Tách họ, tên đệm, tên và chuẩn hóa chữ hoa

Vấn đề bạn đang gặp

Bảng nhân viên của bạn gom từ nhiều nơi: có tên viết HOA hết, có tên viết thường hết, và nhiều tên thừa dấu cách ở đầu, ở cuối hoặc giữa các chữ. Bạn cần thêm cột Họ, cột Tên đệm và cột Tên để sắp xếp theo tên, lập danh sách hoặc gửi thư.

Hai việc cần làm theo thứ tự. Trước hết làm sạch tên: TRIM bỏ dấu cách thừa và PROPER viết hoa chữ cái đầu mỗi chữ. Sau đó mới tách, vì Microsoft nhấn mạnh việc tách tên bằng công thức dựa vào vị trí của các dấu cách: một dấu cách thừa ở đầu sẽ làm mọi vị trí lệch đi.

Cách sửa từng bước

Chuẩn hóa họ tên rồi tách Họ, Tên đệm, Tên

1. 1

   Làm gì: Ở một cột phụ gõ =PROPER(TRIM(B2)) rồi sao chép xuống. Từ đây, nếu tên ở B chưa sạch, hãy thay B2 trong các công thức dưới đây bằng ô vừa chuẩn hóa.

   Vì sao: TRIM giữ một dấu cách giữa các chữ và bỏ dấu cách ở đầu, ở cuối. PROPER viết hoa chữ cái đầu mỗi chữ và viết thường phần còn lại. Làm sạch trước thì các bước tách sau dựa trên vị trí dấu cách đúng.

2. 2

   Làm gì: Họ là chữ đầu. Ở H2 gõ =LEFT(B2,SEARCH(" ",B2)-1).

   Vì sao: SEARCH cho biết vị trí của dấu cách đầu tiên. Họ là phần đứng trước dấu cách đó, nên lấy số ký tự bằng vị trí đó trừ 1.

3. 3

   Làm gì: Tên là chữ cuối. Ở J2 gõ =TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2))).

   Vì sao: SUBSTITUTE thay mỗi dấu cách bằng một dãy dấu cách dài bằng cả họ tên (REPT tạo dãy đó). Chữ cuối bị đẩy ra xa chữ trước. RIGHT lấy phần cuối dài bằng cả họ tên, gồm các dấu cách và chữ cuối, rồi TRIM bỏ dấu cách. Cách này đúng dù tên có hai, ba hay bốn chữ.

4. 4Chỗ hay sai

   Làm gì: Tên đệm là phần giữa. Ở I2 gõ =TRIM(MID(B2,LEN(H2)+1,LEN(B2)-LEN(H2)-LEN(J2))).

   Vì sao: MID bắt đầu ngay sau Họ và lấy số ký tự bằng độ dài cả tên trừ độ dài Họ và Tên. Kết quả còn một dấu cách ở đầu hoặc cuối nên bọc TRIM. Người không có tên đệm thì MID chỉ lấy một dấu cách và TRIM cho chữ rỗng.

5. 5

   Làm gì: Sao chép ba cột xuống hết bảng, rồi xem lại các dòng có hai chữ, bốn chữ, họ kép hoặc tên kép.

   Vì sao: Quy ước chữ đầu là Họ, chữ cuối là Tên chỉ đúng với phần lớn người. Các trường hợp đặc biệt cần bạn kiểm tra bằng mắt.

Tự hỏi mình

• Vì sao nên chuẩn hóa họ tên trước khi tách?
• Họ tên hai chữ cho Tên đệm là gì, và công thức cho kết quả đó bằng cách nào?

Excel 2024/365: TEXTBEFORE và TEXTAFTER

Nếu máy bạn dùng Excel 2024 hoặc Microsoft 365, có thể lấy Họ bằng =TEXTBEFORE(B2," ") và Tên bằng =TEXTAFTER(B2," ",-1). Số âm ở đối số thứ ba nghĩa là tìm từ cuối chữ. Các hàm này không có trên Excel 2019 trở về trước; khi đó dùng LEFT với SEARCH, và SUBSTITUTE với REPT như ở trên.

Vì sao lỗi này xảy ra

Họ tên người Việt có quy ước: chữ đầu là Họ, chữ cuối là Tên, phần ở giữa (nếu có) là Tên đệm, và số chữ ở giữa không cố định. Excel không biết quy ước đó: nó chỉ thấy một chuỗi ký tự. Vì vậy mọi cách tách bằng công thức đều dựa vào vị trí các dấu cách, và dữ liệu lộn xộn làm các vị trí này lệch.

Kiểu chữ cũng lộn xộn vì dữ liệu đến từ nhiều nguồn với thói quen nhập khác nhau. PROPER đưa tất cả về một kiểu: chữ cái đầu mỗi chữ viết hoa, còn lại viết thường.

• Họ hoặc Tên bị rỗng hoặc lệch chữ ở một số dòng.

  Nguyên nhân

  Tên có dấu cách thừa ở đầu hoặc cuối, nên vị trí dấu cách đầu tiên và độ dài cả tên đều lệch.

  Cách sửa

  Chuẩn hóa bằng =PROPER(TRIM(B2)) trước, rồi tách từ cột đã chuẩn hóa.

• Tên hai chữ báo #VALUE! ở cột Tên đệm.

  Nguyên nhân

  Độ dài MID tính ra số âm. Theo Microsoft, MID báo #VALUE! khi số ký tự là số âm.

  Cách sửa

  Kiểm tra phép trừ độ dài: với tên hai chữ, độ dài còn lại là 1 (dấu cách giữa), không phải số âm. Ví dụ =TRIM(MID(B2,LEN(H2)+1,LEN(B2)-LEN(H2)-LEN(J2))).

• Họ kép hoặc tên kép bị tách sai (ví dụ Tên chỉ ra một nửa tên kép).

  Nguyên nhân

  Công thức chỉ biết chữ đầu và chữ cuối, nên không phân biệt được chỗ ngắt của họ kép hoặc tên kép.

  Cách sửa

  Rà các dòng đó bằng mắt và sửa tay, hoặc ghi chú riêng danh sách họ kép, tên kép để xử lý.

• PROPER viết hoa chữ ở giữa một từ, ví dụ sau dấu gạch nối hoặc dấu nháy.

  Nguyên nhân

  PROPER viết hoa mọi chữ cái đứng sau một ký tự không phải chữ, không riêng sau dấu cách.

  Cách sửa

  Sửa tay các tên có dấu gạch nối hoặc dấu nháy sau khi chuẩn hóa.

• TEXTAFTER ra #N/A ở người chỉ có một chữ.

  Nguyên nhân

  Không có dấu cách trong chữ, và Microsoft ghi TEXTAFTER báo #N/A khi dấu phân cách không có trong chữ.

  Cách sửa

  Với danh sách có tên một chữ, bọc bằng IFERROR hoặc dùng cách SUBSTITUTE và REPT như ở trên.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Trên Excel thật

• Lưới DeskLab: TRIM và PROPER cho kết quả đúng với tên tiếng Việt viết chuẩn Unicode, kể cả Đ, Ư, chữ có dấu. Bài tập 2 chấp nhận cả cách cổ điển lẫn TEXTBEFORE, TEXTAFTER.
• TEXTBEFORE, TEXTAFTER: chỉ có trên Excel 2024 và Microsoft 365 (theo trang Microsoft của hai hàm này). Nếu bạn làm cho máy dùng bản cũ hơn, hãy dùng cách LEFT, SEARCH và SUBSTITUTE, REPT.
• Text to Columns: tách thẳng trên Excel thật, không có trong lưới DeskLab.

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

Họ kép hoặc tên kép (ví dụ Lan Anh) thì sao?

Công thức chỉ dựa vào vị trí các dấu cách: chữ đầu là Họ, chữ cuối là Tên. Với họ kép hoặc tên kép, máy không phân biệt được chỗ ngắt, nên bạn cần kiểm tra lại các dòng đó bằng mắt hoặc ghi chú riêng.

Có cách tách không cần công thức không?

Có, trên Excel thật: chọn cột họ tên, vào Data, bấm Text to Columns (Văn bản thành cột), chọn Delimited (Có phân cách), chọn dấu phân cách Space (dấu cách) rồi chọn nơi đặt kết quả. Cách này tách theo dấu phân cách bạn chọn. Công thức trong trang này lấy riêng chữ đầu (Họ), chữ cuối (Tên) và phần ở giữa (Tên đệm), kể cả khi các tên có số chữ khác nhau.