Trích xuất tên đệm và họ từ tên đầy đủ trong Excel
Giả sử, bạn có một danh sách tên người dùng, bây giờ, bạn muốn chia tên đầy đủ thành họ, tên đệm và họ trong các cột tách biệt như hình minh họa sau. Bài viết này, tôi sẽ giới thiệu một số công thức để giải quyết công việc này trong Excel.
- Trích xuất hoặc lấy tên từ tên đầy đủ trong Excel
- Trích xuất hoặc lấy họ từ tên đầy đủ trong Excel
- Trích xuất hoặc lấy tên đệm từ tên đầy đủ trong Excel
- Trích xuất hoặc lấy họ, tên đệm và họ từ tên đầy đủ với một tính năng tuyệt vời
Trích xuất hoặc lấy tên từ tên đầy đủ trong Excel
Để trích xuất hoặc lấy tên từ các tên đầy đủ, hàm LEFT và SEARCH có thể giúp bạn, cú pháp chung là:
- name: Tên đầy đủ hoặc tham chiếu ô mà bạn muốn trích xuất đầu tiên.
Vui lòng sao chép hoặc nhập công thức sau vào ô trống mà bạn muốn nhận kết quả:
Sau đó, kéo chốt điền xuống các ô bạn muốn áp dụng công thức này và tất cả các tên đã được trích xuất như hình minh họa bên dưới:
Giải thích công thức:
TÌM KIẾM ("", A2) -1: Hàm TÌM KIẾM này được sử dụng để lấy vị trí của ký tự khoảng trắng đầu tiên, trừ đi 1 có nghĩa là số ký tự bạn muốn nhỏ hơn một so với vị trí của khoảng trắng. Điều này sẽ nhận được kết quả: 5.
LEFT (A2, SEARCH ("", A2) -1) = LEFT ("Jerry T. Stone", 5): Hàm LEFT này sẽ trích xuất văn bản có tên đầy đủ “Jerry T. Stone” từ phía bên trái với 5 ký tự được trả về bởi hàm TÌM KIẾM.
Trích xuất hoặc lấy họ từ tên đầy đủ trong Excel
Để trích xuất họ từ tên đầy đủ, sự kết hợp của các hàm RIGHT, LEN, FIND, LEN và SUBSTITUTE có thể giúp bạn. Cú pháp chung là:
- name: Tên đầy đủ hoặc tham chiếu ô mà bạn muốn trích xuất họ.
Vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống nơi bạn muốn lấy họ:
Và sau đó, kéo chốt điền xuống các ô để điền vào công thức này và tất cả các họ đã được trích xuất từ các tên đầy đủ, xem ảnh chụp màn hình:
Giải thích công thức:
1. LEN (A2) -LEN (SUBSTITUTE (A2, "", "")): Phần này dùng để lấy số ký tự khoảng trắng trong ô A2.
- LEN (A2): Hàm LEN này trả về tổng số ký tự trong ô A2. Nó sẽ trở lại: 14.
- SUBSTITUTE (A2, "", ""): Hàm SUBSTITUTE này được sử dụng để thay thế tất cả các ký tự khoảng trắng bằng không. Và bạn sẽ nhận được kết quả như sau: “JerryT.Stone".
- LEN (SUBSTITUTE (A2, "", ""): Lấy tổng độ dài của tên đầy đủ trong ô A2 không có khoảng trắng.
- LEN (A2) -LEN (SUBSTITUTE (A2, "-", "")): Trừ độ dài của tên đầy đủ không có khoảng trắng từ tổng độ dài của tên đầy đủ để được số khoảng trắng, và kết quả này sẽ được 2.
2. SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))) = SUBSTITUTE (A2, "", "*", 2): Hàm SUBSTITUTE này được sử dụng để thay thế sự xuất hiện cuối cùng của khoảng trắng được trả về bởi công thức phần đầu bằng một ký tự *. Và bạn sẽ nhận được kết quả này: “Jerry T. * Stone".
3. FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = FIND ("*", "Jerry T. *Sỏi" ): Hàm FIND sẽ trả về vị trí của ký tự * trong chuỗi văn bản được trả về bởi hàm SUBSTITUTE và bạn sẽ nhận được số 9.
4. LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = LEN (A2) - 9: Phần này của công thức sẽ có bao nhiêu ký tự sau dấu cách cuối cùng. Điều này sẽ nhận được số 5. Đây sẽ được công nhận là đối số num_chars trong hàm RIGHT.
5. RIGHT (A2, LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))) = QUYỀN (A2, 5): Cuối cùng, hàm RIGHT được sử dụng để trích xuất 5 ký tự được trả về bởi công thức ở bước 4 từ phía bên phải của chuỗi văn bản trong ô A2.
Chú thích: Nếu danh sách tên đầy đủ của bạn chỉ chứa họ và tên, bạn có thể áp dụng công thức đơn giản sau để lấy họ theo ý muốn:
Trích xuất hoặc lấy tên đệm từ tên đầy đủ trong Excel
Nếu bạn cần trích xuất tên đệm từ tên đầy đủ, công thức này được tạo bởi hàm MID và SEARCH. Cú pháp chung là:
- name: Tên đầy đủ hoặc tham chiếu ô mà bạn muốn trích xuất tên đệm.
Vui lòng nhập hoặc sao chép công thức sau vào ô trống để xuất kết quả:
Sau đó, kéo chốt điền xuống các ô để áp dụng công thức này và tất cả các tên đệm cũng được trích xuất, xem ảnh chụp màn hình:
Giải thích công thức:
Để trích xuất tên đệm, trước tiên, bạn nên tìm vị trí của cả hai khoảng trắng giữa tên đầy đủ, sau đó áp dụng hàm MID để trích xuất văn bản ở giữa chuỗi văn bản.
1. TÌM KIẾM ("", A2) + 1: Hàm SEARCH này sẽ lấy vị trí của khoảng trắng đầu tiên, thêm 1 nghĩa là bắt đầu trích xuất từ ký tự tiếp theo. Điều này sẽ nhận được số 7. Phần này được công nhận là đối số start_num trong hàm MID.
2. TÌM KIẾM ("", A2, TÌM KIẾM ("", A2) +1) - TÌM KIẾM ("", A2) -1: Phần này được công nhận là đối số num_chars trong hàm MID.
- TÌM KIẾM ("", A2, TÌM KIẾM ("", A2) +1): Phần công thức này được sử dụng để lấy vị trí của khoảng trắng thứ hai, nó sẽ nhận được số 9.
- TÌM KIẾM ("", A2): Hàm SEARCH này sẽ lấy vị trí của khoảng trắng đầu tiên trong ô A2. Nó sẽ nhận được số 6.
- TÌM KIẾM ("", A2, TÌM KIẾM ("", A2) +1) - TÌM KIẾM ("", A2) -1 = 9-6-1: Trừ vị trí của khoảng trắng đầu tiên khỏi vị trí của khoảng trắng thứ hai, rồi lấy kết quả trừ đi 1 có nghĩa là loại bỏ một khoảng trắng ở đầu. Và kết quả là 2.
3. MID (A2, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1) = MID (A2,7,2 , XNUMX): Cuối cùng, hàm MID sẽ trích xuất 2 ký tự ở giữa ô A2, bắt đầu từ ký tự thứ bảy.
Chú thích: Nếu chỉ có họ và tên trong tên đầy đủ, công thức trên sẽ nhận được giá trị lỗi như hình ảnh chụp màn hình bên dưới:
Để khắc phục sự cố này, bạn nên đưa công thức trên vào hàm IFERROR, vui lòng áp dụng công thức sau:
Trích xuất hoặc lấy họ, tên đệm và họ từ tên đầy đủ với một tính năng tuyệt vời
Nếu bạn gặp khó khăn với các công thức trên, thì đây, tôi sẽ giới thiệu một công cụ dễ dàng - Kutools cho Excel, Với khả Tên phân tách tính năng, bạn có thể trích xuất tất cả họ, tên và họ vào các ô riêng biệt cùng một lúc. Nhấp để tải xuống Kutools cho Excel dùng thử miễn phí!
Các hàm tương đối được sử dụng:
- LEFT:
- Hàm LEFT trích xuất số ký tự đã cho từ phía bên trái của một chuỗi được cung cấp.
- RIGHT:
- Hàm RIGHT được sử dụng để trích xuất một số ký tự cụ thể từ phía bên phải của chuỗi văn bản
- MID:
- Hàm MID được sử dụng để tìm và trả về một số ký tự cụ thể từ giữa chuỗi văn bản nhất định.
- LEN:
- Hàm LEN trả về số ký tự trong một chuỗi văn bản.
- FIND:
- Hàm FIND được sử dụng để tìm một chuỗi trong một chuỗi khác và trả về vị trí bắt đầu của chuỗi bên trong một chuỗi khác.
- SEARCH:
- Hàm SEARCH có thể giúp bạn tìm vị trí của một ký tự hoặc chuỗi con cụ thể từ chuỗi văn bản đã cho
- SUBSTITUTE:
- Hàm SUBSTITUTE trong Excel thay thế văn bản hoặc ký tự trong chuỗi văn bản bằng một văn bản hoặc ký tự khác.
Các bài viết khác:
- Trích xuất họ và tên đầu tiên từ tên đầy đủ
- Giả sử, bạn có một danh sách tên đầy đủ, bây giờ, bạn muốn trích xuất họ và tên đầu tiên hoặc tên và ký tự đầu tiên của họ từ tên đầy đủ như ảnh chụp màn hình dưới đây. Làm thế nào bạn có thể giải quyết những tác vụ này trong sổ làm việc Excel?
- Lật hoặc Đảo ngược Tên Đầu tiên và Họ trong Danh sách Excel
- Nếu bạn có danh sách tên đầy đủ được định dạng là họ và tên, bây giờ, bạn muốn lật họ và tên thành họ và tên như hình minh họa bên dưới. Bài viết này, tôi sẽ giới thiệu một số công thức để giải quyết công việc này trong Excel.
- Trích xuất nhiều dòng từ một ô
- Nếu bạn có danh sách các chuỗi văn bản được phân tách bằng dấu ngắt dòng (xảy ra bằng cách nhấn phím Alt + Enter khi nhập văn bản) và bây giờ, bạn muốn trích xuất các dòng văn bản này thành nhiều ô như hình minh họa bên dưới. Làm thế nào bạn có thể giải quyết nó với một công thức trong Excel?
- Trích xuất từ thứ N từ chuỗi văn bản trong Excel
- Nếu bạn có một danh sách các chuỗi hoặc câu văn bản, bây giờ, bạn muốn trích xuất từ thứ n cụ thể từ danh sách như hình minh họa bên dưới. Bài viết này tôi sẽ giới thiệu một số phương pháp để giải quyết công việc này trong Excel.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
