xin hàm lọc địa chỉ email (1 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!!!

 

[batman1]

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.

Đúng là khi viết công thức tôi giả thiết là tên miền chỉ có 1 dấu chấm. Chỉ có điều tôi quên viết giả thiết thôi.

Vì nếu xét cả 2 dấu chấm thì giả thiết 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) sẽ không đúng chỗ đỏ đỏ. Trong nhiều trường hợp sẽ không thể biết e-mail là abc@xyz.com.hichic hay abc@xyz.com. Sẽ phải viết công thức khác hoặc phức tạp hơn.

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

Không đúng.
Công thức sẽ không đúng nếu dữ liệu là:
mr.touma@yahoo.com.Cảm ơn thầy ạ. (lọc ra có cả ".Cảm"
mr.touma@yahoo.com. Cảm ơn thầy ạ. (lọc ra có cả dấu chấm cuối)

Trước đó tôi cũng viết công thức tương tự nhưng lúc đó phải bỏ trường hợp sau e-mail có dấu chấm không thuộc e-mail. Vì lúc đó không thể biết được phần sau dấu chấm thứ 2 thuộc về e-mail hay không. Tất nhiên với dữ liệu như thế thì phải lọc ra phần có dấu chấm thứ 2 -> phân tích xem nó có là dạng tên miền không (com, vn, fr, pl, ru, hàng trăm đuôi). Và phải làm bằng code

 

[nguyentrongtien]

cuối cùng chốt hạ lại là công thức nào thưa các Thầy? mấy cthức dài dài em gõ vào chả hiểu sao đều báo lỗi

 

[TheThienChu]

cuối cùng chốt hạ lại là công thức nào thưa các Thầy? mấy cthức dài dài em gõ vào chả hiểu sao đều báo lỗi

Tui chỉ là khán giả chứ không thuộc tầng lớp các thầy mà bạn hỏi dưng mà xem biểu diễn nhiều quá nên muốn hỏi thớt tí là: Dữ liệu thực là như thế nào. Bạn đưa lên có vài dòng ví dụ sao mà đảm bảo đủ mẫu đại diện, xử lý dữ liệu kiểu này khác gì thầy mù xem voi.
Y kiến của khán giả nhé.
Chẳng biết không có đủ dữ liệu thì cái chốt cuối cùng là dư lào. Hóng