☰

Posso usar uma fórmula do Excel para extrair a localização do link de um hiperlink em uma célula?

tag-icon • tag-icon microsoft-excel hyperlink
Posso usar uma fórmula do Excel para extrair a localização do link de um hiperlink em uma célula?

Tenho uma planilha com um grande número de células contendo hiperlinks com texto de exibição diferente do local do hiperlink

ou seja:

localização da célula: A1

exibir texto = "Informações do site"

localização do hiperlink = "http://www.mylocation.com"

Existe uma fórmula do Excel que me permite acessar a sequência de texto do local do hiperlink?

O ideal seria ficar assim:

FÓRMULA(A1) = "http://www.mylocation.com"

Responder1

Eu só precisava extrair o endereço do valor de uma única célula, então achei esta pequena função útil:

Em vez de uma macro de "força bruta", você também pode criar uma função definida pelo usuário que extrairia e retornaria a URL de qualquer hiperlink para o qual ela fosse apontada:

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

Neste caso você pode colocá-lo onde quiser. Se desejar, por exemplo, que o URL de um hiperlink em A1 seja listado na célula C25, na célula C25 você deverá inserir a seguinte fórmula:

=ObterURL(A1)

http://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

Responder2

Você pode usar uma macro:

  • Abra uma nova pasta de trabalho.
  • Entre no VBA (pressione Alt + F11)
  • Insira um novo módulo (Inserir > Módulo)
  • Copie e cole a função definida pelo usuário do Excel abaixo
  • Saia do VBA (pressione Alt+Q)

  • Use esta sintaxe para esta função personalizada do 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

Responder3

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");
  }

}

Responder4

Eu tive o mesmo problema que @SAL

Descobri que o .Hyperlinkobjeto realmente divide o endereço .Addresse .SubAddressse encontra um delimitador. "#" é um desses delimitadores (não sei se existem outros).

Veja o comentário de @gilly3 acima e esta outra pergunta do StackOverflow: hyperlinks(1).address retornando apenas um endereço parcial

Também a documentação (incompleta) da Microsoft sobre .SubAddress: Propriedade Hyperlink.SubAddress

Uma coisa que não foi mencionada em nenhum lugar é que o delimitador "#" não está incluído em nenhum .Addressdeles .SubAddress. Se você quiser um endereço que realmente funcione como um URL completo, será necessário adicionar o delimitador "#" novamente.

Com isso, modifiquei o código do @Matthew Lock da seguinte forma, incluindo também a possibilidade de vários hiperlinks serem armazenados do @Igor O e minha adição de "#":

 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

Isso deve retornar uma string de texto com todos os hiperlinks completos (adicionei um espaço no final para que, se houver mais de um, eles sejam separados).

informação relacionada