Lance có một tập hợp các tên trong sổ làm việc. Tất cả các tên đều nằm trong cột A, một số có tên và họ, trong khi những tên khác sử dụng họ, tên đệm và tên. Anh ta cần tách tên thành các cột riêng lẻ, nhưng trình hướng dẫn Văn bản thành Cột không cung cấp kết quả khả quan. Nó thực hiện tách biệt OK, nhưng vấn đề tên hai so với ba có nghĩa là Lance cần phải thực hiện nhiều thao tác thủ công dữ liệu sau khi nó được tách.

Giải pháp cho vấn đề là không dựa vào trình hướng dẫn Văn bản thành Cột, mà thay vào đó sử dụng một số công thức để đưa tên vào các cột. Kết quả bạn đạt được phần lớn vẫn phụ thuộc vào điều kiện của dữ liệu bạn đang phân tích cú pháp. Nếu dữ liệu của bạn ở định dạng “chữ đệm cuối cùng” (với tên đệm là tùy chọn), thì bạn có thể sử dụng công thức sau để lấy ra tên:

=LEFT(A1,SEARCH(" ",A1,1)-1)

Công thức này kiểm tra khoảng trắng đầu tiên trong tên, sau đó giả định rằng mọi thứ trước khoảng trắng đó đều là tên. Công thức tiếp theo được sử dụng để xác định xem có tên đệm hay không và nếu có, hãy hiển thị nó:

=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1)) = TRUE, "", LEFT( RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1) - 1))

Mặc dù công thức hiển thị dưới dạng 5 dòng ở đây, nó thực sự là một công thức duy nhất và nên được nhập như vậy. Công thức kiểm tra sự tồn tại của khoảng trắng thứ hai trong tên. Nếu một lỗi được tạo ra (không có khoảng trắng thứ hai) thì công thức không trả về kết quả nào; không có tên đệm. Nếu một khoảng trắng thứ hai được phát hiện (không có lỗi nào được tạo ra), thì công thức trả về mọi thứ từ sau khoảng trắng đầu tiên đến khoảng trống thứ hai.

Công thức tiếp theo được sử dụng để trích xuất họ:

=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))

Công thức này dựa trên kết quả được trả về cho cả tên và tên đệm. Nó trả về mọi thứ còn lại trong tên gốc sau khi tính độ dài của tên đầu tiên (giả định trong ô B1) và tên đệm (giả định trong C1). (Xem Hình 1.)

image

Hình 1. Tách tên thành các cột.

Một lần nữa, các công thức này hoạt động nếu các tên trong cột A tuân theo một số nguyên tắc khá nghiêm ngặt. Tên phải ở định dạng “họ đầu tiên” hoặc “họ đệm đầu tiên”. Nếu có các vòng loại trong tên như Ms., Dr., Jr., hoặc III, thì bạn sẽ không nhận được kết quả khả quan. Ngoài ra, nếu họ chứa khoảng trắng (như trong “John van Kamp” hoặc “Mary Anne St. Marie”) hoặc nếu tên không chứa khoảng trắng, thì bạn cũng sẽ không nhận được kết quả như ý.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12383) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: