Lọc gốc domain từ url hoặc email hàng loạt bằng excel

Các bạn làm seo, thường xuyên xuất ra file tổng hợp url trên các công cụ phân tích seo. ví dụ như ahrefs.

Việc đi xóa các url thủ công, để lấy domain gốc, phục vụ cho báo cáo hoặc lên kế hoạch seo thì hôm nay congthuc.vn hướng dẫn công thức lấy domain (tên miền) gốc từ tập tin excel như sau:

Ví dụ bạn muốn lấy url domain gốc như hình

Hàm công thức trong excel: =IF(ISERROR(FIND("//www.",A2)), MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))

A2: là ô chứa url của tên miền
Lưu lý: công thức trên cần phải có http:// hoặc https:// mới áp dụng được. Nếu link của bạn không có http hoặc https:// thì dùng công thức: ="http://"&A2 hoặc = "https://"&A2.

Kinh nghiệm của congthuc khi lọc xong tên miền:
Bôi màu (highlight) tên miền trùng lặp (duplicate Values):


Bước 1: Bôi đen vùng dữ liệu cần kiểm tra dữ liệu trùng

Bước 2: ở thẻ Home chọn Conditional Formatting -> Highlight Cells Rules -> Duplicate values như hình sau:






Bước 3: Hộp thoại mới Duplicate values xuất hiện chọn như hình sau




Sau khi bạn nhấn OK thì dữ liệu trùng sẽ được đánh đấu như hình trên (chế độ preview)
Từ ví dụ các bước trên, bạn sẽ thấy các hàng được đổi màu cho domain đang bị trùng

Xóa tên miền trùng lặp
Bước 1: Bôi đen chọn bảng dữ liệu

Bước 2: Thẻ Data chọn Remove Duplicates, chọn theo hướng dẫn ở hình sau rồi nhấn OK ta được kết quả



Sau khi nhấn OK sẽ xuất hiện hộp thoại thông báo số mẫu tin trùng bị xoá, và số mẫu tin còn lại.
Từ ví dụ trên, các domain trùng lập bây giờ đã được xóa
============================
Cách lấy gốc domain từ email:
Cách dùng hàm Excel như sau: =MID(Q2,FIND("@",Q2)+1,255)

 

Cách lấy đường link trong file Excel ẩn dưới text link
Đây cũng là cách hiện link trong excel

TRÍCH XUẤT ĐỊA CHỈ THỰC CỦA CÁC LIÊN KẾT VỚI TÍNH NĂNG EDIT HYPERLINK
Tính năng Edit Hyperlink có công dụng giúp bạn trích xuất các URL ẩn cho mỗi hyperlink và đặt các địa chỉ thực đó vào một ô khác. Bạn thực hiện các bước như sau:

Bước 1: Chọn ô có hyperlink, nhấp phải và chọn Edit Hyperlink.

Bước 2: Từ hộp thoại Edit Hyperlink, bạn tiến hành chọn và sao chép (Ctrl+C) phần đường dẫn từ ô Address



Bước 3: Đóng hộp thoại và dán địa chỉ liên kết vào bất kì ô nào mà bạn muốn.

Lưu ý: Đối với phương pháp này, bạn chỉ có thể lấy được 1 địa chỉ liên kết cho mỗi lần, nếunhư bạn có nhiều hyperlink, bạn sẽ phải lặp lại các bước cho mỗi lần trích xuất.

TRÍCH XUẤT ĐỊA CHỈ THỰC TỪ HYPERLINK VỚI MÃ VBA
Với nhiều hyperlink, nếu áp dụng phương pháp trên sẽ tốn nhiều thời gian, cho nên đoạn mã VBA sau sẽ giúp bạn cùng lúc trích xuất nhiều địa chỉ liên kết một cách nhanh chóng.

Bước 1: Tiến hành nhấn giữ ALT+F11 để thực hiện mở hộp thoại Microsoft Visual Basic for Applications.

Bước 2: Nhấn Insert > Module, và dán đoạn mã sau:


Sub Extracthyperlinks()

‘Updateby20140318

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

xTitleId = “Hoc Excel Online”

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)

For Each Rng In WorkRng

If Rng.Hyperlinks.Count > 0 Then

Rng.Value = Rng.Hyperlinks.Item(1).Address

End If

Next

End Sub
Bước 3: Nhấn F5 để chạy mã, với một hộp thoại hiện ra, yêu cầu bạn phải chọn những hyperlink bạn muốn trích xuất địa chỉ liên kết, như hình bên dưới

Bước 4: Nhấn OK, và các hyperlink đã được chuyển thành địa chỉ thực.

TRÍCH XUẤT ĐỊA CHỈ THỰC TỪ HYPERLINK VỚI HÀM DO NGƯỜI DÙNG TỰ TẠO
Hàm tự tạo sau có thể lấy được địa chỉ thực từ các Hyperlink.
Bước 1: Nhấn giữ ALT+F11 để tiến hành mở hộp thoại Microsoft Visual Basic for Applications.

Bước 2: Nhấn Insert > Module, và dán đoạn mã sau:

Function GetURL(pWorkRng As Range) As String
'Updateby20140520
GetURL = pWorkRng.Hyperlinks(1).Address
End Function
Bước 3: Thực hiện lưu đoạn mã và đóng cửa sổ, chọn một ô trống và nhập công thức sau: =GetURL(A2) (A2 là ô chứa hyperlink), sau đó nhấn Enter. Bạn có thể thấy địa chỉ liên kết đã được trích xuất.

Hi vọng bài viết về hướng dẫn hiện link trong file excel chi tiết trên đây sẽ giúp bạn thao tác nhanh hơn trên Excel!