☰

Можно ли использовать формулу Excel для извлечения местоположения гиперссылки в ячейке?

tag-icon • tag-icon microsoft-excel hyperlink
Можно ли использовать формулу Excel для извлечения местоположения гиперссылки в ячейке?

У меня есть электронная таблица с большим количеством ячеек, содержащих гиперссылки, в которых отображаемый текст отличается от местоположения гиперссылки.

то есть:

Расположение ячейки: A1

отображаемый текст = "Информация о сайте"

расположение гиперссылки = "http://www.mylocation.com"

Существует ли формула Excel, позволяющая получить доступ к текстовой строке местоположения гиперссылки?

В идеале это будет выглядеть так:

ФОРМУЛА(A1) = "http://www.mylocation.com"

решение1

Мне нужно было извлечь адрес только из значения одной ячейки, поэтому я нашел эту небольшую функцию удобной:

Вместо макроса «грубой силы» вы также можете создать пользовательскую функцию, которая будет извлекать и возвращать URL-адрес для любой гиперссылки, на которую она указывает:

Function GetURL(rng As Range) As String
     On Error Resume Next
     GetURL = rng.Hyperlinks(1).Address 
End Function

В этом случае вы можете разместить его там, где хотите. Если вы хотите, например, чтобы URL-адрес гиперссылки в A1 был указан в ячейке C25, то в ячейке C25 вы должны ввести следующую формулу:

=ПолучитьURL(A1)

http://excel.tips.net/T003281_Извлечение_URL_из_гиперссылок.html

решение2

Вы можете использовать макрос:

  • Откройте новую рабочую книгу.
  • Откройте VBA (нажмите Alt+F11)
  • Вставьте новый модуль (Вставка > Модуль)
  • Скопируйте и вставьте пользовательскую функцию Excel ниже.
  • Выйдите из VBA (нажмите Alt+Q)

  • Используйте следующий синтаксис для этой пользовательской функции Excel: =GetURL(cell,[default_value])

     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

решение3

function EXTRACT_URL(input) {

  var range = SpreadsheetApp.getActiveSheet().getRange(input);
  var re = /^.+?\(\"(.+?)\",.+?$/;
  if (input.indexOf(':') != -1) {
    var formulas = range.getFormulas();
    for (var i in formulas) {
      for (var j in formulas[i]) {
        formulas[i][j] = formulas[i][j].replace(re, "$1");
      }
    }
    return formulas;
  } else {
    return range.getFormula().replace(re, "$1");
  }

}

решение4

У меня была та же проблема, что и у @SAL

Я обнаружил, что .Hyperlinkобъект на самом деле разбивает адрес на .Addressи , .SubAddressесли встречает разделитель. «#» — один из таких разделителей (не знаю, есть ли другие).

См. комментарий @gilly3 выше и этот другой вопрос StackOverflow: hyperlinks(1).address возвращает только частичный адрес

Также (неполная) документация Microsoft по .SubAddress: Свойство Hyperlink.SubAddress

Единственное, о чем нигде не упоминается, так это то, что разделитель «#» не включен ни в один из них .Address, .SubAddressпоэтому, если вам нужен адрес, который будет работать как полный URL, вам придется снова добавить разделитель «#».

После этого я изменил код @Matthew Lock следующим образом, включив также возможность сохранения нескольких гиперссылок от @Igor O и мое добавление «#»:

 Function getURL(rng As Range) As String
     On Error Resume Next
     fullURL = ""
     For Each HL In rng.Hyperlinks
    
         If Len(HL.SubAddress) > 0 Then
             fullURL = fullURL & HL.Address & "#" & HL.SubAddress & " "
         Else
             fullURL = fullURL & HL.Address & " "
         End If

     Next

     getURL = fullURL

 End Function

Это должно вернуть текстовую строку всех гиперссылок в полном объеме (я добавил пробел в конце, чтобы, если их несколько, они были разделены).

Связанный контент