Cách trích xuất họ, tên và họ từ một ô thành các ô riêng biệt trong Excel

Posted on: 2020年11月12日 2020年11月13日
Tags: tips

Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách trích xuất Họ, Tên đệm và Họ từ một chuỗi văn bản nhất định bằng nhiều phương pháp trong Microsoft Excel.

Nhiều khi bạn gặp phải tình huống trong đó bạn được đưa ra một danh sách tên chứa đầy đủ họ và tên và bạn cần phải tách chúng ra theo họ, tên đệm và sau đó trả kết quả vào các ô khác nhau. Đây là một vấn đề rất phổ biến đối với bất kỳ người dùng excel nào và chắc hẳn bạn cũng đã từng gặp phải nó.

Đây là một vấn đề rất phổ biến đối với nhiều người dùng Excel nhưng không có hàm tích hợp cụ thể nào trong Excel có thể trích xuất tên, chữ đệm và họ từ một ô chứa tên đầy đủ.

Tuy nhiên, có nhiều phương pháp trong Excel có thể giúp bạn làm điều tương tự. Và, đó là nội dung bài học này nói về.

Hôm nay, chúng tôi sẽ chỉ cho bạn hai phương pháp khác nhau có thể được sử dụng để tách tên thành các ô khác nhau.

Cách phổ biến nhất trong đó Excel lưu trữ tên là lấy tên đầu tiên, dấu cách rồi đến chữ viết tắt đệm (nếu có), tiếp theo là dấu cách và sau đó là họ. Đôi khi, thay vì dấu cách, dấu phẩy được sử dụng để phân cách.

Chuyển sang ví dụ đầu tiên, như bạn có thể thấy có một vài tên trong “cột A” mà chúng tôi muốn trích xuất và trả lại trong cột B, C và, D lần lượt là tên, chữ đệm và họ.

Ví dụ 1

Lưu ý: Tên mẫu ngẫu nhiên được sử dụng trong hướng dẫn này, chỉ để giải thích cách trích xuất các giá trị và trả lại chúng trong các cột khác nhau.

====== Lưu ý rằng tên mẫu ngẫu nhiên được sử dụng trong hướng dẫn này, chỉ để giải thích cách trích xuất các giá trị và trả về chúng trong các cột khác nhau.

Ở đây, chúng tôi sẽ sử dụng tính năng “Văn bản thành cột” được sử dụng để tách nội dung ô đơn giản là tên và họ thành các cột riêng biệt.

=== Chọn phạm vi từ A9 đến A17 và sau đó nhấn “ALT + D + E”.

====== Lưu ý rằng các tên mẫu ngẫu nhiên được sử dụng trong hướng dẫn này, chỉ để giải thích cách trích xuất các giá trị và trả về chúng trong các cột khác nhau.

Và, bây giờ chúng ta cần làm theo 3 bước đơn giản và như bạn có thể thấy trên đầu hộp trình hướng dẫn, chúng ta đang ở bước đầu tiên.

Chọn “Delimited” Nhấp vào “Next” Trong bước đầu tiên, chúng ta cần cho Excel biết rằng dữ liệu của chúng ta được lưu trữ và định dạng như thế nào trong tệp Excel.

Bây giờ, chúng ta đang ở bước thứ 2 ^ nd ^ của trình hướng dẫn này và như bạn có thể thấy bên dưới, ở đây chúng ta cần đặt các dấu phân cách mà dữ liệu của chúng ta chứa.

====== Không có gì * tên mẫu ngẫu nhiên được sử dụng trong hướng dẫn này, chỉ để giải thích cách trích xuất các giá trị và trả về chúng trong các cột khác nhau.

Ở đây bạn có thể thấy một vài hộp kiểm “Dấu phân cách”; do đó, bạn có thể chọn các hộp kiểm này hoặc có thể đề cập đến dấu phân cách trong hộp kiểm “Khác”. Nếu dữ liệu của bạn chứa một số dấu phân cách khác không được liệt kê ở đây. Bạn có thể chọn một hoặc nhiều dấu phân cách nếu có nhiều hơn một dấu phân cách trong dữ liệu của bạn.

Kiểm tra dấu phân cách “Dấu cách” Sau đó nhấp vào “Tiếp theo”

Chuyển sang bước cuối cùng của trình hướng dẫn “Văn bản thành cột”.

Chọn “Chung” ở định dạng dữ liệu cột Chọn B9 làm “Đích” và khóa nó * Nhấp vào “Kết thúc”

Lưu ý rằng nếu bạn không chỉ định tham chiếu ô đích mới, các cột được trích xuất sẽ thay thế dữ liệu ban đầu.

Bây giờ bạn có thể thấy rằng các tên trong “cột A” đã được trích xuất trong nhiều cột. Tuy nhiên, nếu bạn quan sát kỹ hơn, bạn sẽ nhận thấy rằng trong hàng thứ 11 ^, “Steve Smith” không có chữ cái đệm và họ của anh ấy được chuyển sang cột tên đệm.

Đó là bởi vì, “Văn bản thành cột” chỉ được sử dụng để tách nội dung đơn giản thành các ô khác nhau dựa trên dấu phân cách của chúng. Và, vì chỉ có một khoảng trắng duy nhất trong tên của anh ấy, nó chỉ bị tách ra trong 2 ô tiếp theo. Đây là nhược điểm duy nhất mà tính năng này có.

=== Ví dụ 2

Lưu ý: Trong ví dụ thứ hai, chúng tôi đã lấy “Thị trưởng Tom C Bradley làm tên đầy đủ trong ô H9, chỉ để cho người dùng biết rằng khi có nhiều hơn 1 chữ cái đầu trong tên đệm, hàm sẽ trích xuất cả hai chữ cái đó.

Bây giờ, chúng ta chuyển sang ví dụ 2 ^ nd ^, nơi chúng ta sẽ sử dụng nhiều hàm cùng nhau để có được những gì chúng ta đang tìm kiếm. Đây là kỹ thuật nâng cao để trích xuất “Họ”, “Tên đệm” và “Họ” thành các cột khác nhau. Và, để làm điều tương tự, chúng tôi đã lấy dữ liệu tương tự trong cột H và, chúng tôi sẽ có các tên được phân tách thành ba phần là Họ, Tên đệm & Họ trong cột I, J và K tương ứng. B

Đây là giải pháp tốt nhất để xem xét khi bạn muốn tách tên thành “Đầu tiên”, “Giữa” và “Họ” nhưng không muốn lặp lại phương pháp trước đó mỗi khi bạn làm điều đó. Bạn có thể đặt công thức vào các ô và ô tham chiếu thời điểm sẽ được cập nhật, tên sẽ được trích xuất tự động trong các cột cụ thể mà bạn đã nhập công thức.

=== Để trích xuất Tên

Chọn ô I9 Nhập công thức sau mà không có dấu ngoặc kép “= LEFT (H9, SEARCH (” “, H9) -1)” * Nhấn Enter

hàm trả về vị trí của ký tự cụ thể mà nó tìm thấy đầu tiên trong chuỗi. Vì vậy, ở đây hàm SEARCH đã tìm thấy khoảng trắng đầu tiên ở vị trí thứ 6 ^ trong chuỗi và để loại trừ khoảng trắng, chúng tôi đã cung cấp giá trị âm giúp LEFT chỉ nhận 5 ký tự.

=== Để trích xuất Họ

Chọn ô K9 Nhập công thức sau với dấu ngoặc kép _ “= TRIM (RIGHT (SUBSTITUTE (H9,” “, REPT (” “, 255)), 255))” _ * Nhấn Enter

Để trích xuất họ, chúng ta đã lồng hàm REPT và hàm SUBSTITUTE bên trong hàm RIGHT. Cuối cùng, chúng tôi lồng tất cả các điều kiện vào TRIM để loại bỏ tất cả các khoảng trắng không mong muốn. Hãy xem tất cả các câu lệnh này hoạt động cùng nhau như thế nào để trả về họ.

REPT được lồng bên trong SUBSTITUTE lặp lại khoảng trắng đơn lẻ 255 lần và sau đó SUBSTITUTE thay thế khoảng trắng đơn bằng 255 khoảng trắng trong chuỗi được cung cấp. Và bây giờ mỗi ký tự đầu tiên được phân tách bằng 255 dấu cách và sau đó hàm RIGHT được sử dụng để trích xuất 255 ký tự trên cùng và sau đó TRIM được sử dụng để xóa tất cả các khoảng trắng thừa và sau đó trả về chỉ với chữ cái đầu cuối cùng của tên.

=== Để trích xuất Tên đệm

Bây giờ chọn ô J9 Nhập công thức sau không có dấu ngoặc kép “_ = IF (LEN (H9) -LEN (SUBSTITUTE (H9,”

“,” “))> 1, TRIM (SUBSTITUTE (SUBSTITUTE (H9, I9 , “”), K9, “”)), “”) “_ * Nhấn Enter

Cập nhật: _ _Trong trường hợp bạn phải tách tên đệm giống với họ hoặc tên, hãy sử dụng công thức sau trong cột J thay vì công thức được đề cập ở trên.` = TRIM (MID (H9, LEN (I9) +1, LEN (H9) -LEN (I9) -LEN (K9))) ` Việc trích xuất chữ đệm ở giữa trông khá khó khăn vì chúng ta cần đảm bảo rằng hàm đánh giá tất cả các điều kiện và chỉ trích xuất tên đệm từ nó. Vì vậy, chúng tôi đã lồng các hàm LEN, SUBSTITUTE, TRIM vào bên trong IF

Trong đối số đầu tiên của hàm IF, kiểm tra logic sẽ kiểm tra xem độ dài của chuỗi không có khoảng trắng khi trừ tổng độ dài của chuỗi có lớn hơn 1 không hay không. Khi kết quả là TRUE, hàm thực thi điều kiện TRUE trong đó SUBSTITUTE được lồng bên trong SUBSTITUTE. SUBSTITUTE lồng nhau đang thay thế giá trị của cột I bằng văn bản trống trong cột H và sau đó SUBSTITUTE chính sẽ thay thế giá trị của cột K bằng văn bản trống và sau đó để xóa các khoảng trắng không bắt buộc, https://www.youtube.com/watch?v= -HO9lat0Mfg [TRIM] được sử dụng. Và, trong trường hợp IF điều kiện không đáp ứng kiểm tra logic, thì hàm sẽ trả về văn bản trống.

Đây là cách bạn có thể sử dụng các chức năng tùy chỉnh và các phương pháp đơn giản để trích xuất “Tên”, “Tên đệm” & “Họ” từ ô chứa tên đầy đủ.

== Video: Cách trích xuất Họ, Tên và Họ từ Một ô thành Các Ô Riêng biệt trong Excel Hãy xem hướng dẫn bằng video thú vị này, nơi chúng ta nói chi tiết về tất cả các chức năng và kỹ thuật được sử dụng để trích xuất tên vào các ô khác nhau.

Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook.

Chúng tôi rất muốn nghe ý kiến từ bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]