Как извлекать ссылки из ячеек Excel?

С гиперлинками (hyperlinks) в Excel немного парадоксальная ситуация. Оформить ячейку или даже часть текста в ячейке(!) в виде линка очень просто, под это отведена отдельная формула. Но готового инструмента для обратного процесса нет!

Как создать гиперссылку

=ГИПЕРССЫЛКА(адрес; [имя])

В английской версии:

=HYPERLINK(link_location, [friendly_name])

Оба параметра гиперссылки могут задаваться как текстом так и быть ссылками на другие ячейки. Формула легко растягивается, обратить обычную url запись во что-нибудь более кликбейтное не представляет никакой сложности. Например:

hyperlink скриншот примера 1

Как получить гиперссылку

При копировании таблицы например из html в Excel вы получаете примерно такую проблему: как вытащить в соседнюю колонку B ссылку, которая явно есть в колонке А?

hyperlink скриншот примера 2

Нормальных решений 3 (даже где-то 3,5). Во-первых, можно все сделать «руками». Правой кнопкой мышки на тексте в ячейке, выбираем Изменить гиперссылку, затем копируем (или изменяем/удаляем) ссылку в строке Адрес. Если ссылок 2-3, норм. А если 1000?

Второй способ. Пишем новую функцию с помощью макроса, решение, предлагаемое много где в сети, в частности, здесь:

  1. Нажимаем Alt+F11 (вызов VBA)
  2. Выбираем Insert – Module
  3. Копируем в новый модуль следующий код:

Function GetURL(cell As range, Optional default_value As Variant)

‘Lists the Hyperlink Address for a Given Cell

‘If cell does not contain a hyperlink, return default_value

If (cell.range(«A1»).Hyperlinks.Count <> 1) Then

GetURL = default_value

Else

GetURL = cell.range(«A1»).Hyperlinks(1).Address

End If

End Function

hyperlink скриншот примера 3

  1. Нажимаем Alt+Q (закрываем VBA)

Теперь в ячейке набирая =GetURL(A2), получаем искомую ссылку.

Чуть упростим решение. Макрос выше проверяет, содержится ли ссылка в ячейке в принципе. Если такая ошибка вас не смущает, например, как у меня, у вас все строки в столбце содержат ссылки, то можно и сократить макрос до 1 строки.

Function GetURL(cell As Range) As String

GetURL = cell.Hyperlinks(1).Address

End Function

hyperlink скриншот примера 31

У вас может появится такая ошибка, если в проверяемом поле не все в порядке:

hyperlink скриншот примера 4

Наконец, третий способ: неужели никто до сих пор не озаботился об автоматизации подобных решений для Excel. Конечно, нет. Можно купить и поставить Kutools for Excel — надстройку для Excel, содержащую огромное количество доработок к стандартным возможностям Excel

После установки Kutools выбираем закладку Kuttols – Link – Convert Hyperlinks

Share Button

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.