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

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

Добрый день Уважаемые форумчане

Есть таблица в которую вносится описание — столбец В. Описание это обычно текст, но бывает, что пользователи указывают адреса папок/файлов в виде текста (при этом не добавляют гиперссылку). Такие адреса нужно затем преобразовать в нормальную гиперссылку, кликнув по которой можно было переходить к определенному файлу / папке.

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

Sub TransformToHyperLink() Range("Таблица1[Описание]").Select For Each xCell In Selection ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula Next xCell End Sub

Подскажите как можно обойти данное ограничение ?
Прикрепленные файлы

• TransformToHyperlink.xlsm (16.99 КБ)

Изменено: pinguindell — 25.09.2017 10:56:42
Пользователь
Сообщений: 5099 Регистрация: 22.12.2012
25.09.2017 11:26:48
вариант для данного примера

For Each xCell In Range("Таблица1[Описание]") If InStr(1, xCell, ":\") Then ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula Next xCell

Функция ДВССЫЛ позволяет преобразовать текст в ссылку Excel

Функция ДВССЫЛ в Excel предназначена для преобразования текстового представления ссылки на ячейку или диапазон к ссылочному типу данных и возвращает значение, которое хранится в полученной ссылке.

Примеры использования функции ДВССЫЛ в Excel

Поскольку функция ДВССЫЛ принимает ссылки в качестве текстовых строк, входные данные могут быть модифицированы для получения динамически изменяемых значений.

Ссылки на ячейки в Excel могут быть указаны в виде сочетания буквенного наименования столбца и цифрового номера строки (например, D5, то есть, ячейка в столбце D и строке с номером 5), а также в стиле RXCY, где:

• R – сокращенно от «row» (строка) – указатель строки;
• C – сокращенно от «column» (столбец) – указатель столбца;
• X и Y – любые целые положительные числа, указывающие номер строки и столбца соответственно.

Функция ДВССЫЛ может принимать текстовые представления ссылок любого из этих двух вариантов представления.

Как преобразовать число в месяц и транспонировать в Excel

Пример 1. Преобразовать столбец номеров месяцев в строку, в которой содержатся текстовые представления этих месяцев (то есть, транспонировать имеющийся список).

Вид таблицы данных:

Для получения строки текстовых представлений месяцев введем в ячейку B2 следующую формулу:

Функция ДВССЫЛ принимает аргумент, состоящий из текстового представления обозначения столбца (“A”) и номера столбца, соответствующего номеру строки, и формирует ссылку на ячейку с помощью операции конкатенации подстрок (символ “&”). Полученное значение выступает вторым аргументом функции ДАТА, которое возвращает дату с соответствующим номером месяца. Функция ТЕКСТ выполняет преобразование даты к требуемому значению месяца в виде текста.

Протянем данную формулу вдоль 1-й строки вправо, чтобы заполнить остальные ячейки:

Примечание: данный пример лишь демонстрирует возможности функции ДВССЫЛ. Для транспонирования данных лучше использовать функцию ТРАНСП.

Как преобразовать текст в ссылку Excel?

Пример 2. В таблице содержатся данные о покупках товаров, при этом каждая запись имеет свой номер (id). Рассчитать суммарную стоимость любого количества покупок (создать соответствующую форму для расчета).

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

В ячейке E2 запишем следующую формулу:

Функция ДВССЫЛ принимает в качестве аргумента текстовую строку, которая состоит из буквенного обозначения диапазона ячеек столбца столбца (“C:C”) и номера строки, определенного значениями, хранящимися в в ячейках F4 и G4 соответственно. В результате вычислений запись принимает, например, следующий вид: C2:C5. Функция СУММ вычисляет сумму значений, хранящихся в ячейках указанного диапазона.

Как вставить текст в ссылку на ячейку Excel?

Пример 3. В таблице хранятся данные об абонентах. Создать компактную таблицу на основе имеющейся, в которой можно получить всю информацию об абоненте на основе выбранного номера записи (id).

Вид исходной таблицы:

Создадим форму для новой таблицы:

Для заполнения ячеек новой таблицы данными, соответствующими выбранному из списка абоненту, используем следующую формулу массива (CTRL+SHIFT+ENTER):

Примечание: перед выполнение формулы необходимо выделить диапазон ячеек B16:E16.

В результате получим компактную таблицу с возможностью отображения записей по указанному номеру (id):

Особенности использования функции ДВССЫЛ в Excel

Функция имеет следующую синтаксическую запись:

=ДВССЫЛ( ссылка_на_текст; [a1])

• ссылка_на_текст – обязательный аргумент, принимающий текстовую строку, содержащую текст ссылки, который будет преобразован к данным ссылочного типа. Например, результат выполнения функции =ДВССЫЛ(“A10”) эквивалентен результату выполнения записи =A10, и вернет значение, хранящееся в ячейке A10. Также этот аргумент может принимать ссылку на ячейку, в которой содержится текстовое представление ссылки. Например, в ячейке E5 содержится значение 100, а в ячейке A5 хранится текстовая строка “E5”. В результате выполнения функции =ДВССЫЛ(A5) будет возвращено значение 100.
• [a1] – необязательный для заполнения аргумент, принимающий значения логического типа:

• ИСТИНА – функция ДВССЫЛ интерпретирует текстовую строку, переданную в качестве первого аргумента, как ссылку типа A1. Данное значение используется по умолчанию (если аргумент явно не указан).
• ЛОЖЬ – первый аргумент функции должен быть указан в виде текстового представления ссылки типа R1C1.

1. Если в качестве первого аргумента функции был передан текст, не содержащий ссылку или ссылка на пустую ячейку, функция ДВССЫЛ вернет код ошибки #ССЫЛКА!.
2. Результат выполнения функции ДВССЫЛ будет пересчитан при любом изменении данных на листе и во время открытия книги.
3. Если переданная в качестве первого аргумента ссылка в виде текста указывает на вертикальный диапазон ячеек с более чем 1048576 строк или горизонтальный диапазон с более чем 16384 столбцов, результатом выполнения функции будет код ошибки #ССЫЛКА!.
4. Использование текстовых представлений внешних ссылок (ссылки на другие книги) приведет к возникновению ошибки #ССЫЛКА!, если требуемая книга не открыта в приложении Excel.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

Всем доброго времени суток

Можно ли общей формулой преобразовать вот так:

33 — 33-69 (LP) -> https://www.resonar.ru/product/33-33-69-lp
36 — The Lower Lights (reissue) (limited numbered opaque red vinyl 2xLP + MP3 download code) -> https://www.resonar.ru/product. ad-code
36 — Stasis Sounds For Long Distance Space Travel (reissue) (limited coloured vinyl LP (comes in different coloured vinyl, we cannot guarantee which one you will receive)) -> https://www.resonar.ru/product. receive
36 — Anon 1 (LP + download code) -> https://www.resonar.ru/product/36-anon-1-lp-download-code

Всем доброго времени суток

Можно ли общей формулой преобразовать вот так:

33 — 33-69 (LP) -> https://www.resonar.ru/product/33-33-69-lp
36 — The Lower Lights (reissue) (limited numbered opaque red vinyl 2xLP + MP3 download code) -> https://www.resonar.ru/product. ad-code
36 — Stasis Sounds For Long Distance Space Travel (reissue) (limited coloured vinyl LP (comes in different coloured vinyl, we cannot guarantee which one you will receive)) -> https://www.resonar.ru/product. receive
36 — Anon 1 (LP + download code) -> https://www.resonar.ru/product/36-anon-1-lp-download-code Oh_Nick

Сообщение Всем доброго времени суток

Можно ли общей формулой преобразовать вот так:

покупка

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

Convert plain text to clickable hyperlinks:

With Kutools for Excel’s Convert Hyperlinks utility can help you quickly extract the actual url addresses from hyperlinks and convert multiple plain text to hyperlinks at once.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Преобразование текста URL в интерактивную гиперссылку с кодом VBA

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

1. Выберите диапазон текста URL-адреса, который вы хотите преобразовать в интерактивные гиперссылки.

2. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Sub ConvertToHyperlinks() 'Updateby Extendoffice Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value Next End Sub 

4, Затем нажмите F5 нажмите клавишу для запуска кода, появится всплывающее диалоговое окно для выбора диапазона, затем щелкните OK, а выбранные несвязанные URL-адреса были преобразованы в интерактивные гиперссылки. Смотрите скриншоты:

Преобразуйте текст URL-адреса в интерактивную гиперссылку с помощью Kutools for Excel

Возможно, код VBA является для вас сложным, здесь я могу представить вам многофункциональный инструмент —Kutools for Excel, С его Конвертировать гиперссылки функция, вы можете быстро справиться со следующими операциями:

Если вы установили Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон текста URL-адреса, который вы хотите преобразовать в интерактивные гиперссылки.

2. Нажмите Кутулс > Ссылка > Конвертировать гиперссылки, см. снимок экрана:

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

4, Затем нажмите OK, все выбранные текстовые ссылки были преобразованы в интерактивные гиперссылки. Смотрите скриншот:

Ноты:

1. Если вы хотите поместить фактические адреса в исходный диапазон, установите флажок Преобразовать исходный диапазон.

2. Если гиперссылка связана с текущим документом, отметьте Гиперссылки — это место в этом документе опцию.

Нажмите Конвертировать гиперссылки чтобы узнать больше об этой функции.

Демонстрация: преобразование текста URL-адреса в интерактивную гиперссылку с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Связанная статья: