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))

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.

=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).

Một lần nữa, các công thức này hoạt động nếu dữ liệu gốc nằm trong “cuối cùng đầu tiên”

hoặc định dạng “đầu tiên giữa cuối cùng”. 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”), 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 (2789) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: