Trong Excel, rất dễ dàng để tách họ, tên đệm và tên xuất hiện trong cùng một cột thành các cột riêng biệt bằng cách sử dụng các cách khác nhau.
Giả sử bạn nhận được một danh sách liên hệ với tất cả các tên được liệt kê đầy đủ trong một cột duy nhất và có thể bạn sẽ cần phải tách họ, tên đệm và họ và chia chúng thành các cột riêng biệt. Có một số cách khác nhau mà bạn có thể sử dụng để phân tách các tên - bằng cách sử dụng tính năng Văn bản thành Cột, Tô flash và công thức.
Trong Excel, việc tách tên từ một cột thành hai hoặc nhiều cột thực sự dễ dàng. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách tách tên thành các cột khác nhau trong excel bằng các cách khác nhau.
Cách tách tên trong Excel
Có ba cách dễ dàng để tách tên trong Excel. Tùy thuộc vào cấu trúc dữ liệu và việc bạn muốn các tên phân tách là tĩnh hay động, hãy chọn một trong các phương pháp sau:
- Tách tên bằng tính năng Văn bản thành Cột
- Tách các tên bằng Công thức
- Tách các tên bằng cách sử dụng Flash Fill
Tách các tên bằng cách sử dụng trình hướng dẫn văn bản thành cột
Trình hướng dẫn Văn bản thành Cột là một trong những cách phổ biến nhất để tách họ và tên trong Excel cũng như tên đệm. Đảm bảo rằng có các cột trống bên cạnh tên mà bạn sắp chia vì tên đầy đủ sẽ được chia thành các cột riêng biệt.
Ví dụ: bạn có tập dữ liệu dưới đây với đầy đủ tên và bạn muốn tách / tách họ và tên và lưu trữ chúng thành các ô riêng biệt.
Đầu tiên, đánh dấu cột tên đầy đủ mà bạn muốn tách biệt. Sau đó, chuyển đến tab ‘Dữ liệu’ và nhấp vào tùy chọn ‘Văn bản thành cột’ trong phần ‘Công cụ dữ liệu’.
Trình hướng dẫn Chuyển đổi Văn bản thành Cột sẽ mở ra. Trên bước đầu tiên của Wizard, chọn tùy chọn ‘Delimited’ và nhấp vào ‘Next’.
Trong Bước 2/3 của Trình hướng dẫn Chuyển văn bản thành Cột, hãy chọn dấu phân tách phân tách dữ liệu của bạn, xóa bất kỳ dấu kiểm nào khác và nhấp vào 'Tiếp theo'. Trong trường hợp của chúng tôi, 'dấu cách' phân tách Họ và Tên, vì vậy chúng tôi chọn dấu phân cách này.
Phần Xem trước dữ liệu bên dưới trong cửa sổ hiển thị cách phân tích cú pháp tên của bạn.
Ở bước 3/3, bạn chọn định dạng và đích dữ liệu rồi nhấp vào ‘Hoàn tất’.
Thông thường, ‘Chung’ mặc định sẽ hoạt động tốt đối với hầu hết các loại dữ liệu. Trong trường 'Đích đến', hãy chỉ định vị trí mà bạn muốn hiển thị đầu ra. Bạn phải chỉ định địa chỉ của ô đầu tiên trong cột mà bạn muốn có kết quả (B2, trong trường hợp của chúng tôi).
Hãy nhớ rằng, nếu bạn không chỉ định ô đích, trình hướng dẫn sẽ ghi đè lên dữ liệu ban đầu, vì vậy hãy đảm bảo chọn một cột trống.
Khi bạn nhấp vào nút ‘Hoàn tất’, nó sẽ ngay lập tức tách tên đầy đủ thành hai cột riêng biệt (Tên và Họ).
Làm theo các bước tương tự nếu bạn có họ, tên đệm và họ, và tên của bạn sẽ được chia thành ba cột thay vì hai.
Ghi chú: Kết quả của phương pháp này là tĩnh. Nó có nghĩa là, nếu bạn thay đổi tên ban đầu, bạn cần phải làm lại tất cả điều này để tách các tên.
Tên phân tách được phân tách bằng dấu phẩy
Nếu họ và tên được phân tách bằng dấu phẩy, hãy làm theo các bước dưới đây để xóa dấu phẩy và tách họ và tên.
Trong ví dụ sau, tên được định dạng theo định dạng ngược lại (Họ, Tên), trong đó họ đứng trước bằng dấu phẩy, sau đó là tên.
Chọn tên và đi tới Dữ liệu -> Văn bản thành Cột. Ở bước 1, chọn 'Dấu phân cách' và nhấp vào 'Tiếp theo'. Ở bước 2, trong Dấu phân cách, hãy chọn ‘Dấu phẩy’ (,) làm dấu phân cách vì tên của bạn được phân tách bằng dấu phẩy.
Ở bước cuối cùng, bạn chọn định dạng dữ liệu là ‘Chung’, chỉ định đích và nhấp vào ‘Hoàn tất’.
Bây giờ, bạn sẽ có tên trong các cột riêng biệt.
Tên riêng biệt bằng công thức
Trình hướng dẫn Văn bản thành Cột giúp tách tên nhanh chóng và dễ dàng. Tuy nhiên, nếu bạn muốn chỉnh sửa tên gốc và muốn một phương thức động sẽ tự động cập nhật mỗi khi bạn thay đổi tên, thì tách tên bằng công thức là lựa chọn phù hợp. Bạn có thể sử dụng các hàm LEFT, RIGHT, MID, LEN và SEARCH hoặc FIND để tách các tên.
Tách biệt họ và tên trong Excel bằng công thức
Lấy tên
Giả sử bạn có tập dữ liệu dưới đây và bạn muốn tách tên thành một ô riêng biệt. Bạn cần kết hợp hàm FIND và hàm LEFT thành một công thức để lấy tên.
Sử dụng công thức sau để lấy tên:
= LEFT (A2, FIND ("", A2) -1)Công thức này sử dụng hàm FIND để tìm vị trí của ký tự khoảng trắng (““) ở giữa họ và tên và trừ đi 1 để loại trừ chính khoảng trắng. Sau đó, số này được cung cấp cho hàm LEFT, hàm này sử dụng số vị trí này để trích xuất tất cả văn bản trước nó. Bạn cũng có thể sử dụng chức năng TÌM KIẾM thay vì chức năng TÌM.
Sau khi bạn nhập công thức vào một ô trống (B2), hãy kéo chốt điền xuống các ô khác để áp dụng công thức này và tất cả các tên đã được tách thành cột B như hình dưới đây:
Bạn có thể lồng hàm SEARCH và FIND bên trong hàm LEFT để trích xuất tên. Sự khác biệt chính giữa hai hàm là FIND phân biệt chữ hoa chữ thường, trong khi SEARCH phân biệt chữ hoa chữ thường.
Nếu bạn có dấu phẩy (,) thay vì dấu cách giữa họ và tên, thì hãy sử dụng dấu phẩy làm đối số đầu tiên trong hàm FIND:
= LEFT (A2, FIND (",", A2) -1)Lấy họ
Bây giờ nếu bạn cần trích xuất họ, hãy sử dụng hàm RIGHT. Công thức sau sẽ trích xuất họ từ cùng một tập dữ liệu:
= RIGHT (A2, LEN (A2) -FIND ("", A2))Đầu tiên, công thức tìm vị trí ký tự khoảng trắng, trừ số đó khỏi tổng độ dài của chuỗi (được trả về bởi hàm LEN) và số này sau đó được cung cấp cho hàm RIGHT để trích xuất nhiều ký tự đó từ cuối chuỗi. (Tên).
Tách biệt Họ, Tên đệm và Tên trong Excel bằng Công thức
Việc tách các tên bao gồm tên đệm cần có các công thức khác nhau, tùy thuộc vào định dạng tên bạn có.
Để có được họ khi bạn có tên đệm hoặc chữ đệm, hãy sử dụng cùng một công thức TÌM TRÁI mà bạn đã quen thuộc.
Lấy họ
Công thức TÌM KIẾM ở trên hoạt động tốt khi chỉ có họ và tên, nó sẽ không được sử dụng nhiều trong trường hợp tên ban đầu của bạn chứa tên đệm hoặc chữ viết tắt. Điều này là do bạn không tính đến hai ký tự khoảng trắng trong tên.
Để lấy họ khi bạn cũng có tên đệm, hãy sử dụng công thức sau:
= RIGHT (A2, LEN (A2) -SEARCH ("", A2, TÌM KIẾM ("", A2,1) +1))Để trích xuất họ, trước tiên hãy xác định vị trí của ký tự khoảng trắng thứ hai bằng cách sử dụng các hàm TÌM KIẾM lồng nhau, thêm 1 vào TÌM KIẾM (”“, A2,1) để bắt đầu trích xuất với ký tự tiếp theo. Tiếp theo, trừ vị trí của khoảng trắng thứ 2 khỏi tổng độ dài chuỗi và thu được độ dài của họ dưới dạng số kết quả. Sau đó, cung cấp số kết quả này cho hàm RIGHT để trích xuất số ký tự từ cuối chuỗi.
Lấy tên đệm
Hàm MID sử dụng ba đối số, đối số đầu tiên chỉ định văn bản hoặc địa chỉ ô, đối số thứ hai chỉ định vị trí bắt đầu và đối số cuối cùng cho biết số ký tự để trích xuất tên đệm từ vị trí đó.
Cú pháp:
= MID (văn bản, start_num, num_chars)Để lấy tên đệm, hãy nhập công thức này vào một ô trống:
= MID (A2, TÌM KIẾM ("", A2) + 1, TÌM KIẾM ("", A2, TÌM KIẾM ("", A2) +1) -SEARCH ("", A2) -1)Hãy xem công thức phức tạp này hoạt động như thế nào:
Để trích xuất tên đệm hoặc chữ viết tắt, bạn cần xác định vị trí của cả hai khoảng trắng trong tên đầy đủ. Để tìm vị trí của ký tự khoảng trắng đầu tiên, hãy nhập vào TÌM KIẾM ("", A2) trong đối số ‘start_num’ và thêm 1 để bắt đầu trích xuất từ ký tự tiếp theo.
Sau đó, để tìm ra độ dài của tên đệm, hãy đặt điều này TÌM KIẾM ("", A2, TÌM KIẾM ("", A2) +1) -SEARCH ("", A2) -1 hàm lồng nhau trong đối số ‘num_chars’, hàm này trừ vị trí của khoảng trắng thứ nhất khỏi vị trí của khoảng trắng thứ 2 và trừ đi 1 từ kết quả để xóa một khoảng trắng ở cuối. Kết quả cuối cùng sẽ cho bạn biết có bao nhiêu ký tự cần giải nén.
Bây giờ, hàm MID với vị trí bắt đầu của tên đệm và các ký tự số để trích xuất tách tên đệm khỏi tên đầy đủ (A2).
Tên riêng biệt trong Excel bằng cách sử dụng Flash Fill
Điền vào flash chủ yếu được sử dụng để tự động điền dữ liệu của một mẫu cụ thể. Nó cũng có thể được sử dụng để tách tên. Nó chỉ có sẵn trong Excel 2013, 2016, 2019 và 365.
Giả sử bạn có tập dữ liệu dưới đây và bạn chỉ muốn lấy tên trong số các tên đầy đủ.
Trong ô liền kề với tên gốc, hãy nhập tên. Trong trường hợp này, hãy nhập ‘Steve’ vào ô B2.
Sau đó, bắt đầu nhập tên đầu tiên vào ô thứ hai của cột. Trong khi bạn đang nhập, nếu Excel nhận thấy một mẫu, Flash Fill sẽ tự động hiển thị cho bạn danh sách tên trong các ô khác (màu xám).
Khi bạn thấy danh sách tên có màu xám và nếu những tên đó đúng, thì bạn chỉ cần nhấn phím ‘Enter’ và Flash Fill sẽ tự động điền tên vào phần còn lại của cột.
Thực hiện theo quy trình tương tự để tách họ thành một cột riêng biệt.
Kết quả:
Flash Fill hoạt động bằng cách phát hiện một mẫu trong dữ liệu đó và làm theo mẫu đó trong khi cung cấp cho bạn dữ liệu đã sửa đổi. Lúc đầu, khi bạn nhập tên vào ô đầu tiên, Flash Fill không nhận dạng được mẫu. Nhưng khi bạn bắt đầu nhập lại tên vào ô thứ hai, Flash Fill sẽ nhận dạng mẫu và hiển thị cho bạn đề xuất tách tên. Sau đó, chỉ cần nhấn phím ‘Enter’.
Nói chung, tính năng Flash Fill được bật theo mặc định. Nếu nó không hoạt động trong Excel của bạn, sau khi nhập tên đầu tiên vào ô đầu tiên, bạn có thể chỉ cần chọn ô thứ hai và nhấp vào nút ‘Flash Fill’ từ nhóm Công cụ Dữ liệu trên tab ‘Dữ liệu’.
Ngoài ra, bạn cũng có thể nhấn ‘Ctrl’ + ‘E’ để nhận được kết quả tương tự.
Bây giờ, phần còn lại của các ô sẽ được điền tên.
Đôi khi, bạn có thể không nhìn thấy gợi ý mẫu có màu xám, trong trường hợp đó, bạn có thể sử dụng chốt điền để nhận kết quả Flash Fill.
Đầu tiên, nhập tên theo cách thủ công vào hai ô và chọn cả hai ô này. Sau đó, di con trỏ của bạn vào góc dưới bên phải của vùng chọn. Bạn sẽ nhận thấy rằng con trỏ thay đổi từ một hình vuông nhỏ màu xanh lá cây (biểu tượng Filler) thành một biểu tượng dấu cộng.
Tiếp theo, nhấp đúp vào biểu tượng dấu cộng đó. Điều này sẽ lấp đầy phần còn lại của các ô. Tại thời điểm này, kết quả không chính xác, bạn sẽ thấy cả hai tên được lặp đi lặp lại. Sau đó, ở góc dưới bên phải của dữ liệu kết quả, bạn sẽ thấy một biểu tượng Tự động điền nhỏ như hình dưới đây. Nhấp vào biểu tượng ‘Tự động điền’ này và chọn ‘Flash Fill’.
Điều này sẽ điền tên vào tất cả các ô:
Xóa tên đệm
Bạn cũng có thể sử dụng công cụ Flash Fill để loại bỏ tên đệm khỏi tên đầy đủ.
Ví dụ: giả sử bạn có tập dữ liệu dưới đây và bạn chỉ muốn lấy họ và tên mà không có tên đệm hoặc chữ đệm.
Để nhận các tên không có tên đệm hoặc chữ viết tắt, hãy nhập thủ công ‘Lord Stark’ vào ô bên cạnh. Sau đó, trong ô liền kề thứ hai, nhập ‘Daenerys Targaryen’. Trong khi bạn đang nhập, Flash Fill sẽ nhận ra một mẫu và nó sẽ hiển thị cho bạn danh sách các tên không có tên đệm (màu xám).
Nếu gợi ý là đúng, hãy nhấn phím ‘Enter’ và Flash Fill sẽ tự động điền vào các ô còn lại bằng tên mà không có tên đệm.
Nếu bạn chỉ muốn lấy tên đệm mà không có họ và tên, hãy nhập tên đệm vào hai ô đầu tiên và sử dụng công cụ Flash Fill để lấy tên đệm từ tất cả các tên đầy đủ trong một cột.
Hướng dẫn này chỉ ra cách tách các tên trong khi thao tác với dữ liệu văn bản. Các phương pháp này cũng có thể hữu ích khi làm việc với các dạng dữ liệu khác như địa chỉ, tên sản phẩm, tên thương hiệu, v.v.