xin hàm lọc địa chỉ email (7 người xem)

[nguyentrongtien]

dear các bạn!
mình có 1 file excel, như hình bên dưới

em muốn giữ lại dòng 53, còn dòng 55-56 thì chỉ dữ lại email còn các thành phần khách thì xoá bỏ (nói chung em chỉ muốn giữ lại email) vì số lượng dòng (row) trong file excel này quá lớn (gần 50k) nên làm bằng tay em chịu không thấu với lại hay bị sót. Rất mong các bro của diễn đàn giúp em với ạ
mẫu: data-demo

 

[bebo021999]

Bạn đưa mẫu lên nhé. Nếu trước và sau email có dấu cách thì hy vọng.

 

[nghiaphuc]

dear các bạn!
mình có 1 file excel, như hình bên dưới

em muốn giữ lại dòng 53, còn dòng 55-56 thì chỉ dữ lại email còn các thành phần khách thì xoá bỏ (nói chung em chỉ muốn giữ lại email) vì số lượng dòng (row) trong file excel này quá lớn (gần 50k) nên làm bằng tay em chịu không thấu với lại hay bị sót. Rất mong các bro của diễn đàn giúp em với ạ

Đề xuất với bạn một phương án, bạn tạo 1 bản sao và thử trên bản sao này xem sao nhé: Để ý thấy các địa chỉ email đều kết thúc bằng .vn hoặc .com, vậy nên mình xóa những thứ gì sau .vn, .com hết đi, vậy là mất khúc đuôi. Thêm nữa, nếu phía trước địa chỉ email mà có dấu cách thì bỏ hết từ dấu cách đó về phía trước đi, vậy là mất khúc đầu.
Cách làm như sau:
1. Chọn vùng dữ liệu (vùng chứa email) và nhấn Ctrl+H.
2. Thay .vn* bởi .vn
3. Thay .com* bởi .com (nếu còn phần đuôi khác nữa thì làm tương tự).
4. Thay * (dấu * và dấu cách) bởi chuỗi trống: Find what: * (dấu * và dấu cách), Replace with: Bỏ trống

 

[nguyentrongtien]

cảm ơn bạn, bạn giúp mình nhé, mình vừa đưa data-demo lên đấy

 

[nguyentrongtien]

Đề xuất với bạn một phương án, bạn tạo 1 bản sao và thử trên bản sao này xem sao nhé: Để ý thấy các địa chỉ email đều kết thúc bằng .vn hoặc .com, vậy nên mình xóa những thứ gì sau .vn, .com hết đi, vậy là mất khúc đuôi. Thêm nữa, nếu phía trước địa chỉ email mà có dấu cách thì bỏ hết từ dấu cách đó về phía trước đi, vậy là mất khúc đầu.
Cách làm như sau:
1. Chọn vùng dữ liệu (vùng chứa email) và nhấn Ctrl+H.
2. Thay .vn* bởi .vn
3. Thay .com* bởi .com (nếu còn phần đuôi khác nữa thì làm tương tự).
4. Thay * (dấu * và dấu cách) bởi chuỗi trống: Find what: * (dấu * và dấu cách), Replace with: Bỏ trống

vâng em cảm ơn anh, em thử ngay ạ

 

[nguyentrongtien]

vâng em cảm ơn anh, em thử ngay ạ

hay quá anh ạ, em làm được rồi, em cảm ơn anh nhiều ạ

 

[bebo021999]

3. Thay .com* bởi .com (nếu còn phần đuôi khác nữa thì làm tương tự).

Xúi vậy mất luôn .com.vn à Phúc ơi

 

[nghiaphuc]

hay quá anh ạ, em làm được rồi, em cảm ơn anh nhiều ạ

Tất nhiên đó chỉ là một đề xuất thôi, nó không đáp ứng được cho mọi trường hợp đâu, chẳng hạn gặp trường hợp địa chỉ email có dạng com.com.canh@gmail.com hay abcvn.defg@yahoo.vn thì coi như xong phim.

 

[befaint]

Xúi vậy mất luôn .com.vn à Phúc ơi

Mình lọc trường hợp đó để xử lý riêng chắc là ổn chứ anh. (.com.vn*)

 

[befaint]

Tất nhiên đó chỉ là một đề xuất thôi, nó không đáp ứng được cho mọi trường hợp đâu, chẳng hạn gặp trường hợp địa chỉ email có dạng com.com.canh@gmail.com hay abcvn.defg@yahoo.vn thì coi như xong phim.

Thế thì mình tách ra cột phụ phần sau @. Xử lý xong thì mình ghép lại với phần đầu. Được chứ anh nhỉ?

 

[nghiaphuc]

Xúi vậy mất luôn .com.vn à Phúc ơi

Post xong mới thấy trường hợp này bác ạ. Ngoài ra phải đưa cái ngoặc đó lên chỗ đánh số 2 mới đúng (đúng hơn so với đặt ở vị trí đánh số 3).

 

[bebo021999]

Định làm mà bận quá.
Xử lý cái mail có khoảng trắng trước và sau trước. Còn lại tính sau.
Phúc giúp bạn ấy theo hướng này xem (Đang nghỉ hè mà):
Add thêm 1 khoảng trắng trước và sau, tạo chuỗi A
Subtitute 1 khoảng trắng thành 50 khoảng trắng
"----- abc@def.com.xxx ------- "
Tìm vị trí của @, lấy về bên trái 50 ký tự lấy đầu, lấy về bên phải 50 ký tự lấy đuôi
Khử dấu . ở đuôi (nếu có)

 

[eke_rula]

