Как быстро извлечь имена доменов из URL в Excel

Несколько хитростей и советов помогут Вам извлечь имена доменов из списка URL при помощи формул Excel. Два варианта формулы позволяют извлечь имена доменов с WWW или без, независимо от протокола URL (поддерживаются http, https, ftp и прочие). Решение работает во всех современных версиях Excel от 2003 до 2013.

Извлекаем имя домена при помощи Excel

Если Вы заботитесь о продвижении своего сайта (как я) или выполнении профессионального СЕО-продвижения сайтов Ваших клиентов за деньги, то часто будете сталкиваться с задачей обработки и анализа огромных списков URL: Google Analytics предоставляет отчёты о трафике, инструменты вебмастера сообщают о новых ссылках, об обратных ссылках на сайты Ваших конкурентов (которые содержат множество интересных фактов) и так далее.

Чтобы обрабатывать такие списки от десятка до миллиона ссылок, Microsoft Excel становится идеальным инструментом. Он мощный, гибкий, расширяемый и позволяет Вам отправлять отчёты клиентам прямо из листа Excel.

Почему мы называем диапазон от 10 до 1000000? – спросите Вы меня. Потому что Вам определённо не нужен инструмент для обработки меньше, чем 10 ссылок, и вряд ли он Вам нужен, если количество входящих ссылок у Вас больше миллиона. Готов поспорить, что в таком случае у Вас уже есть какое-то программное обеспечение, разработанное специально для Вас и адаптированное специально для нужд Вашего бизнеса. Плюс в этом случае я бы изучал Ваши статьи, а не наоборот :)

Анализируя список URL, перед Вами часто стоят такие задачи: выделить доменные имена для дальнейшей обработки, сгруппировать URL по доменам, удалить ссылки из уже обработанных доменов, сравнить и сделать слияние двух таблиц по доменным именам и так далее.

5 простых шагов, чтобы извлечь имена доменов из списка URL

В качестве примера давайте возьмём фрагмент отчёта об обратных ссылках, сгенерированного для сайта ablebits.com сервисом Google Webmaster Tools.

Извлекаем имя домена при помощи Excel

Совет: Я рекомендую пользоваться сервисом ahrefs.com, чтобы своевременно определять новые ссылки для Вашего собственного сайта и сайтов Ваших конкурентов.

  1. Добавьте столбец Domain в конец Вашей таблицы.

    Мы экспортировали данные из файла CSV, поэтому сейчас они занимают простой диапазон. Нажмите Ctrl+T, чтобы преобразовать его в таблицу Excel, поскольку так работать будет значительно удобнее.

    Извлекаем имя домена при помощи Excel

  2. В первой ячейке столбца Domain (B2) введите формулу для извлечения имени домена:
    • Извлекаем домен с "WWW." если он присутствует в URL:

      =MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
      =ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3)

    • Пропускаем "WWW." и извлекаем чистое имя домена:

      =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))
      =ЕСЛИ(ЕОШИБКА(НАЙТИ("//www.";A2));ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3);ПСТР(A2;НАЙТИ(":";A2;4)+7;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-7))

    Извлекаем имя домена при помощи Excel

    Вторая формула может показаться слишком длинной и сложной, но только если Вы не видели действительно длинных формул. Microsoft не без оснований увеличила максимальную длину формул до 8192 символов для новых версий Excel :)

    Плюс в том, что нам не нужен ни вспомогательный столбец, ни макрос VBA. На самом деле, использование макросов VBA для автоматизации задач в Excel – это не так сложно, как может показаться, подробнее об этом читайте в отличной статье – Учебник по макросам в Excel с примерами. Но в данном случае мы на самом деле не нуждаемся в них: быстрее и проще будет сделать это при помощи формулы.

Замечание: Технически, "WWW" – это домен третьего уровня, хотя во всех нормальных сайтах "WWW." используется как приставка к имени домена первого уровня. На заре развития интернета Вы могли сказать по телефону или в рекламе на радио: Вэ-Вэ-Вэ, Наш_Крутой_Сайт, Точка Ком, – и все прекрасно понимали и запоминали, где Вас можно найти… конечно, если имя Вашего крутого сайта было не http://www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com/ :)

Вам необходимо оставить все другие доменные имена 3-го уровня, иначе Вы можете перемешать ссылки с разных сайтов, например, с доменом CO.UK или из разных аккаунтов на BLOGSPOT.COM и так далее.

  1. Так как мы имеем полноценную таблицу, Excel автоматически копирует формулу во все строки столбца.

Готово! У нас получился столбец с извлечёнными доменными именами.

