Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn lấy tên miền từ danh sách các URL bằng công thức Excel. Hai biến của công thức này cho phép bạn lấy tên miền có chứa hoặc không chứa “www.” với bất kỳ giao thức nào (http, https, fpt…). Giải pháp có hiệu quả trong mọi phiên bản Excel hiện đại từ 2010-2016.

5 bước đơn giản để lấy tên miền từ danh sách các URL

Ta có bảng sau đây:

1. Thêm cột ‘Domain’ vào cuối bảng dữ liệu.

Chúng ta xuất dữ liệu từ tệp CSV, đó là lý do tại sao dữ liệu Excel của chúng ta chỉ ở dạng cơ bản. Nhấn Ctrl + T để chuyển đổi chúng thành bảng biểu Excel hoàn chỉnh.

2. Trong ô tính đầu tiên của cột ‘Domain’ (B2), nhập công thức để xuất tên miền:

• Xuất tên miền có chứa ‘www.’ nếu hiển thị trong URL :

=MID(A2,FIND(“:”,A2,4)+3,FIND(“/”,A2,9)-FIND(“:”,A2,4)-3)

• Loại bỏ ‘www.’, chỉ lấy tên miền:

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

Chú ý. Về mặt kỹ thuật, ‘www’ là tên miền cấp 3, mặc dù với tất cả các trang web bình thường, ‘www.’ chỉ là một bí danh của miền chính.

Bạn cần để lại tất cả các tên miền cấp 3 khác, nếu không bạn sẽ khiến các liên kết trở nên lẫn lộn, ví dụ: với tên miền “co.uk” hoặc từ các tài khoản khác nhau trên blogspot.com…

3. Sau khi chúng ta có một bảng hoàn chỉnh, Excel sẽ tự động sao chép công thức sang các ô tính khác trong cột. Như vậy, chúng ta có một cột chứa tên miền trích xuất.

Trong phần tiếp theo, bạn sẽ học cách xử lý một danh sách URL dựa trên cột ‘Domain’.

Chú ý. Nếu bạn cần chỉnh sửa tên miền hoặc sao chép kết quả vào một trang tính Excel khác, hãy thay thế kết quả công thức bằng các giá trị. Để thực hiện việc này, thực hiện theo các bước sau:

• Nhấp vào bất kỳ ô nào trong cột ‘Domain’ và nhấn Ctrl + Space để chọn tất cả các ô trong cột đó.
• Nhấn Ctrl + C để sao chép dữ liệu vào Clipboard, sau đó di chuyển đến thẻ Home, nhấn Paste và chọn Value trong danh sách tùy chọn.

Xử lý một danh sách URL dựa trên cột Domain

Nhóm các URL bằng tên miền

1. Nhấp vào bất kỳ ô tính nào trong cột ‘Domain’.
2. Sắp xếp bảng dữ liệu theo tên miền: di chuyển đến thẻ Data và nhấn nút A-Z.
3. Chuyển đổi dữ liệu dạng bảng thành dạng bình thường: nhấp vào ô bất kỳ trong bảng, di chuyển đến thẻ Design và nhấn nút Convert to the range.
4. Di chuyển đến thẻ Data và nhấp vào biểu tượng Subtotal.
5. Trong hộp thoại Subtotal, chọn các tùy chọn sau: chọn Domain trong At each change in:, chọn Count trong Use function:, đánh dấu chọn Domain trong Add subtotal to:

6. Nhấn OK.

Excel đã tạo một phụ lục cho dữ liệu ở phía bên trái màn hình. Có 3 cấp độ khác nhau và những gì bạn nhìn thấy trên hình là chế độ xem mở rộng, (hoặc cấp độ 3). Nhấp vào số 2 ở góc trên bên trái để hiển thị dữ liệu cuối cùng theo tên miền, sau đó bạn có thể nhấp vào dấu cộng và dấu trừ (+/-) để mở rộng/thu gọn chi tiết đối với mỗi tên miền. 

Highlight URL thứ hai và tất cả các URL trong cùng một tên miền

Trong phần trước, chúng tôi đã chỉ ra cách nhóm URL theo tên miền. Thay vì nhóm, bạn có thể nhanh chóng tô màu các mục trùng lặp của cùng một tên miền trong URL.

Chi tiết hơn, mời các bạn xem bài Cách highlight tự động những mục trung lặp trong Excel 

So sánh URL từ các bảng dữ liệu khác nhau bằng cột domain

 Bạn có thể có một hoặc nhiều trang tính Excel riêng biệt, nơi bạn lưu giữ một danh sách các tên miền. Các bảng biểu có thể chứa các liên kết mà bạn không muốn sử dụng, như liên kết rác hoặc những tên miền đã được sử dụng. Hoặc bạn muốn giữ danh sách chỉ gồm các liên kết hữu ích và xóa toàn bộ những liên kết còn khác.

Ví dụ: nhiệm vụ của tôi là tô màu đỏ tất cả các tên miền trong danh sách đen của mình:

Để không lãng phí thời gian, bạn có thể so sánh bảng biểu của mình với danh sách những liên kết không cần thiết. Chi tiết hơn, mời các bạn đọc bài viết Cách so sánh hai cột trong Excel và xóa những cột trùng lặp (bôi đậm, tô màu, xóa bỏ)

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.