dear các bạn!
mình có 1 file excel, như hình bên dưới

em muốn giữ lại dòng 53, còn dòng 55-56 thì chỉ dữ lại email còn các thành phần khách thì xoá bỏ (nói chung em chỉ muốn giữ lại email) vì số lượng dòng (row) trong file excel này quá lớn (gần 50k) nên làm bằng tay em chịu không thấu với lại hay bị sót. Rất mong các bro của diễn đàn giúp em với ạ
mẫu: data-demo

Bạn thử CT này xem:

C1=LEFT(B1,LOOKUP(1000,FIND({".com",".vn"},B1)+LEN({".com",".vn"}))-1)

Còn đuôi nào chưa có thì thêm vào là được!!!

 

[befaint]

Định làm mà bận quá.
Xử lý cái mail có khoảng trắng trước và sau trước. Còn lại tính sau.
Phúc giúp bạn ấy theo hướng này xem (Đang nghỉ hè mà):
Add thêm 1 khoảng trắng trước và sau, tạo chuỗi A
Subtitute 1 khoảng trắng thành 50 khoảng trắng
"----- abc@def.com.xxx ------- "
Tìm vị trí của @, lấy về bên trái 50 ký tự lấy đầu, lấy về bên phải 50 ký tự lấy đuôi
Khử dấu . ở đuôi (nếu có)

Em làm theo hướng dẫn của anh (có tăng mức độ "nguy hiểm" lên 200).

=SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&$B1&" "," ",REPT(" ",300)),SEARCH("@",SUBSTITUTE(" "&$B1&" "," ",REPT(" ",300)))-200,400)),"."," "))," ",".")

 

[befaint]

Bạn thử CT này xem:

C1=LEFT(B1,LOOKUP(1000,FIND({".com",".vn"},B1))+LOOKUP(1000,FIND({".com",".vn"},B1),LEN({".com",".vn"}))-1)

Còn đuôi nào chưa có thì thêm vào là được!!!

Còn phần đầu không thuộc email và trường hợp bài #8 nữa.
Ví dụ:
"Email của em: mr.touma.com.com.vavn@yahoo.com. Cảm ơn thầy ạ."

 

[eke_rula]

Còn phần đầu không thuộc email và trường hợp bài #8 nữa.
Ví dụ:
"Email của em: mr.touma.com.com.vavn@yahoo.com. Cảm ơn thầy ạ."

Ah vẫn còn trường hợp dòng 2!!!

 

[huuthang_bd]

Còn phần đầu không thuộc email và trường hợp bài #8 nữa.
Ví dụ:
"Email của em: mr.touma.com.com.vavn@yahoo.com. Cảm ơn thầy ạ."

Vậy vầy được không (CT mảng)

=TRIM(LEFT(SUBSTITUTE(MID(B1,MATCH("*@*",LEFT(B1,FIND(" ",B1&" ",ROW($1:$100))),0),100)," ",REPT(" ",100)),100))

 

[batman1]

Giả thiết:
1. Trước e-mail hoặc không có gì hoặc phải là dấu cách. Vì nếu vd. trước e-mail có dấu chấm "em buon cuoi qua hichic.abc@xyz.com" thì có trời mới biết e-mail là hichic.abc@xyz.com hay là abc@xyz.com
2. Sau e-mail hoặc không có gì (abc@xyz.com) hoặc dấu chấm (abc@xyz.com. Em cam on nhieu), hoặc dấu cách (abc@xyz.com ?? cam on thay a)
Công thức

=TRIM(RIGHT(SUBSTITUTE(" " &LEFT(A1,FIND(".",SUBSTITUTE(A1&" "," ","."),FIND(".",A1,FIND("@",A1))+1)-1)," ",REPT(" ",100)),100))

 

[huuthang_bd]

Giả thiết:
1. Trước e-mail hoặc không có gì hoặc phải là dấu cách. Vì nếu vd. trước e-mail có dấu chấm "em buon cuoi qua hichic.abc@xyz.com" thì có trời mới biết e-mail là hichic.abc@xyz.com hay là abc@xyz.com
2. Sau e-mail hoặc không có gì (abc@xyz.com) hoặc dấu chấm (abc@xyz.com. Em cam on nhieu), hoặc dấu cách (abc@xyz.com ?? cam on thay a)
Công thức

=TRIM(RIGHT(SUBSTITUTE(" " &LEFT(A1,FIND(".",SUBSTITUTE(A1&" "," ","."),FIND(".",A1,FIND("@",A1))+1)-1)," ",REPT(" ",100)),100))

Nếu tên miền có nhiều hơn 1 dấu chấm thì công thức của bạn chưa đúng.
Ý tưởng của bạn sửa lại như vầy hình như đúng.

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",100)),100))

 

[eke_rula]

Cách khác:

dear các bạn!
mình có 1 file excel, như hình bên dưới

em muốn giữ lại dòng 53, còn dòng 55-56 thì chỉ dữ lại email còn các thành phần khách thì xoá bỏ (nói chung em chỉ muốn giữ lại email) vì số lượng dòng (row) trong file excel này quá lớn (gần 50k) nên làm bằng tay em chịu không thấu với lại hay bị sót. Rất mong các bro của diễn đàn giúp em với ạ
mẫu: data-demo

Cách khác:

B1=TRIM(LOOKUP(100,FIND("@",MID(SUBSTITUTE(B1," ",REPT(" ",100)),(ROW($1:$10)-1)*100+1,100)),MID(SUBSTITUTE(B1," ",REPT(" ",100)),(ROW($1:$10)-1)*100+1,100)))

Fill xuống!!!