Извлекаем имя домена при помощи Excel

В следующей части Вы узнаете, как можно обрабатывать список URL, используя столбец Domain.

Подсказка: Если понадобится редактировать доменные имена вручную или копировать результаты на другой лист Excel, замените формулы их значениями. Для этого выполните следующие шаги:

  • Кликните по любой ячейке в столбце Domain и нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в этом столбце.
  • Нажмите Ctrl+C, чтобы скопировать данные в буфер обмена, затем откройте вкладку Home (Главная), нажмите кнопку выпадающего списка Paste (Вставить) и выберите вариант Value (Значения).

    Извлекаем имя домена при помощи Excel

Обработка списка URL по столбцу с доменным именем

В этой части статьи Вы найдёте несколько советов из моего собственного опыта по дальнейшей обработке списка URL.

Группируем URL по доменному имени

  1. Кликните любую ячейку в столбце Domain.
  2. Отсортируйте таблицу по столбцу Domain: на вкладке Data (Данные) нажмите кнопку A-Z (А-Я).
  3. Преобразуйте таблицу обратно в диапазон: кликните любую ячейку таблицы, откройте вкладку Design (Конструктор) и нажмите кнопку Convert to the range (Преобразовать в диапазон).
  4. На вкладке Data (Данные) кликните иконку Subtotal (Промежуточный итог).
  5. В диалоговом окне Subtotal (Промежуточные итоги) установите вот такие параметры: в строке At each change in (При каждом изменении в) – значение "Domain"; в строке Use function (Операция) – значение "Count" (Количество); в строке Add subtotal to (Добавить итоги по) – значение "Domain".

    Извлекаем имя домена при помощи Excel

  6. Нажмите ОК.

Excel создаст структуру Ваших данных в левой части экрана. Структура в нашем случае состоит из 3 уровней. То, что Вы видите на снимке экрана ниже, это развёрнутый вид или вид 3 уровня. Кликните цифру 2 в верхнем левом углу экрана, чтобы отобразить итоговые данные по доменам. Далее Вы можете нажимать знаки плюс и минус (+/-), чтобы развернуть/свернуть детализацию по каждому домену.

Извлекаем имя домена при помощи Excel

Выделяем цветом второй и все последующие URL в общем домене

В предыдущей части статьи мы показывали, как группировать URL по домену. Вместо группировки, Вы можете быстро раскрасить повторяющиеся записи в Ваших URL.

Более подробно об этом методе читайте в статье Как в Excel автоматически выделить дубликаты.

Извлекаем имя домена при помощи Excel

Сравниваем URL из различных таблиц по столбцу доменных имён

У Вас может быть один или несколько отдельных листов Excel, в которых содержится список доменных имён. Ваши таблицы могут содержать ссылки, с которыми Вы не хотите возиться, такие как спам или домены, которые Вы уже обработали. Возможно, Вам потребуется сохранить список доменов с интересными ссылками и удалить остальные.

Например, моя задача – выделить красным цветом все домены, которые занесены в мой чёрный список спамеров.

Извлекаем имя домена при помощи Excel

Не теряя много времени, Вы можете сравнить Ваши таблицы и удалить ненужные ссылки. Об этом читайте в статье Как сравнить два столбца и удалить дубликаты в Excel.

02.07.2015 00:00
2572

4 комментария

10.03.2016 21:23
Андрей отличная статья! мне очень пригодилась эта формула, для изьятия домена из ячейки
=ЕСЛИ(ЕОШИБКА(НАЙТИ("//www.";A2)); ПСТР(A2; НАЙТИ(":";A2;4)+3; НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3); ПСТР(A2; НАЙТИ(":";A2;4)+7; НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-7))

www.delightenglish.ru/Videocurs.htm Сохраненная копияПохожиеВидеокурс для изучения английского языка смотреть бесплатно онлайн, английский для начинающих, тесты по английскому языку онлайн,… Видеокурс состоит из уроков, диалогов и тестов на английском языке с переводом.

www.efl.ru › faq Сохраненная копия Похожие Учил ее по учебнику Е.Е.Израилевич и К.Н.Качаловой «Практическая грамматика английского языка с упражнениями и ключами». Считаю, что это очень …

audio-class.ru/articles/english-fast.php

и тому подобных. Честно сказать, саму формулу я не понимаю, но прошу помочь подсказать как ее смодулировать…
Спасибо!
Здравствуйте! Прежде чем модулировать формулу, поймите несколько моментов:

