Làm thế nào để tách email trong dữ liệu text? Làm thế nào để tách email thành user và domain rời? Làm thế nào để tách email từ một loạt chuỗi gmail? Hãy cùng Kế Toán VN tìm hiểu chi tiết dưới đây:
1. Cách tách email trong Excel
Giả sử bạn có một danh sách gmail và muốn tách thành 2 cột (1 cột tên user và 1 cột tên domain) như hình dưới đây:
Cách 1: Sử dụng các công thức
Để tách email thành domain và user ta có thể sử dụng các hàm sau:
Hàm TEXTBEFORE và TEXTAFTER
Ví dụ A2 là ô dữ liệu chứa gmail thì ta sẽ sử dụng công thức sau:
- Để tách User:
=TEXTBEFORE(A2,"@") - Để tách Domain:
=TEXTAFTER(A2,"@")
Ta có thể sử dụng hàm IFERROR để kiểm tra xem email có hợp lệ hay không bằng công thức sau:
=IFERROR(AND(ISNUMBER(FIND("@",A2)), ISNUMBER(FIND(".", MID(A2, FIND("@",A2)+1, 99)))) , FALSE)
Hàm FIND kết hợp với hàm MID và LEFT
Để tách gmail trong Excel ta cũng có thể sử dụng hàm FIND kết hợp với các hàm LEN, MID và LEFT như sau:
- Tách User:
=LEFT(A2, FIND("@",A2)-1) - Tách Domain:
=MID(A2, FIND("@",A2)+1, LEN(A2)-FIND("@",A2))
Cách 2: Tách email bằng Text to Colums trong Excel
Ngoài cách sử dụng công thức, ta có thể tách email thành domain và user bằng tính năng Text to Colums như sau:
- Bước 1: Bôi đen vùng dữ liệu Email cần tách => vào thẻ Data => Chọn Text to Columns
- Bước 2: Khi cửa sổ Text to Columns xuất hiện => Chọn Delimited => chọn Next
- Bước 3: Tích vào ô Other và điền
@vào hộp Next, lúc này ta có thể xem trước kết quả tách email ở cửa sổ bên dưới. - Bước 4: Chọn Next => Chọn 1 ô để đặt kết quả => chọn Finish để địa chỉ email được tách thành domain và User.
2. Cách lọc địa chỉ Email trong Excel
Để lọc địa chỉ Email từ các văn bản chẳng hạn như ảnh dưới đây:
Ta có thể sử dụng các cách sau:
Cách 1: Sử dụng FIND and Replace
Ví dụ với bảng dữ liệu gmail trộn lẫn vào câu chat, van bản như sau:
Ta sử dụng Find And Replace như sau:
- Xóa các văn bản phía sau Gmail:
- Bước 1: Bôi đen vùng dữ liệu cần lọc email => Chọn Find & Select => chọn Replace (hoặc sử dụng phím tắt Ctrl + H)
- Bước 2: Khi cửa sổ Find and Replace xuất hiện => chọn tab Replace
- Bước 3: Tại mục Find What điền
.com*và mục Replace điền.com=> Ok
- Xóa các văn bản phía trước Gmail:
- Bước 1: Bôi đen vùng email đã lọc văn bản phía sau gmail => Chọn Find & Select => chọn Replace (hoặc sử dụng phím tắt Ctrl + H)
- Bước 2: Khi cửa sổ Find and Replace xuất hiện => chọn tab Replace
- Bước 3: Tại mục Find What điền * dấu cách (
*)và mục Replace không điền gì => Ok
Khi đó Excel sẽ thông báo số ký tự tìm kiếm được theo nội dung tìm kiếm mà người dùng thiết lập. Bạn nhấn OK để đồng ý như hình dưới đây.
Kết quả trên màn hình danh sách email sẽ loại bỏ toàn bộ nội dung ở đằng sau địa chỉ email.
Tiếp đến mở lại giao diện Find and Replace. Tại Find what nhập * dấu cách, còn phần Replace không nhập gì. Tiếp theo bạn nhấn Replace all để loại bỏ các ký tự ở đầu Email.
Trên màn hình sẽ xuất hiện thông báo số lượng ký tự tìm được, bạn nhấn OK để thay thế.
Sau khi hoàn tất, toàn bộ nội dung phía đầu của email đã được loại bỏ, bao gồm cả dấu cách. Nhờ vậy danh sách địa chỉ email trên Excel đã được rút ngắn hơn rất nhiều.
Cách 2: Sử dụng hàm để lọc Gmail
Ngoài cách sử dụng Find and Replace để, ta có thể sử dụng các hàm và công thức như sau:
1. Sử dụng hàm để lọc gmail từ văn bản:
Giả sử với bảng dữ liệu gồm cả văn bản lẫn với gmail như sau:
Để lọc email ta sử dụng hàm REGEXEXTRACT như sau:
=IFERROR(REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),"")
Kết quả hàm sẽ trả về như sau:
2. Sử dụng hàm lọc email từ 1 chuỗi các email
Giả sử ta có bảng dữ liêu nhưng các email nó thành chuỗi với nhau như ảnh dưới đây:
Ta lọc một chuỗi email thành các email riêng biệt bằng công thức như sau:
=LET(arr, TEXTSPLIT(A2, {" ", ",", ";"}),flat, TOCOL(TRIM(arr)),emails, FILTER(flat,ISNUMBER(SEARCH("@", flat))),IFERROR(emails, ""))
Kết quả như sau:
Trên đây là hướng dẫn chi tiết cách để tách địa chỉ email và lọc địa chỉ email trong Excel. Mong rằng bài viết hữu ích và chúc các bạn áp dụng thành công!
