Tìm và trả về đoạn text có chứa ký tự cho trước

[dzung_nguyen]

Chào các bạn,

Mình có một danh sách liên lạc, trong đó có địa chỉ email nằm dải dác (trong một cột thôi nhưng ô có ô không, trong một ô còn có những thông tin khác nữa). Mình muốn chích ra những địa chỉ email này sang một cột khác. Mong được các bạn quan tâm giúp đỡ.

Rất cảm ơn các bạn!
Dzung_nguyen

 

[ndu96081631]

Chào các bạn,

Mình có một danh sách liên lạc, trong đó có địa chỉ email nằm dải dác (trong một cột thôi nhưng ô có ô không, trong một ô còn có những thông tin khác nữa). Mình muốn chích ra những địa chỉ email này sang một cột khác. Mong được các bạn quan tâm giúp đỡ.

Rất cảm ơn các bạn!
Dzung_nguyen

Dùng AutoFilter là cách nhanh nhất:
- Quét chọn cột có chứa địa chỉ mail
- Vào menu Data\Filter\AutoFilter
- Bấm vào mũi tên xổ xuống của AutoFilter, chọn Custom
- Gõ vào điều kiện tìm là: *@*
- Copy list đã được lọc rồi paste sang nơi khác
===> Nếu muốn dùng code để trích xuất, tôi nghĩ ta cũng nên dựa vào cách làm ở trên ---> Sẽ nhanh gọn hơn For rất nhiều

 

[rollover79]

Dùng AutoFilter là cách nhanh nhất:
- Quét chọn cột có chứa địa chỉ mail
- Vào menu Data\Filter\AutoFilter
- Bấm vào mũi tên xổ xuống của AutoFilter, chọn Custom
- Gõ vào điều kiện tìm là: *@*
- Copy list đã được lọc rồi paste sang nơi khác
===> Nếu muốn dùng code để trích xuất, tôi nghĩ ta cũng nên dựa vào cách làm ở trên ---> Sẽ nhanh gọn hơn For rất nhiều

Hình như chưa đúng với ý tác giả lắm, như thế này thì mới chỉ là trích ra những ô có chưa địa chỉ email thôi, còn tác giả muốn trích địa chỉ email mà tại vì tôi đọc đoạn này và hiểu là vậy.

(trong một cột thôi nhưng ô có ô không, trong một ô còn có những thông tin khác nữa). Mình muốn chích ra những địa chỉ email này sang một cột khác

Nếu đúng yêu cầu này thì cần phải hỏi lại tác giả là những thông tin khác có phân cách với địa chỉ email bởi 1 hoặc nhiều dấu cách, hoặc 1 ký tự nào đó không? Nếu có thì bài này mới có khả năng giải quyết, còn nếu nó liền với nhau không phân cách thì tôi nghĩ là không có khả năng để giải quyết bài này.

 

[ndu96081631]

Hình như chưa đúng với ý tác giả lắm, như thế này thì mới chỉ là trích ra những ô có chưa địa chỉ email thôi, còn tác giả muốn trích địa chỉ email mà tại vì tôi đọc đoạn này và hiểu là vậy.

Nếu đúng yêu cầu này thì cần phải hỏi lại tác giả là những thông tin khác có phân cách với địa chỉ email bởi 1 hoặc nhiều dấu cách, hoặc 1 ký tự nào đó không? Nếu có thì bài này mới có khả năng giải quyết, còn nếu nó liền với nhau không phân cách thì tôi nghĩ là không có khả năng để giải quyết bài này.

Đúng vậy... Nhưng hãy tách cái nào chắc ăn có chưa địa chỉ mail ra trước... Việc còn lại chỉ là các hàm xử lý TEXT ---> Quá bình thường

 

[rollover79]

Đúng vậy... Nhưng hãy tách cái nào chắc ăn có chưa địa chỉ mail ra trước... Việc còn lại chỉ là các hàm xử lý TEXT ---> Quá bình thường

