Aplicação automática de hiperlinks de uma célula para outra

Question

Eu tenho uma pasta de trabalho que tem uma coluna que contém os títulos de diferentes documentos publicados em um site (B4: B6). Outra coluna contém os URLs para esses documentos (C4: C6). O URL é criado pelo site quando o documento é carregado e sempre acrescenta um número ao URL que não é previsível antecipadamente.

Exemplo de planilha

Eu tenho um usuário que é usado para inserir manualmente um hiperlink na célula que contém o título, para que a célula do título aponte para o URL. Ele quer que as células de título sejam hiperlinkadas, entende a necessidade de ter o hiperlink em sua própria célula para fins de referência, mas não quer redigitar ou copiar e colar a URL. Como faço para configurar as células de URL (C4: C6) para verificar as células de título para hiperlinks incorporados e, se encontrar uma, para replicá-la na célula de URL apropriada?

Alternativamente, como configuro as células de título para verificar as células de URL para o texto e, se encontrar alguma, para aplicar automaticamente esse texto como um hiperlink na célula de título apropriada?

E o pior de tudo, como faço isso sem usar VBA / macros?

Coloque de outra forma:

Estado atual: o usuário aplica manualmente o hiperlink ao título na coluna B. O usuário também insere manualmente o texto da URL na coluna C.

Estado desejado (opção 1): o usuário aplicou manualmente o hiperlink ao título na coluna B. O texto da URL é extraído automaticamente do hiperlink (não do texto do título) e colocado na coluna C.

Estado desejado (opção 2): o usuário insere manualmente o texto do URL na coluna C. O URL é aplicado automaticamente como um hiperlink incorporado na célula que contém o título na coluna B.

As colunas auxiliares são totalmente boas, se necessário.

    

Answer 1

Esta fórmula examinará uma célula e, se houver um hiperlink (usando a fórmula Hyperlink() ), retornará a URL. Se não há link e é apenas texto, ele cria um link para http://www.fakewebsite.com/[text] :

=IFERROR(MID(FORMULATEXT(A2),SEARCH("http",FORMULATEXT(A2)),SEARCH(",",FORMULATEXT(A2))-SEARCH("http",FORMULATEXT(A2))-1),HYPERLINK("http://www.fakewebsite.com/"&SUBSTITUTE(A2," ","-")))

Nota: Se não houver "http", basta substituir "http" por "www" na fórmula acima. Funciona assim:

Editar:poresclarecimento,issoextrairáumlinkdacoluna"Título do documento" e colocará um texto, se não houver um link:

=IFERROR(MID(FORMULATEXT(A2),SEARCH("http",FORMULATEXT(A2)),SEARCH(",",FORMULATEXT(A2))-SEARCH("http",FORMULATEXT(A2))-1),"No Link")

    

Answer 2

Se você quiser usar fórmulas e links não seguem um padrão simples, então você precisa copiar seus títulos para outra coluna (ou planilha) e referenciá-los em sua fórmula hyperlink .

HYPERLINK("link","copied_title")

Veja abaixo:

Paraaoutraopçãodesejada,vocêaindaprecisausarVBA,mastalvezapenasumaopçãodefinidapelousuáriosejasuficiente,conformedescritoemumadasrespostasneste thread: Extraindo URLs de Hyperlinks

Solução VBA

Se você estiver interessado em VBA, algo assim ajudaria:

Sub hyperlink_title()
    Dim i As Integer
    Dim wsh as Worksheet
    Set wsh = ThisWorkbook.ActiveSheet

    i = 4
    With wsh
    While .Cells(i, 2) <> ""

        ActiveSheet.Hyperlinks.Add Anchor:=.Cells(i, 2), Address:=.Cells(i, 3).Value, _
        TextToDisplay:=.Cells(i, "B").Value2

        i = i + 1

    Wend

End Sub