Tìm họ trong excel

Các công thức sau sẽ giúp bạn trích xuất tên ra khỏi ô trong Excel (hoặc Google Sheets) bằng công thức.  

Mục lục

Họ và tên

Trong ví dụ đầu tiên của chúng tôi, tên đầy đủ có định dạng FirstName LastName

Giả sử tên đầy đủ nằm trong ô A2, đây là các công thức

Tên đầu tiên

=LEFT(A2, FIND(" ", A2)-1)

Họ

=RIGHT(A2, FIND(" ", A2)+1)

Làm thế nào nó hoạt động

Đây là một minh chứng tốt về cách sử dụng các hàm LEFT và RIGHT. LEFT cung cấp cho bạn một số ký tự cụ thể (đây có thể là chữ cái, số, dấu cách, v.v. - bất cứ thứ gì trong ô) từ bên trái của ô (nói cách khác, phần đầu văn bản trong ô), trong khi RIGHT thực hiện tương tự từ bên phải (cuối văn bản trong ô). Đây là "cú pháp" của các hàm LEFT và RIGHT hoặc cách họ muốn bạn cung cấp cho họ thông tin họ cần

=LEFT(ô cần tìm, bao nhiêu ký tự bạn muốn)

=RIGHT(tìm ô nào, bạn muốn bao nhiêu ký tự)

Để tìm tên, chúng ta bắt đầu đọc ở bên trái của ô và dừng lại ngay trước dấu cách đầu tiên. Nói cách khác, số lượng ký tự chúng ta muốn ít hơn một vị trí của khoảng trắng. Chúng tôi sử dụng hàm FIND để tìm khoảng trắng, sau đó trừ 1 vì chúng tôi muốn kết thúc ở ký tự trước khoảng trắng

Để tìm họ, chúng tôi đếm từ bên phải của ô và chúng tôi muốn mọi thứ sau khoảng trắng. Tương tự với tên, chúng ta sử dụng FIND để tìm khoảng trống ở đâu, nhưng lần này chúng ta thêm 1 vì họ của chúng ta bắt đầu sau khoảng trắng

Họ, Tên

Trong ví dụ thứ hai của chúng tôi, tên đầy đủ của chúng tôi ở định dạng Họ, Tên. Một lần nữa, chúng tôi giả sử nó nằm trong ô A2

Tên đầu tiên

=RIGHT(A5, LEN(A5)-FIND(", ", A5)-1)

Họ

=LEFT(A2, FIND(", ", A2)-1)

Làm thế nào nó hoạt động

Điều này rất giống với tập hợp công thức cuối cùng, ngoại trừ chúng tôi đang tìm kiếm dấu phẩy/dấu cách (",") thay vì chỉ khoảng trắng và họ ở bên trái, tên ở bên phải.  

Họ, Tên có thể có Tên đệm/Tên

Ví dụ thứ ba này tương tự như ví dụ thứ hai, nhưng nó có thể xử lý các tên có hoặc không có chữ cái đầu/tên đệm. Nếu danh sách tên của bạn có thể có tên đệm/tên viết tắt, thì bạn cũng có thể sử dụng công thức này thay vì công thức ở trên, nhưng chúng tôi đã bao gồm cả hai để bạn có thể so sánh các công thức và hiểu rõ hơn cách chúng hoạt động.  

Tên đầu tiên

=if(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1>2, MID(A2, FIND(", ", A2)+2, (FIND(" ", A2, FIND(", ", A2)+2)-FIND(", ", A2))-2), RIGHT(A2, LEN(A2)-FIND(", ", A2)-1))

Họ

=LEFT(A2, FIND(", ", A2)-1)

Điều này trở nên phức tạp hơn một chút, vì vậy hãy đợi ở đó. Nếu bạn chưa đọc, hãy đọc phần giải thích về công thức trước đó để hiểu cách thức hoạt động của các hàm LEFT và RIGHT

Điều này bổ sung thêm một chức năng văn bản - MID. Thay vì đếm từ đầu hoặc cuối ô, hàm MID có thể lấy văn bản ra khỏi giữa. Đây là cú pháp hoặc định dạng

=MID(ô cần tìm, bắt đầu từ ký tự nào, bạn muốn bao nhiêu ký tự)

Chúng tôi cũng sử dụng hàm FIND để tìm vị trí của một ký tự trong ô.  

=FIND(bạn đang tìm gì, ô nào cần tìm, [bắt đầu tìm ở đâu])

[Nơi bắt đầu tìm kiếm] nằm trong ngoặc vì nó là tùy chọn

Có hai tình huống mà chúng tôi có thể gặp phải - hoặc chúng tôi chỉ có họ/tên hoặc họ/tên đệm. Chúng tôi xác định cái nào là trường hợp trong phần này của công thức của chúng tôi

LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Nếu là 2, chúng ta chỉ có hai tên và nếu lớn hơn hai, chúng ta cũng có tên đệm. Trang này giải thích cách nó hoạt động

Chúng tôi sử dụng hàm IF để kiểm tra điều này và quyết định phải làm gì trong từng tình huống. Đây là cú pháp

=IF(điều cần kiểm tra, điều cần làm nếu đúng, điều cần làm nếu sai)

Trong trường hợp của chúng tôi, chúng tôi kiểm tra xem nó có lớn hơn 2 không

nếu(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1>2,

và nếu có, chúng tôi thực hiện phép thuật này để tìm tên giữa hai khoảng trắng trong cụm từ

GIỮA(A2, TÌM(", ", A2)+2, (TÌM(" ", A2, TÌM(", ", A2)+2)-TÌM(", ", A2))-2)

và nếu không, chúng tôi sử dụng công thức tên đơn giản hơn từ ví dụ trước

PHẢI(A2, LEN(A2)-TÌM(", ", A2)-1)

Hãy nói về phần giữa của hàm sử dụng công thức MID. Trong phần đó, chúng ta tìm tên đầu tiên giữa dấu phẩy (",") và dấu cách thứ hai trong ô. Đây là bản dịch "tiếng Anh" của phần đó

=MID(tìm trong ô A2, bắt đầu bằng hai ký tự sau dấu phẩy, lấy bao nhiêu ký tự nằm giữa khoảng trắng thứ nhất và khoảng trắng thứ hai)

Cách chúng tôi bắt đầu với hai ký tự sau dấu phẩy. FIND(", ", A2)+2 là hai ký tự sau dấu phẩy. Chúng tôi muốn có hai ký tự sau chứ không phải một ký tự như trong các ví dụ trước, vì có cả dấu phẩy và khoảng trắng

Cách chúng tôi biết có bao nhiêu ký tự giữa khoảng trắng thứ nhất và thứ hai (là độ dài của tên). (TÌM(" ", A2, TÌM(", ", A2)+2)-TÌM(", ", A2))-2

• Để tìm vị trí của không gian thứ hai. FIND(" ", A2, FIND(", ", A2)+2) Chúng tôi tìm khoảng trắng trong A2, không bắt đầu từ đầu mà bắt đầu hai ký tự sau dấu phẩy
• Sau đó, chúng tôi trừ bắt đầu

Họ rất giống với công thức họ trước đó, nhưng nó tìm kiếm dấu phẩy (", ") thay vì khoảng trắng

Địa chỉ email màu nâu

Tại Brown, hầu hết các địa chỉ email đều ở định dạng first_last@brown. giáo dục. Công thức này giúp trích xuất họ và tên từ địa chỉ email ở định dạng này