Nếu những dữ liệu khác kia mà có chưa cả ký tự @ nữa thì chắc cũng không bình thường chút nào . Có lẽ phải chờ tác giả mô tả kỹ hơn mới có thể giải quyết được.

 

[ndu96081631]

Nếu những dữ liệu khác kia mà có chưa cả ký tự @ nữa thì chắc cũng không bình thường chút nào . Có lẽ phải chờ tác giả mô tả kỹ hơn mới có thể giải quyết được.

Cho dù bạn làm bằng VBA đi nữa thì ăn chắc cũng phải dựa vào ký tự @ thôi ---> Chuyện sai sót đương nhiên có thể xãy ra, nhưng tôi nghĩ nó chiếm tỉ lệ nhỏ lắm ---> Có thể sửa sau khi đã Convert
Khổ 1 nỗi: Tác giã hỏi xong chả thấy ý kiến ý cò gì (file cũng chả thấy)... làm anh em mình đoán già đoán non
Hic...

 

[dzung_nguyen]

Hình như chưa đúng với ý tác giả lắm, như thế này thì mới chỉ là trích ra những ô có chưa địa chỉ email thôi, còn tác giả muốn trích địa chỉ email mà tại vì tôi đọc đoạn này và hiểu là vậy.

Nếu đúng yêu cầu này thì cần phải hỏi lại tác giả là những thông tin khác có phân cách với địa chỉ email bởi 1 hoặc nhiều dấu cách, hoặc 1 ký tự nào đó không? Nếu có thì bài này mới có khả năng giải quyết, còn nếu nó liền với nhau không phân cách thì tôi nghĩ là không có khả năng để giải quyết bài này.

Cảm ơn các bạn nhiều vì đã nhiệt tình giúp mình.
Trong ô chứa email này không có thông tin khác chứa ký tự "@", chỉ duy nhất có email mới có thôi. Các thông tin trong ô có cách nhau (các) khoảng trắng. Như bạn nói ở đây là đúng ý mình. Các bạn có thể dùng cách nào đó gắn với ký tự "@" này để chích email ra là ok rồi.
Mình muốn post file lên để các bạn xử lý cho nhanh mà ko biết attact ở đâu, các bạn thông cảm xíu.

Cảm ơn các bạn nhiều.

 

[BNTT]

Bạn Dzung nguyen nó vậy là khá rõ rồi đó.
Tôi thì làm không được, nhưng xin gợi ý phương pháp để tìm ra cái địa chỉ email trong một chuỗi:

Tìm từ đầu đến khi nào gặp ký tự @, thì tìm lui lại để lấy được một ký tự [Space] trước ký tự @, rồi tìm tiếp một ký tự [Space] sau ký tự @. Cái nằm ở giữa hai ký tự Space tìm được là cái địa chỉ email cần lấy ra.​

Trong trường hợp không tìm ra ký tự [Space] nào nằm ở trước ký tự @, thì cái địa chỉ email này nằm ở ngay đầu chuỗi.

Tương tự, trường hợp không tìm ra ký tự [Space] nào nằm ở sau ký tự @, thì cái địa chỉ email này nằm ở cuối chuỗi.

Tuy nhiên, điều này chỉ chính xác nếu như địa chỉ email không có khoảng trắng trong đó.

 

[ndu96081631]

