Công thức tách họ và tên trong Excel là gì?

Khi sử dụng các tùy chọn như sắp xếp và lọc, điều quan trọng là phải tách tên đầy đủ thành tên, tên đệm và họ💡

Excel cung cấp nhiều cách để tách tên🤔

Sử dụng một vài ví dụ, chúng ta sẽ tìm hiểu bốn kỹ thuật cơ bản để tách tên trong Excel

Download file excel đính kèm và thực hành cùng mình

Mục lục

Tách tên với các chức năng

Chúng ta có thể sử dụng các công thức Excel với các hàm văn bản excel để phân chia dữ liệu

Hãy sử dụng công thức Excel để tách họ và tên ở cột đầu tiên trong ví dụ dưới đây

Để trích xuất tên trong Excel, chúng ta có thể sử dụng hai công thức khác nhau

Công thức ví dụ #1 để trích xuất tên

Để chỉ cần lấy tên vào cột tên, hãy sử dụng hàm TEXTBEFORE

1. Trong cột B, nhập dấu bằng và chọn “TEXTBEFORE. ”

Viết,

=TEXTBEFORE(

1. Chọn ô chứa họ và tên

Hãy chọn ô A2 làm Văn bản

Bây giờ công thức của bạn là;

=TEXTBEFORE(A2

1. Nhập một ký tự khoảng trắng làm dấu phân cách

Bây giờ, công thức là

=TEXTBEFORE(A2,” “

1. Đóng dấu ngoặc đơn và nhấn enter

Tên đầu tiên được chiết xuất😍

Hiện tại chỉ người dùng Microsoft 365 mới có thể sử dụng công thức trên😕

Công thức 2 để trích xuất tên

Vì vậy, hãy cùng tìm hiểu công thức khác mà bất kỳ người dùng Excel nào cũng có thể áp dụng🤗

1. Tại cột B, nhập dấu bằng và chọn hàm “LEFT”

1. Chọn tên đầy đủ

Tôi chọn ô A2

Bây giờ công thức của bạn là;

=TRÁI(A2

1. Dùng chức năng “Tìm” để lấy số ký tự trong tên

Chức năng “Tìm” giúp xác định vị trí của một nhân vật cụ thể

Để tính số ký tự trong tên, tìm ký tự khoảng trắng và trừ 1

Công thức bây giờ sẽ trông như thế này

=LEFT(A2,TÌM(” “,A2)-1

Mẹo chuyên nghiệp

Trong công thức trên, bạn có thể thay hàm FIND bằng hàm SEARCH

Cả hai đều có chức năng rất giống nhau

Tuy nhiên, hàm FIND phân biệt chữ hoa chữ thường, trong khi hàm kia thì không

Khi chúng tôi tìm vị trí ký tự khoảng trắng khi trích xuất tên,  phân biệt chữ hoa chữ thường không liên quan

Công thức của hàm TÌM KIẾM là

=LEFT(A2,SEARCH(” “,A2)-1

1. Đóng dấu ngoặc đơn và nhấn “Enter”

Tên đầu tiên được chiết xuất😍

Để lấy họ ra ngoài cột họ và tên trên Excel, chúng ta có thể sử dụng 2 công thức khác nhau

Công thức ví dụ #1 để trích xuất họ

Công thức Excel đơn giản nhất để lấy họ là hàm TEXTAFTER

1. Nhập dấu bằng vào cột họ và chọn “TEXTAFTER”

Viết,

=TEXTAFTER(

1. Chọn ô chứa họ và tên

Vì vậy, chọn A2

Bây giờ công thức của bạn là;

=TEXTAFTER(A2

1. Nhập một ký tự khoảng trắng làm dấu phân cách

Công thức bây giờ là;

=TEXTAFTER(A2,” “

Nếu tên đầy đủ có tên lót thì thêm dấu phẩy và gõ 2

Kasper Langmann, Chuyên gia Microsoft Office

1. Đóng dấu ngoặc đơn và nhấn “Enter”

Tên cuối cùng được trích xuất😎

TEXTAFTER hiện chỉ khả dụng cho người dùng Microsoft 365😒

Công thức ví dụ #2 để trích xuất họ trong Excel từ cột họ và tên

Tất cả người dùng Excel đều có thể sử dụng công thức Excel bên dưới để trích xuất văn bản vào cột họ 🤗

1. Nhập dấu bằng vào cột họ và chọn chức năng “RIGHT”

Viết;

=ĐÚNG(

1. Chọn tên đầy đủ

Vì vậy, chọn ô A2

Bây giờ công thức của bạn là;

=RIGHT(A2

1. Độ dài của họ sau đó phải được tính toán

Có bao nhiêu ký tự trong tên cuối cùng?

Chúng ta có thể tìm ra điều đó bằng cách sử dụng chức năng LEN và chức năng “Tìm kiếm”

Hàm LEN trả về số lượng ký tự trong một chuỗi văn bản

Vì thế,

Số ký tự trong họ =

Số lượng ký tự trong chuỗi văn bản họ và tên – Vị trí ký tự khoảng trắng

Nhập hàm LEN trước, sau đó tham chiếu ô trong cột họ và tên

Bây giờ, công thức của bạn là;

=RIGHT(A2,LEN(A2)

1. Sau đó, giảm vị trí ký tự khoảng trắng từ công thức trên

Sử dụng hàm FIND để định vị khoảng trắng đầu tiên trong ô chứa cả họ và tên

Bây giờ thanh công thức của chúng ta sẽ hiển thị

=RIGHT(A2,LEN(A2)-FIND(” “,A2)

1. Đóng dấu ngoặc đơn và nhấn “Enter”

Họ được chiết xuất 🤩

Bây giờ bạn đã biết cách tách họ và tên bằng công thức Excel

Hãy xem cách sử dụng công thức trong Excel để tách tên khi chúng có họ, tên đệm và họ

Mỗi tên đầy đủ trong bảng dưới đây đều có tên đệm

Cùng học cách tách tên có tên đệm

Công thức 1 tách tên có đệm ra ô riêng biệt

Hàm tách họ và tên trong Excel đơn giản nhất là hàm TEXTSPLIT

1. Nhập dấu bằng vào cột B và chọn “TEXTSPLIT”

Viết,

=TEXTSPLIT(

1. Chọn ô từ cột họ tên

Hãy chọn A2

Bây giờ công thức của bạn là;

=TEXTSPLIT(A2

1. Nhập một ký tự khoảng trắng làm dấu phân cách

Công thức cập nhật của bạn phải là;

=TEXTSPLIT(A2,” “

1. Đóng dấu ngoặc đơn và nhấn “Enter”

Các ô tên, đệm và họ được điền tự động 🥳

TEXTSPLIT loại bỏ nhu cầu nhập nhiều công thức vào ba cột

Chỉ nhập công thức vào một cột, cột B

Kasper Langmann, Chuyên gia Microsoft Office

TEXTSPLIT, như TEXTBEFORE và TEXTAFTER, chỉ khả dụng với người dùng Microsoft 365 💡

Công thức ví dụ #2 để trích xuất tên bao gồm tên đệm

Cùng tìm hiểu thêm một công thức lấy tên đệm ở cột họ và tên

1. Nhập dấu bằng vào cột tên đệm và chọn “MID”

Viết;

=GIỮA(

1. Chọn ô để trích xuất tên đệm

Hãy chọn ô A2

Bây giờ công thức là;

=MID(A2

1. Sau đó, chọn vị trí bắt đầu “tên đệm”

Để làm điều đó, xác định vị trí không gian đầu tiên và sau đó thêm một

Bây giờ công thức của bạn là;

=MID(A2,TÌM(” “,A2)+1

1. Nhập số ký tự cho tên đệm

Vì vậy, chúng tôi sẽ sử dụng công thức dưới đây để tìm không gian cuối cùng

TÌM(” “,SUBSTITUTE(A2,” “,”@”,1))-FIND(” “,A2)-1)

Bây giờ, công thức của bạn phải là;

=MID(A2,FIND(” “,A2)+1,FIND(” “,SUBSTITUTE(A2,” “,”@”,1))-FIND(” “,A2)-1

1. Đóng dấu ngoặc và nhấn “Enter” để tách nhanh tên đệm ra khỏi tên đầy đủ

Bây giờ bạn đã biết cách tách tên trong excel bằng công thức👏

Tách tên bằng Text-to-Columns

Trình hướng dẫn chuyển đổi văn bản thành cột là một tính năng tuyệt vời khác của Excel để chuyển đổi văn bản thành cột

Hãy áp dụng thuật sĩ chuyển đổi văn bản thành cột để tách văn bản thành các cột riêng biệt

1. Chọn cột họ tên

Không chọn tiêu đề cột

1. Chuyển đến tab dữ liệu

1. Chọn “Chuyển văn bản thành cột” từ “nhóm công cụ dữ liệu”

1. Chọn “Được phân cách” và nhấp vào “Tiếp theo”

1. Chọn “Tùy chọn không gian” và nhấp vào “Tiếp theo”

Cửa sổ xem trước dữ liệu sẽ hiển thị cách chia họ và tên trong excel thành các cột riêng biệt

Kasper Langmann, Chuyên gia Microsoft Office

1. Thay đổi ô đích thành B2 và nhấp vào “Finish”

Nếu không, dữ liệu hiện có sẽ bị ghi đè

Tên hiện được tự động điền vào các cột khác nhau 😜

Mẹo chuyên nghiệp

Với tính năng Excel này, bạn thậm chí có thể tạo một bảng chỉ có họ và tên trong 2 cột riêng biệt

Lặp lại các bước trước đó và thực hiện các điều chỉnh được liệt kê bên dưới trong bước cuối cùng

• Chọn cột tên đệm từ phần xem trước dữ liệu

• Chọn “không nhập cột” từ “Định dạng dữ liệu cột”

Đây là kết quả

Tách tên bằng Tìm và Thay thế

Tìm và Thay thế là một tính năng excel tuyệt vời khác

1. Copy toàn bộ danh sách họ tên sang cột họ tên riêng

1. Mở hộp thoại Tìm và Thay thế

Phím tắt cho “Thay thế” là “Control + H”

1. Nhập một ký tự khoảng trắng và một biểu tượng dấu hoa thị trong “Tìm gì” và nhấp vào “Thay thế tất cả”

1. Nhấp vào OK trên thông báo bật lên

Sau đó, chỉ tên sẽ còn lại trong cột và tất cả các tên còn lại sẽ bị xóa

Để lấy họ trong Excel, hãy nhập ký hiệu dấu hoa thị và sau đó là ký tự khoảng trắng trong phần “Tìm gì”

Kasper Langmann, Chuyên gia Microsoft Office

Tách tên bằng tính năng Flash Fill

Đây là cách đơn giản nhất để tách tên trong Excel🤩

Excel cảm nhận một mẫu và điền tên bằng tính năng này

1. Nhập thủ công tên trong ô đầu tiên

1. Nhập thủ công tên trong ô thứ hai xuống. Khi bạn nhập, Flash Fill sẽ tự động hiển thị danh sách tên (màu xám)

Nhấn enter và lặp lại các bước tương tự cho cột tên đệm và họ 🥳

Đó là nó - Bây giờ thì sao?

Bây giờ bạn đã biết những cách tốt nhất để tách tên trong Excel. 👏

Khi các tên được phân tách, bạn có thể dễ dàng áp dụng các hàm IF, SUMIF và VLOOKUP cho dữ liệu của mình

Nhấp vào đây để truy cập khóa học trực tuyến 30 phút miễn phí của tôi, nơi bạn có thể tìm hiểu về IF, SUMIF và VLOOKUP nếu bạn chưa có

Công thức để tách họ và tên là gì?

Công thức tách văn bản trong Excel là gì?