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

adam_tran · 11/10/07

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 · 11/10/07

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 · 11/10/07

anhtuan1066 đã viết:
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 · 11/10/07

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

vietanhu · 10/11/07

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 · 12/11/07

vietanhu đã viết:
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 · 12/11/07

phamduylong đã viết:
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 · 13/11/07

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 · 13/11/07

adam_tran đã viết:
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 · 13/11/07

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 · 13/11/07

ThuNghi đã viết:
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 · 13/11/07

phamduylong đã viết:
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 · 17/11/07

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 · 21/11/07

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 · 21/11/07

Mr_cuong đã viết:
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 · 21/11/07

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 · 21/11/07

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 · 21/11/07

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 · 21/11/07

salam đã viết:
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 · 21/11/07

salam đã viết:
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)

AGU · 25/1/08

Mong các pác giúp dùm em

Công việc của em nó như vầy mà làm hoài cũng không được
em có chuỗi họ và tên muốn cắt ra chữ đầu của tên, chữ đầu của tên điệm, và tên
ví dụ như sau:
Tran Van Teo ----> tvteo
Nguyen Van Be Ti ----> nvbti
Mong các pác có kinh nghiệm giúp dùm em pát em cam on nhùi

anhtuan1066 · 26/1/08

Nó đây! Bạn xem đúng ý ko nha
ANH TUẤN

longnh · 30/1/08

Nhân tiện chuyện "cắt tên"
Cắt xong rồi thì đến sắp xếp tên theo "ABC" khổ nối tiếng việt còn rất nhiều ký tự khác như:
a
b
â
đ
à
...
Vậy làm sao sắp danh sách này theo đúng thứ tự dấu huyền sắc hỏi ngã nặng
a à â ă ẫ ẩ ậ b đ đ e ê ế ề ễ ể ệ

dovankhoa · 9/5/09

cho mình hỏi cách để cắt chữ khi xuống dòng liên tục :
Cty có dùng hóa đơn tự in nhưng ở phần "số tiền thành chữ" thì tách ra thành 2 dòng.dòng thứ nhất thì mính dùng hàm Left tách ra được rồi nhưng ở dòng thứ 2 thì để đảm bảo số tiền thành chữ được liên tục thì ta dùng hàm nào vậy.
Ví dụ:Số tiền bằng chữ là : hai trăm ngàn đồng.
thì dòng thứ nhất dùng hàm Left ta tách ra được là :"Hai trăm"
-dòng thứ hai ta phải dùng hàm nào để được liên tục là :"ngàn đồng".Sẽ có trường hợp xảy ra khi thay đổi số tiền, thì xảy ra hiện tượng không đúng.ví dụ :dòng 1 là :"Hai tră" và dòng thứ 2 là :"m ngàn đồng".Anh chị chỉ dùm em nha.

BNTT · 9/5/09

dovankhoa đã viết:
cho mình hỏi cách để cắt chữ khi xuống dòng liên tục :
Cty có dùng hóa đơn tự in nhưng ở phần "số tiền thành chữ" thì tách ra thành 2 dòng.dòng thứ nhất thì mính dùng hàm Left tách ra được rồi nhưng ở dòng thứ 2 thì để đảm bảo số tiền thành chữ được liên tục thì ta dùng hàm nào vậy.
Ví dụ:Số tiền bằng chữ là : hai trăm ngàn đồng.
thì dòng thứ nhất dùng hàm Left ta tách ra được là :"Hai trăm"
-dòng thứ hai ta phải dùng hàm nào để được liên tục là :"ngàn đồng".Sẽ có trường hợp xảy ra khi thay đổi số tiền, thì xảy ra hiện tượng không đúng.ví dụ :dòng 1 là :"Hai tră" và dòng thứ 2 là :"m ngàn đồng".Anh chị chỉ dùm em nha.

Tại sao lại dùng cái cách ngắt dòng như thế nhỉ? Tại sao không để trong một dòng?
Làm sao mà mình có thể lường trước được số tiền sẽ lớn hay nhỏ bao nhiêu để ngắt dòng?

Còn trong trường hợp số tiền bằng chữ dài quá, thì tôi sẽ để cho nó tự xuống dòng. Nghĩa là, tôi sẽ kéo cái chiều cao của ô sẽ chứa cái số tiền bằng chữ này rộng ra gấp đôi, và bật chức năng WrapText cho cái ô đó.

dovankhoa · 13/5/09

BNTT đã viết:
Tại sao lại dùng cái cách ngắt dòng như thế nhỉ? Tại sao không để trong một dòng?
Làm sao mà mình có thể lường trước được số tiền sẽ lớn hay nhỏ bao nhiêu để ngắt dòng?

Còn trong trường hợp số tiền bằng chữ dài quá, thì tôi sẽ để cho nó tự xuống dòng. Nghĩa là, tôi sẽ kéo cái chiều cao của ô sẽ chứa cái số tiền bằng chữ này rộng ra gấp đôi, và bật chức năng WrapText cho cái ô đó.

