Excel works!
Как получить ссылку из ячейки с гиперссылкой в Excel? Вытаскиваем адрес ссылки формулой
Ноябрь 17, 2017 / Написал Izotov / 1 Comment
Мы уже писали о том, что такое гиперссылка и как ее создать, в этой статье . Но бывает нужно получить саму ссылку из ячейки с гиперссылкой в Excel. Если таких ячеек очень много, даже больше 20, на мой взгляд, не стоит пытаться получить ссылку из ячейки вручную. Проще воспользоваться небольшим макросом . Приведу варианты решения такой задачи, все не так сложно:
Бывает, что вы скопировали адреса сайтов из списка в интернете, в большинстве случаев, при таком копировании наименование адреса неравно самому адресу:
Разберемся как вытащить гиперссылку из такой ячейки
Как вывести адрес ссылки для одной ячейки?
Это несложно. Необходимо правой кнопкой мыши нажать на ячейку и выбрать Изменить гиперссылку.
В открывшемся окне вы можете скопировать ссылку из графы Адрес:
Конечно, для каждой ячейки из множества так не накопируешься, поэтому есть удобный способ — создать отдельную функцию для доставания адреса сайта или места на диске.
Если при нажатии на ячейку правой кнопкой мыши в появившемся окне нет кнопок Изменить гиперссылку, Открыть гиперссылку и т.д. Проверьте ту ли ячейку вы выбираете. Если ту, то в такой ячейке нет гиперссылки или она задана неверно.
Как получить ссылку из ячейки с гиперссылкой в Excel
На днях у меня возникла задача: нужно было вытащить ссылки более чем из 10 тыс ячеек. Не делать же такое вручную. Я сначала поискал решение в интернете и нашел отличное вариант. Публикую для своих читателей решение с прекрасного сайта . Для применения способа запишите текст, приведенный ниже в окно модуля VBA ( Ctrl+F11 ). Если не знаете о чем я веду речь, советую прочитать статью про макросы .
Текст создаваемой функции будет следующий:
Function Получить_Ссылку(ByVal rCell As Range) As String Dim s As String If rCell.Hyperlinks.Count = 0 Then If Mid$(rCell.Formula, 2, 9) = "HYPERLINK" Then Получить_Ссылку= Mid$(rCell.Formula, 13, InStr(13, rCell.Formula, Chr(34)) - 13) Else Получить_Ссылку = "В ячейке нет гиперссылки!" End If Else s = rCell.Hyperlinks(1).SubAddress If s <> "" Then s = "#" & rCell.Hyperlinks(1).SubAddress Получить_Ссылку= rCell.Hyperlinks(rCell.Hyperlinks.Count).Address & s End If End Function
Теперь если начать записывать формулу, у вас будет появляться и эта функция тоже.
Конечно, она будет доступна и в меню функций.
Введите адрес ячейки как аргумент этой функции и протяните формулу на нужное количество ячеек. Все!
Удобно, еще раз спасибо Excel-vba.ru за отличный сайт.
Похожие статьи
- 09.08.2015Добавить список адресов на картуPosted in Прочее
- 09.02.2019Определить тип данных в ячейке. Функция ТИП в ExcelPosted in Формулы, Работа с ячейками
- 19.01.2018Каким был 2017 год для excelworks.ru? План на 2018 годPosted in Прочее
- 08.12.2015Взимание платы с грузовиков свыше 12 тонн. ПлатонPosted in Прочее
- 01.10.2016Выпадающий список по значениюPosted in Формулы, Работа с ячейками
- 16.10.2017Как правильно объединить ячейки в Excel?Posted in Прочее, Формулы, Оформление
- 14.03.2015Как столбцы сделать строками? Транспонирование в ExcelPosted in Excel, Формулы
- 28.01.2018Абзац в Excel. Как начать с новой строки в ячейкеPosted in Работа с текстом, Формулы, Работа с ячейками
Как получить адрес гиперссылки в Excel
Функция может помочь если вам, к примеру, необходимо извлечь адреса ссылок из скопированного в интернете тексте.
Функция =АДРЕССЫЛКИ(ГИПЕРССЫЛКА) имеет один обязательный аргумент
- ГИПЕРССЫЛКА — ссылка на ячейку содержащую гиперссылку.
Пример
Определение адреса ссылки по гиперссылке из ячейки.
Код на VBA
Public Function АДРЕССЫЛКИ(ГИПЕРССЫЛКА As Range) As String АДРЕССЫЛКИ = ГИПЕРССЫЛКА.Hyperlinks.Item(1).Address End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Как вытащить ссылку из гиперссылки в excel
Господа,
в интернете нашел решение для случаев задания гиперссылки через свойство ячейки и для случая использования функции ГИПЕРССЫЛКА но с целиком прописанной ссылкой
Прикрепленные файлы
- post_151560.xls (28.5 КБ)
Пользователь
Сообщений: 8839 Регистрация: 11.01.2013
02.09.2010 18:52:55
Доработал вторую функцию. Внутри первого аргумента ф-ии ГИПЕРССЫЛКА не должно быть запятых!
Function WWWVIEW2(ByVal Cell As Range) As String
If Cell.Hyperlinks.Count = 0 Then
If Mid$(Cell.Formula, 2, 9) = «HYPERLINK» Then
WWWVIEW2 = Evaluate(Mid$(Cell.Formula, 12, InStr(12, Cell.Formula, «,») — 12))
Else
WWWVIEW2 = «В ячейке нет гиперссылки!»
End If
Else
WWWVIEW2 = Cell.Hyperlinks(1).Address
End If
End Function
Пользователь
Сообщений: 63 Регистрация: 01.01.1970
03.09.2010 10:00:35
Подскажи пожалуйста в чем разница между mid и mid$?
Пользователь
Сообщений: 5192 Регистрация: 16.01.2013
03.09.2010 10:04:53
хороший вопрос 🙂
Живи и дай жить..
Пользователь
Сообщений: 5192 Регистрация: 16.01.2013
03.09.2010 10:05:22
ну, кто знает?
Живи и дай жить..
Пользователь
Сообщений: 23775 Регистрация: 22.12.2012
03.09.2010 10:13:06
Цитата, не помню, где взял 🙂 :
3. Держитесь подальше от функций без значка «$» таких как Left, Mid и т.п., т.к. они возвращают значение типа Variant. Обычные функции Left$ и Mid$ возвращают результат немедленно, без его неявного преобразования в Variant.
Пользователь
Сообщений: 8839 Регистрация: 11.01.2013
03.09.2010 10:16:10
Mid$() returns a String Variable.
Mid() returns a Variant.
Mid$() will be more efficient when working with strings in VBA code.
Mid() is the only choice when working with fields, because a field might be
Null and strings cannot be Null.
===================
The difference between the functions with the $ and those without is how
they handle Nulls. The ones with the $ are text only and will give you an
error if you pass them a Null.
В самом деле, попробуйте в окне Immediate
?mid(null,1,2)
?mid$(null,1,2)
Как извлечь линк (ссылку) из гиперссылки Excel
Я с столкнулась с тем, что нужно было извлечь ссылки со списка текста с гиперссылками в Excel. Решение гиперссылки Excel я нашла в этой статье How to extract a URL from a hyperlink on Excel и перевела ее.
Чтобы извлечь ссылки из гиперссылок и поместить их в соседний столбик нужно выполнить несколько простых шагов:
- Открыть документ Excel.
- Выделите столбик с гиперссылками
- Перейти в режим VBA (Alt+F11)
- Вставить новый модуль (Insert > Module)
- Скопировать код (ниже) и вставить в открывшееся окно