Công thức chung
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Tóm lược
Nếu bạn cần trích xuất họ từ tên đầy đủ, bạn có thể làm như vậy với công thức khá phức tạp này sử dụng một số hàm. Ở dạng chung của công thức (ở trên), tên là tên đầy đủ, với khoảng trắng ngăn cách tên với các phần khác của tên.
Trong ví dụ, ô hiện hoạt chứa công thức này:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Giải trình
Về cốt lõi, công thức này sử dụng hàm RIGHT để trích xuất các ký tự bắt đầu từ bên phải. Các hàm khác tạo nên phần phức tạp của công thức này chỉ làm một việc: chúng tính toán số lượng ký tự cần được trích xuất.
Ở cấp độ cao, công thức thay thế khoảng trống cuối cùng trong tên bằng dấu hoa thị "*" và sau đó sử dụng FIND để xác định vị trí của dấu hoa thị trong tên. Vị trí được sử dụng để tính toán có bao nhiêu ký tự để trích xuất với RIGHT.
Làm thế nào để hàm chỉ thay thế khoảng trắng cuối cùng? Đây là phần thông minh.
Hãy thắt dây an toàn, lời giải thích có một chút kỹ thuật.
Chìa khóa của công thức này là bit này:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Cái nào thực sự thay thế khoảng trắng cuối cùng bằng "*".
SUBSTITUTE có đối số thứ tư (tùy chọn) chỉ định "phiên bản" nào của văn bản tìm kiếm sẽ được thay thế. Nếu không có gì được cung cấp cho đối số này, tất cả các phiên bản sẽ được thay thế. Tuy nhiên, nếu giả sử số 2 được cung cấp, thì chỉ có trường hợp thứ hai được thay thế. Trong đoạn mã trên, phiên bản được tính bằng cách sử dụng SUBSTITUTE thứ hai:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Ở đây, độ dài của tên không có bất kỳ khoảng trắng nào được trừ vào độ dài thực của tên. Nếu chỉ có một khoảng trắng trong tên, nó tạo ra 1. Nếu có hai khoảng trắng, nó cho kết quả là 2, v.v.
Trong tên ví dụ ở B4, có hai khoảng trắng trong tên, vì vậy chúng tôi nhận được:
15 - 13 = 2
Và hai được sử dụng như trong số phiên bản:
SUBSTITUTE(B4," ","*",2)
sẽ thay thế khoảng trắng thứ hai bằng "*". Sau đó, tên giống như sau:
"Susan Ann * Chang"
Sau đó, hàm FIND sẽ tiếp nhận để tìm ra vị trí của "*" trong tên:
FIND("*", "Susan Ann*Chang")
Kết quả là 10 (dấu * ở vị trí thứ 10) được trừ cho tổng độ dài của tên:
LEN(B4)-10
Vì tên có 15 ký tự, chúng tôi có:
15-10 = 5
Số 5 được sử dụng bởi RIGHT như vậy:
=RIGHT(B4,5)
Kết quả là "Chang"
Như bạn có thể thấy, để tính được số 5 đơn giản đó còn rất nhiều công việc ở trên!
Xử lý không gian không nhất quán
Khoảng trắng thừa sẽ gây ra vấn đề với công thức này. Một giải pháp là sử dụng hàm TRIM trước để dọn dẹp mọi thứ, sau đó sử dụng công thức phân tích cú pháp.