nhưng mà khi mình xuống dòng thì dòng chữ thứ 2 phải lùi về đầu dòng bên trái(tức là sát viền của hóa đơn

dovankhoa · 20/5/09

Các anh chị ơi cho em hỏi một chút.
Hiện tại em đang làm 1 File Excel co dung lượng 2M sử dụng cho phần mềm kế toán trên Excel.Nhưng khi mỗi lần em đánh số hay sửa bất cứ một dữ liệu nào trong ô Excel thì tại thanh Toobar phía dưới(ở gần chữ Start vào chương trình) nó hiện lên chữ Calculating Cell chạy từ 1% đến 100% thì em mới tiếp tục thực hiện những động tác khác, như vậy sẽ rất tốn thời gian cho việc làm của em.Các anh chị có cách nào để khắc phục việc chạy chậm như vậy không?

ndu96081631 · 20/5/09

dovankhoa đã viết:
Các anh chị ơi cho em hỏi một chút.
Hiện tại em đang làm 1 File Excel co dung lượng 2M sử dụng cho phần mềm kế toán trên Excel.Nhưng khi mỗi lần em đánh số hay sửa bất cứ một dữ liệu nào trong ô Excel thì tại thanh Toobar phía dưới(ở gần chữ Start vào chương trình) nó hiện lên chữ Calculating Cell chạy từ 1% đến 100% thì em mới tiếp tục thực hiện những động tác khác, như vậy sẽ rất tốn thời gian cho việc làm của em.Các anh chị có cách nào để khắc phục việc chạy chậm như vậy không?

Ít nhất bạn phải nén file lại rồi đưa lên đây mới có thể biết nguyên nhân là gì!
Mặc khác, bạn post bài tùm lum thế thì thật là khó khăn (mai này muốn tìm lại bài viết mới thấy phê)

dovankhoa · 20/5/09

Các anh chị ơi cho em hỏi một chút.
Hiện tại em đang làm 1 File Excel co dung lượng 2M sử dụng cho phần mềm kế toán trên Excel.Nhưng khi mỗi lần em đánh số hay sửa bất cứ một dữ liệu nào trong ô Excel thì tại thanh Toobar phía dưới(ở gần chữ Start vào chương trình) nó hiện lên chữ Calculating Cell chạy từ 1% đến 100% thì em mới tiếp tục thực hiện những động tác khác, như vậy sẽ rất tốn thời gian cho việc làm của em.Các anh chị có cách nào để khắc phục việc chạy chậm như vậy không?
Mình kèm theo file này mong các anh chị giúp xem giup mình nha.

ndu96081631 · 20/5/09

dovankhoa đã viết:
Các anh chị ơi cho em hỏi một chút.
Hiện tại em đang làm 1 File Excel co dung lượng 2M sử dụng cho phần mềm kế toán trên Excel.Nhưng khi mỗi lần em đánh số hay sửa bất cứ một dữ liệu nào trong ô Excel thì tại thanh Toobar phía dưới(ở gần chữ Start vào chương trình) nó hiện lên chữ Calculating Cell chạy từ 1% đến 100% thì em mới tiếp tục thực hiện những động tác khác, như vậy sẽ rất tốn thời gian cho việc làm của em.Các anh chị có cách nào để khắc phục việc chạy chậm như vậy không?
Mình kèm theo file này mong các anh chị giúp xem giup mình nha.

Tôi mở file trên máy tôi thấy bình thường, không có chậm gì cả!
-----------------------
Các Mod nếu có ghé qua đây vui lòng chuyển bài sang box nào đó phù hợp! Để đây thấy.. kỳ quá

dovankhoa · 5/6/09

Mong các anh, chị giúp mình tạo công thức muốn gộp các dữ liệu rời rạc từ 1 cột thành các dữ liệu liên tục nha.Mình gửi File đính kèm bên dưới, mong anh, chị giúp mình với.

Các anh, chị cho mình hỏi việc cập nhật dữ liệu liên tục từ Excel. mình gửi File kèm theo bên dưới nha>

thivantan · 5/6/09

Bạn có thể dùng Code sau để xóa dòng trống, bạn muốn xóa dòng trống ở cột nào thì tùy chỉnh cho đúng ý.

Mã:

Sub DeleteRow()
    Dim rng As Range
     On Error Resume Next
          Set rng = Intersect(Range("[B]B[/B]2:[B]B[/B]2000"), ActiveSheet.UsedRange)
           rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      ActiveSheet.UsedRange
End Sub

Chúc thành công. Thân

TrungChinhs · 5/6/09

Có thể viết gọn hơn

PHP:

Sub DeleteRow()
On Error Resume Next
[B2:B2000].SpecialCells(4).EntireRow.Delete
End Sub

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

Người dùng đang xem chủ đề này

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên mới

Thành viên mới

-

Thành viên mới

Hãy cho rồi sẽ nhận!

-

Hãy cho rồi sẽ nhận!

-

Hãy cho rồi sẽ nhận!

File đính kèm

-

File đính kèm

Thành viên mới

Hãy cho rồi sẽ nhận!

Thành viên mới

Hãy cho rồi sẽ nhận!

Thành viên gắn bó

Hãy cho rồi sẽ nhận!

-

Thành viên mới

Thành viên gạo cội

File đính kèm

Thành viên chính thức

Thành viên mới

Bùi Nguyễn Triệu Tường

Thành viên mới

Thành viên mới

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

Thành viên mới

File đính kèm

Thành viên thường trực

Thành viên tích cực

Thời gian đếm ngược.