Вопрос или проблема
Ссылки отображаются гиперссылками с синей линией в Excel, я хочу извлечь некоторый текст из ссылки, например, URL http://sea.craigslist.com отображается как “seattle” в Excel. Я хочу извлечь текст “sea” из ссылки и вставить в следующий столбец.
Эта формула извлечет весь текст после http:// и перед первой точкой, где A1 — это ячейка, содержащая гиперссылку. Это не слишком сложный метод, но если все ваши данные в одинаковом, простом формате, это может быть достаточно.
=MID( A1 , SEARCH("http://",A1)+7 , SEARCH(".",A1)-SEARCH("http://",A1)-7 )
Однако, это зависит от того, что URL гиперссылки является видимым текстом, и не работает, когда они показывают свое дружелюбное имя. Другими словами, это не работает, когда гиперссылка отображается как просто “Seattle”, потому что формула работает с этим вместо URL.
Пока я не могу найти способ обойти эту проблему, не прибегая к пользовательской функции VBA – в этом случае все может быть как пользовательская функция. Будет ли это приемлемым решением?
Кроме того, знает ли кто-нибудь встроенную функцию для извлечения цели ссылки?
Ответ или решение
Если вы столкнулись с задачей извлечения текста из гиперссылок в Excel, представленная проблема имеет достаточно специфический контекст, который требует аккуратного подхода. Давайте разберем возможные решения и методы, которые могут быть полезны в данной ситуации.
Проблема
Вы скопировали 200 гиперссылок с веб-сайта в Excel, и теперь хотите извлечь определенный текст из этих ссылок. Например, из ссылки http://sea.craigslist.com, отображающейся в Excel как "Seattle", вам нужно получить текст "sea". Основная трудность заключается в том, что стандартная формула Excel работает только с видимым текстом, а не самими URL-адресами.
Решения
1. Использование VBA
Если стандартные функции Excel не позволяют достичь желаемого результата, самым эффективным способом будет написание пользовательской функции на VBA, которая сможет извлечь URL из гиперссылки. Вот пример того, как это можно реализовать:
- Откройте Excel и нажмите
ALT + F11, чтобы открыть редактор VBA. - Вставьте новый модуль, выбрав
Insert > Module. - Введите следующий код:
Function GetURL(cell As Range) As String
On Error Resume Next
GetURL = cell.Hyperlinks(1).Address
End Function- Закройте редактор и вернитесь в вашу таблицу Excel.
Теперь вы можете использовать эту новую функцию GetURL в ячейке, например, так:
=GetURL(A1)где A1 — это ячейка, содержащая гиперссылку. Эта функция вернет ссылку, связанную с текстом в ячейке.
2. Извлечение текста из URL
Теперь, когда вы получили нужный URL, можно применить текстовые функции Excel для извлечения подстроки. Например, чтобы получить текст "sea" из http://sea.craigslist.com, можно воспользоваться следующей формулой:
=MID(GetURL(A1), SEARCH("://", GetURL(A1)) + 3, SEARCH(".", GetURL(A1)) - (SEARCH("://", GetURL(A1)) + 3))Эта формула извлекает текст между :// и первым . в URL.
Заключение
Как видно, подход с использованием VBA и дополнительными формулами позволяет эффективно решить вашу задачу по извлечению текста из гиперссылок в Excel. Это решение не только оптимизирует процесс, но и предоставляет вам гибкость для работы с большими объемами данных. Если у вас остались вопросы или необходима дальнейшая помощь, не стесняйтесь обращаться за поддержкой.