Bài này cũng dể mà... giống như tách tên ấy
- Đầu tiên dùng SUBSTITUTE biến ký tự rỗng thành 255 ký tự rỗng khác
- Xác định vị trí của ký tự @ ---> Quét ngược về bên trái 255 ký tự và về bên phải 255 ký tự ---> Ta lấy ra được ngay địa chỉ mail
Ví dụ:
=TRIM(MID(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",10000)),FIND("@",SUBSTITUTE(" "&$A2&" "," ",REPT(" ",10000)),1)-255,510))
Có thể đặt name cho gọn hơn

 

[BNTT]

Anh ndu96081631 ơi, xem lại dùm em trường hợp địa chỉ email nằm ở giữa chuỗi, nó báo #VALUE!
Em đã cố gắng mò mãi mà chưa tìm ra sai ở đâu...

Công thức trong cột F là công thức của anh, ở bài trên.
Các trường hợp ở A2, A3, A4 đều chạy tốt. Nhưng giống như ở A5 thì không.

 

[ndu96081631]

Anh ndu96081631 ơi, xem lại dùm em trường hợp địa chỉ email nằm ở giữa chuỗi, nó báo #VALUE!
Em đã cố gắng mò mãi mà chưa tìm ra sai ở đâu...

Công thức trong cột F là công thức của anh, ở bài trên.
Các trường hợp ở A2, A3, A4 đều chạy tốt. Nhưng giống như ở A5 thì không.

Mai mốt anh Hai đưa file lên luôn (mất công giã lập quá)
Hiii...
Giãm bớt... 10000 thành 1000 xem .. hoặc 255 càng tốt ---> Vì lúc nãy "thử lửa" ... chưa kịp sửa lại

 

[ThuNghi]

Mai mốt anh Hai đưa file lên luôn (mất công giã lập quá)
Hiii...
Giãm bớt... 10000 thành 1000 xem .. hoặc 255 càng tốt ---> Vì lúc nãy "thử lửa" ... chưa kịp sửa lại

Bác Ndu rút gọn lại hộ, xin cám ơn!
Dùng thử ct sau:

=IF(ISERROR(FIND("@",$A1,1)),"No",IF(ISERROR(FIND(" ",$A1,FIND("@",$A1,1))),TRIM(RIGHT(SUBSTITUTE($A1," ",REPT(" ",500)),500)),TRIM(RIGHT(SUBSTITUTE(LEFT(Sheet1!$A1,FIND(" ",Sheet1!$A1,FIND("@",Sheet1!$A1,1))-1)," ",REPT(" ",500)),500))))

 

[BNTT]

Mai mốt anh Hai đưa file lên luôn (mất công giã lập quá)
Hiii...
Giãm bớt... 10000 thành 1000 xem .. hoặc 255 càng tốt ---> Vì lúc nãy "thử lửa" ... chưa kịp sửa lại

Dạ, file đây ạ. Có chút xíu vậy mà cũng.. làm biếng! (nói vui thôi nha)
Thay 10000 bằng 255 chạy tốt rồi anh à. Nhưng tại sao như vậy?

Trong file em có một vấn đề bé tí... hỏi cả hai anh.

 

[ThuNghi]

Dạ, file đây ạ. Có chút xíu vậy mà cũng.. làm biếng! (nói vui thôi nha)
Thay 10000 bằng 255 chạy tốt rồi anh à. Nhưng tại sao như vậy?

Trong file em có một vấn đề bé tí... hỏi cả hai anh.

Lồng thêm 1 cái thay thế "," = ""
SUBSTITUTE(....,",","")

 

[rollover79]

Em xin phép được bình "loạn" chút nhé, cả 2 cách của 2 bác ThuNghi và Ndu vẫn còn có 1 hạn chế nho nhỏ, đó là việc các bác thay thế mỗi dấu cách bởi 1 số lượng dấu cách nhất định để thực hiện thì kết quả chỉ trả về đúng khi độ dài của địa chỉ email không vượt quá số lượng các dấu cách mà các bác thay thế, nếu nó vượt qua thì kết quả của các bác cũng chưa được chính xác. Dĩ nhiên về mặt thực tế thì nó vẫn cho kết quả đúng vì chẳng có địa chỉ email nào lại dài tới mức vậy .

 

[ndu96081631]

Em xin phép được bình "loạn" chút nhé, cả 2 cách của 2 bác ThuNghi và Ndu vẫn còn có 1 hạn chế nho nhỏ, đó là việc các bác thay thế mỗi dấu cách bởi 1 số lượng dấu cách nhất định để thực hiện thì kết quả chỉ trả về đúng khi độ dài của địa chỉ email không vượt quá số lượng các dấu cách mà các bác thay thế, nếu nó vượt qua thì kết quả của các bác cũng chưa được chính xác. Dĩ nhiên về mặt thực tế thì nó vẫn cho kết quả đúng vì chẳng có địa chỉ email nào lại dài tới mức vậy .

Đồng ý điều này...
Nhưng rõ ràng đây là phương pháp cho tốc độ cao hơn là FOR ---> Thuật toán được xây dựng dựa trên dử liệu cơ mà ---> Nếu dử liệu thật khác đi, ta sẽ làm khác đi...
Với ThuNghi:
- Ngoài ISERROR(FIND... ) ta có thể dùng COUNTIF
- Thay vì xác định xem chuổi có ký tự rỗng hay không (IF(ISERROR(FIND(" "...) anh có thể làm như tôi đã làm: cho 2 khoảng trắng vào đầu và cuối chuổi
Với BNTT:
- Tôi nghĩ việc xử lý dấu phẩy thừa ấy không mấy quan trọng... vì rõ ràng đó là lổi nhập liệu của ta ---> Tại sao ta không tạo cho đ/c email cách biệt với các phần khác? Dính vào dấu phẩy làm gì (tự mình làm khó mình)

Có chút xíu vậy mà cũng.. làm biếng! (nói vui thôi nha)
Thay 10000 bằng 255 chạy tốt rồi anh à. Nhưng tại sao như vậy?.

- Có lẽ nó bị "quá tải" thôi mà
----------
Cuối cùng, để công thức được hoàn hảo và ngắn ngọn hơn, ta nên dùng name, chẳng hạn:

Temp =SUBSTITUTE(" "&Sheet1!$A3&" "," ",REPT(" ",255))

rồi công thức:

=IF(COUNTIF($A3,"*@*"),TRIM(MID(Temp,FIND("@",Temp,1)-255,510)),"")

 

[rollover79]

Tôi vừa tình cờ thấy có 1 người có cách giải bài này theo 1 cách khác, xin được đưa lên đây mọi người cùng tham khảo. Cách này tôi thấy không bị hạn chế về độ dài của email, không phải thay thế dấu cách.

 

[Mr Okebab]

Mệt quá nhỉ, làm một cái UDF cho nó đơn giản.

Nhìn công thức của các bác thấy choáng quá.

Thân!

 

[ThuNghi]

Tôi vừa tình cờ thấy có 1 người có cách giải bài này theo 1 cách khác, xin được đưa lên đây mọi người cùng tham khảo. Cách này tôi thấy không bị hạn chế về độ dài của email, không phải thay thế dấu cách.

Dùng {...} mệt lắm, theo yc của Mr OKeBab làm đại 1 UDF vậy.

Option Explicit
Private Function LayMail(myRng As Range) As String
Dim lVitri As Byte, sText As String
If Len(myRng) = 0 Then Exit Function
sText = Trim(CStr(myRng))
lVitri = InStr(1, sText, "@")
If lVitri > 0 Then
    lVitri = InStr(lVitri, sText, " ")
    If lVitri > 0 Then
        sText = Trim(Left(sText, lVitri))
    End If
End If
lVitri = InStrRev(sText, " ")
If lVitri > 0 Then
    sText = Trim(Right(sText, Len(sText) - lVitri))
End If
sText = Replace(Trim(sText), ",", "")
LayMail = sText
End Function

 

[rollover79]

Mệt quá nhỉ, làm một cái UDF cho nó đơn giản.

Nhìn công thức của các bác thấy choáng quá.

Thân!

Mỗi bài đều có nhiều cách giải mà, đã nói đến UDF thì bài này đâu cần phải bàn, quá đơn giản rồi, chỉ cần 1 dòng lệnh là ra ngay vì VBA có hàm InStrRev. Cái ý mà tôi muốn nói ở cái công thức mảng ở đây là nó cho ta kết quả giống như InStrRev của VBA(chỉ có điều nó không có cái Start giống InStrRev nên phải tạo cột phụ), cái này tôi tìm hiểu lâu nay mà chưa thấy.