Thử thách tí: Hàm cắt tên (không dùng VBA)

[adam_tran]

Lâu rồi bận quá không vào GPE, chẳng đóng góp được gì. Hôm nay góp 1 câu đố cho vui cửa vui nhà.

Các bạn đã biết khi cần tách tên ra khỏi họ tên (Từ cuối cùng sau khoảng trắng) thì dùng VBA (quá dễ). Nhưng nếu dùng công thức thì sao nhĩ? Mời các bạn tham gia bài tập thử thách này. Yêu cầu không giới hạn tên có bao nhiêu từ.

Gợi ý: Dùng các hàm FIND, LEN, RIGHT và SUBSTITUTE

 

[anhtuan1066]

Món này ngon ăn nhất là TEXT TO COLUMN sau đó dùng 2 hàm IF và OR là xong...

 

[adam_tran]

Món này ngon ăn nhất là TEXT TO COLUMN sau đó dùng 2 hàm IF và OR là xong...

ơ... vậy thì thêm yêu cầu không dùng cột phụ, và không dùng những công thức mới của Excel 2007 (mình lạc hậu quá rồi, chưa xài 2007 bao giờ).

 

[LearnMore]

Ở đây có đáp án rồi nè anh
http://www.giaiphapexcel.com/forum/showpost.php?p=15539&postcount=8

 

[vietanhu]

Bạn Adam_tran xem thử hàm này được không?
=MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,100) với cột A là cột chứa tên.
công thức trên chỉ áp dụng trong trường hợp tên có 2 từ trở lên
.................................................................................................
công thức áp dụng cho tên không hạn chế số từ:
=IF(ISERROR(FIND(" ",A2)),A2,(MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,100)))

 

[phamduylong]

công thức áp dụng cho tên không hạn chế số từ:
=IF(ISERROR(FIND(" ",A2)),A2,(MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,100)))

Công thức đáp ứng yêu cầu, nhưng còn 2 lỗi:
1. Khi A2 rổng cho kết quả là 0
2. Khi nhập không chuẩn bị dư khoảng trắng cuối không tách được.
Vd: "Giải pháp Excel" > "Excel" nhưng "Giải pháp Excel " > rổng
Trường hợp này rất thường gặp.

 

[vietanhu]

Công thức đáp ứng yêu cầu, nhưng còn 2 lỗi:
1. Khi A2 rổng cho kết quả là 0
2. Khi nhập không chuẩn bị dư khoảng trắng cuối không tách được.
Vd: "Giải pháp Excel" > "Excel" nhưng "Giải pháp Excel " > rổng
Trường hợp này rất thường gặp.

Nếu vậy ta thêm hàm trim(text) vào là giải quyết được vấn đề thôi. Còn đối với cell rỗng thì đơn giản rồi.
Công thức sửa lại các bạn kiểm tra lại xem đúng chưa:
=IF(TRIM(A2)="","",IF(ISERROR(FIND(" ",TRIM(A2))),TRIM(A2),(MID(TRIM(A2),FIND("*",SUBSTITUTE(TRIM(A2)," ","*",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))+1,100))))

 

[ThuNghi]

Rất hay, mấu chốt là ở đây, bữa giờ lại không nghĩ ra. Vô cùng cám ơn.
=SUBSTITUTE($A$1," ","*",LEN($A$1)-LEN(SUBSTITUTE($A$1," ","")))

 

[phamduylong]

Các bạn đã biết khi cần tách tên ra khỏi họ tên (Từ cuối cùng sau khoảng trắng) thì dùng VBA (quá dễ). Nhưng nếu dùng công thức thì sao nhĩ? Mời các bạn tham gia bài tập thử thách này. Yêu cầu không giới hạn tên có bao nhiêu từ.
Gợi ý: Dùng các hàm FIND, LEN, RIGHT và SUBSTITUTE

Hàm tách tên xem như đã giải quyết xong, nhưng họ tên cũng còn nhiều việc phải làm, nhân chủ đề này giải quyết luôn:
1. Chấp nhận người nhập nhập không chuẩn về khoảng trắng: "Họ Và Tênl" nhập là " Họ Và tên" hoặc " Họ Và Tên " vẫn phải tính.
2. Đã là họ tên phải từ 2 từ trở lên. Ví dụ "Nguyễn", "Bình" có 1 từ bỏ qua, không tính.
3. Đối với họ tên từ 3 từ trở lên chia làm 3 phần:
- Họ: từ đầu tiên
- Tên: từ cuối cùng
- Tên đệm: các từ ở giữa Họ và Tên. Nếu họ tên có 2 từ thì tên đệm rổng.
Ví dụ:
"Trần Văn A" tên đệm là "Văn"
"Nguyễn Trần Thái Bình" tên đệm là "Trần Thái"
"Phan Thanh" có 2 từ nên tên đệm ""
Mời các bạn tham gia viết tiếp công thức tách họ, tách tên, tách tên đệm theo yêu cầu trên.

 

[ThuNghi]

Em xin tham gia hoàn thiện phần họ và tên lót. Họ và tên luôn có ít nhất 2 từ.
Họ:=IF(TRIM($A2)="","",LEFT(TRIM($A2),FIND(" ",TRIM($A2),1)))
Tên:=IF(TRIM($A2)="","",IF(ISERROR(FIND(" ",TRIM($A2))),TRIM($A2),(MID(TRIM($A2),FIND("*",SUBSTITUTE(TRIM($A2)," ","*",LEN(TRIM($A2))-LEN(SUBSTITUTE(TRIM($A2)," ",""))))+1,100))))
Tên đệm (lót): =IF(LEN(TRIM($A2))-LEN(SUBSTITUTE(TRIM($A2)," ",""))=1,"",TRIM(MID($A2,LEN($B2)+1,LEN($A2)-LEN($B2)-LEN($D2))))
Chắc có thể bỏ bớt cho gọn hơn.

 

[phamduylong]

Em xin tham gia hoàn thiện phần họ và tên lót. Họ và tên luôn có ít nhất 2 từ.
Họ:=IF(TRIM($A2)="","",LEFT(TRIM($A2),FIND(" ",TRIM($A2),1)))
Tên đệm (lót): =IF(LEN(TRIM($A2))-LEN(SUBSTITUTE(TRIM($A2)," ",""))=1,"",TRIM(MID($A2,LEN($B2)+1,LEN($A2)-LEN($B2)-LEN($D2))))
Chắc có thể bỏ bớt cho gọn hơn.

1. Họ: khi A2 là một từ báo lỗi #VALUE
2. Tên đệm (lót):
- Còn mượn ô B2, có thể tính trực tiếp từ A2 không?
- "aaa bbb ccc ddd" tách thành "bbb ccc ddd". Đúng phải là "bbb ccc"

 

[ThuNghi]

1. Họ: khi A2 là một từ báo lỗi #VALUE
2. Tên đệm (lót):
- Còn mượn ô B2, có thể tính trực tiếp từ A2 không?
- "aaa bbb ccc ddd" tách thành "bbb ccc ddd". Đúng phải là "bbb ccc"

A2 luôn ít nất là 2 tư. Còn 1 từ cũng ok
1/Họ:
=IF(OR(ISERROR(FIND(" ",TRIM($A2))),TRIM($A2)=""),"",TRIM(LEFT(TRIM($A2),FIND(" ",TRIM($A2),1))))
2/Đệm:
=IF(LEN(TRIM($A2))-LEN(SUBSTITUTE(TRIM($A2)," ",""))=1,"",TRIM(MID(TRIM($A2),LEN($B2)+1,LEN(TRIM($A2))-LEN($B2)-LEN($D2))))
Phải lấy thêm từ tên, vì ct tên dài nhất.
3/Tên:
=IF(TRIM($A2)="","",IF(ISERROR(FIND(" ",TRIM($A2))),TRIM($A2),(MID(TRIM($A2),FIND("*",SUBSTITUTE(TRIM($A2)," ","*",LEN(TRIM($A2))-LEN(SUBSTITUTE(TRIM($A2)," ",""))))+1,100))))

 

[phamduylong]

Các hàm họ tên theo yêu cầu bài 13. Lấy trực tiếp từ A1 (không qua ô phụ)
1. Họ:
IF(ISERROR(FIND(" ";TRIM(A1);1));"";LEFT(TRIM(A1);FIND(" ";TRIM(A1);1)))

2. Tên:
=IF(ISERROR(FIND(" ";TRIM(A1);1));"";RIGHT(TRIM(A1);LEN(TRIM(A1))-FIND("#";SUBSTITUTE(TRIM(A1);" ";"#";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))))

3. Tên đệm:
=IF(ISERROR(FIND(" ";TRIM(A1);1));"";IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))<2;"";MID(TRIM(A1);FIND(" ";TRIM(A1))+1;FIND("#";SUBSTITUTE(TRIM(A1);" ";"#";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))-FIND(" ";TRIM(A1))-1)))

 

[Mr_cuong]

Lấy tên từ chuỗi họ tên

Giải pháp đơn giản và nhanh nhất là dùng lệnh Replace thay thế ngay trên cột tham chiếu (không cần dùng cột phụ)
Bước 1: quét khối cột
Bước 2: dùng lệnh Replace thay thế * (*và khoảng trắng)= trống
Cuongbsg

 

[ThuNghi]

Giải pháp đơn giản và nhanh nhất là dùng lệnh Replace thay thế ngay trên cột tham chiếu (không cần dùng cột phụ)
Bước 1: quét khối cột
Bước 2: dùng lệnh Replace thay thế * (*và khoảng trắng)= trống
Cuongbsg

Bạn làm thử 1 ví dụ nhé. Cám ơn.

 

[Mr_cuong]

Bước 1: Quét khối cột A
Bước 2: Chon Alt E -E
Mục Find What gõ *thêm một khoảng trắng phía sau dấu *
Mục Replace With để trống
Chọn Alt - A

Cuongbsg

 

[ThuNghi]

Hiểu ý bạn rồi, cái này dùng xong mất luôn họ luôn, nếu vậy nên dùng text to column thì hay hơn. Cám ơn.

 

[salam]

Oh Bác DuyLong ơi bác xem lại công thức phần tên đệm của bác khi tên có hai từ thì báo lỗi bác à. bác thêm đk khi không có tên đệm vào cho hoàn chỉnh công thức.
Thân!

 

[ThuNghi]

Oh Bác DuyLong ơi bác xem lại công thức phần tên đệm của bác khi tên có hai từ thì báo lỗi bác à. bác thêm đk khi không có tên đệm vào cho hoàn chỉnh công thức.
Thân!

Bạn sửa lại ct như sau:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))<=1,"",MID(TRIM(A1),FIND(" ",TRIM(A1))+1,FIND("#",SUBSTITUTE(TRIM(A1)," ","#",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-FIND(" ",TRIM(A1))-1))

LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1) ie số khoảng trắng <=1 là chỉ có < 2 từ thì tên đệm =""

 

[phamduylong]

Oh Bác DuyLong ơi bác xem lại công thức phần tên đệm của bác khi tên có hai từ thì báo lỗi bác à. bác thêm đk khi không có tên đệm vào cho hoàn chỉnh công thức.
Thân!

Đã chỉnh lại lỗi công thức 3 và tải lại Ham Ho Ten.zip
(Xem lại bài 17)