1. Не понятно откуда вы взяли такие ссылки. Это не URL адреса! Очень похоже, что вы просто скопировали поисковую выдачу googla или яндекса и пытаетесь достать оттуда домены.

2. Данная формула отталкивается от URL адресов типа http:..., https:... и прочие. Сама формула вначале проверяет есть ли в адресе «www», затем ищет в адресе двоеточие ":", а затем возвращает адрес домена, который якобы находится между двоеточием и 1-м слешем после двоеточия. Чувствуете закономерность?

Основой формулы является функция ЕСЛИ, которая содержит условие:
ЕОШИБКА(НАЙТИ("//www.";A2)) — данная формула ищет в адресе строку "//www.". Если ее нет, то выполняется эта формула:
ПСТР(A2; НАЙТИ(":";A2;4)+3; НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3)
а если есть, то эта формула:
ПСТР(A2; НАЙТИ(":";A2;4)+7; НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-7)

Формулы абсолютно идентичны, разница лишь в 3 и 7, которые как раз таки учитывают, либо не учитывают www в домене.

Коротко об одной из этих формул

Вот, что мы ищем (выделено жирным):
http://office-guru.ru/excel/kak-bystro-izvlech-imena-domenov-iz-url-v-excel-289.html

Вот формула (домен без www):
ПСТР(A2; НАЙТИ(":";A2;4)+3; НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3)

A2 — это строка с адресом.
НАЙТИ(":";A2;4) — формула ищет двоеточие в строке, начиная с 4-го символа. т.е. 5
НАЙТИ(":";A2;4)+3 — формула возвращает позицию 1-го символа названия домена. 5+3 = 8, т.е. буква «o» занимает 8-ю позицию.
НАЙТИ("/";A2;9) — формула ищет слеш, начиная с 9 символа. т.е. 22
НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3 — формула возвращает число символов, из которых состоит имя домена. 22-5-3 = 14

Если вместо исходной громоздкой формулы подставить получившиеся числа, получим:
ПСТР(A2; 8; 14) — т.е. формула вернет 14 символов из ячейки A2, начиная с 8-го символа.

Вторая формула (для адресов с www) идентична этой. Надеюсь стало понятнее.

3. В тех ссылках, что вы привели творится хаос. Нет четкой закономерности. Если есть уверенность, что все ваши ссылки будут начинаться с имени домена (с www или без www) или еще с чего-то конкретного, а имя домена будет заканчиваться слешем или, к примеру, ".ru", то формулу создать получится. Если же закономерностей в ссылках не будет, то ничего не получится.

К тому же стоит учитывать, что лишние пробелы и невидимые символы также могут помешать. Такие вещи часто появляются, если копировать текст прямо из интернета в Excel. Такой текст необходимо предварительно обрабатывать, либо заранее позаботится о его «чистоте».
12.03.2016 00:50
Андрей спасибо за подробный комментарий, теперь формула понятна. Эврика!!! :)
Но!
по работе мне все таки нужно создать формулу которая будет вытаскивать большинство из такого рода доменов (для анализа CEO оптимизации)
Поэтому. Начинаю писать формулу, заменив первую функцию ЕСЛИ на ЕОШИБКА, и подставлять в условие значение_если_ложь внутривложенные ЕОШИБКА, для поиска просто www без //, для поиска только одного слэша, и т.д. т.е. вложить несколько проверок в одну формулу. Начинаешь ее писать и быстро теряется сама формула, т.е. глаза разбегаются :) и формула не получается…
Как укоротить формулу, чтобы ее можно было «прочитать», пока не понимаю… есть мысли?
Спасибо заранее )
Вы опишите, что будут представлять из себя ссылки. Как начинаться, как заканчиваться. Какие варианты могут быть. Просто словами опишите. Так понятнее будет, в первую очередь вам, что делать. Чем больше всяких разных вариантов, тем сложнее будет формула. Даже не сложнее, а скорее более громоздкая.

Те 3 ссылки, что вы представили выше можно описать так:
— часть ссылок начинается с имени домена, часть с “www.
— имя домена заканчивается либо "/" (1-я и 3-я ссылки), либо " > " (2-я ссылка).

Первым условием можно проверить с чего начинается домен, например, так:
=ЕСЛИ(ЛЕВСИМВ(A1;4)=“www.”; значение_если_истина; значение_если_ложь).

Значение_если_истина и значение_если_ложь — функция ЕСЛИ, условием которой, к примеру, является поиск строки " > " (пробел, знак больше, пробел). Если такая строка есть, то возвращаем строку до первого пробела, а если нет, то до первого слеша. Все очень похоже на вышеописанные формулы.