Crie muitas células com hyperlink com conteúdo de texto legível em vez de URL literal

Question

Em geral, estou tentando criar uma planilha do MS Excel com centenas de linhas de URLs relacionadas. Column A deve conter texto legível, mas o texto da célula é vinculado a um URL HTTP. Column B deve conter uma URL HTTP de texto plano (isso pode ou não ser hyperlinkado para sua URL aparente, não importa). Column C e em ter conteúdo simples de texto sem formatação. A planilha seria semelhante à seguinte:

| Produto A | http: // host: 1234 / svn / product_a | outros dados ... |
| Produto B | http: // host: 1234 / svn / product_b | outros dados ... |
| Produto C | http: // host: 1234 / svn / product_c | outros dados ... |

Observe que o URL com hiperlink e o URL de texto simples estão relacionados, mas diferentes!

Atualmente, tenho um script bash + sed gerando essa lista exaustiva de repositórios usando a substituição de regex simples das URLs, mas não consigo encontrar documentos do MS Excel sobre como representar em texto sem formatação uma célula com texto com hiperlink (ideal para copiar e colar na planilha).

Se eu estivesse gerando algum tipo de marcação como HTML, seria trivial; mas infelizmente há colunas adicionais que precisam estar disponíveis para outros não-desenvolvedores editarem regularmente.

Existe alguma maneira de criar uma célula Column A usando apenas texto simples copiado na célula?

E desculpe pelo título terrível, eu não estou familiarizado com o MS Excel ou seu jargão

    

Answer 1

Se a coluna B já contiver o URL e a coluna A contiver seu URL de texto simples, tente isso na célula C1 . (Isso pressupõe que você pode inserir uma coluna C para esse uso sem atrapalhar sua organização. A outra opção é digitar o nome de texto plano em cada fórmula na coluna A.)

=HYPERLINK(B1,A1)

Os parâmetros para HYPERLINK são (link_location,[friendly_name]) , em que friendly_name é opcional.

UPDATE

NOTA: A abordagem a seguir só funcionará se você quiser que o nome amigável seja o mesmo que o texto após o último / em sua URL.

Supondo que seus dados iniciem na linha 1, coloque isso na célula A1 e arraste-a para baixo.

=HYPERLINK(B1,IFERROR(RIGHT(B1,LEN(B1)-FIND("$",SUBSTITUTE(B1,"/","$",LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))))),B1))

Agora, para a explicação!

LEN(B1)-LEN(SUBSTITUTE(B1,"/","")

Os parâmetros

SUBSTITUTE são (text,old_text,new_text,[instance_num]) , em que instance_num é opcional.

SUBSTITUTE substitui todos os caracteres / na string por uma string vazia, pois não fornecemos um parâmetro instance_num. Com o LEN , obtemos o comprimento da string menos o comprimento da string sem o / , o que nos dá o número de vezes que / ocorre, o que seria 4 para o seu OP.

SUBSTITUTE(B1,"/","$",4)

Neste caso, estamos usando o quarto parâmetro opcional (estou usando o próprio número do resultado anterior aqui do OP apenas para este exemplo). Isso substituirá somente a última ocorrência de / pela tag $ , que deve ser algum caractere que nunca faria parte da sua string. Seu resultado de OP seria " link $ product_a"

LEN(B1)-FIND("$","http://host:1234/svn$product_a") 21

Os parâmetros

FIND são (find_text,within_text,[start_num]) e não precisamos usar o parâmetro opcional start_num, pois se tivermos escolhido a tag correta, haverá apenas um. (Mais uma vez, estou usando a string do resultado anterior aqui do OP apenas para este exemplo).

O comprimento da string (30) menos a localização da tag (21) fornece o tamanho do texto após o último / (9).

RIGHT fornece a string de texto que são os 9 caracteres mais à direita da string.

IFERROR coloca isso em uma verificação de erro. Se não houver erro, você obtém o resultado do cálculo. Se esses erros, como / na cadeia de caracteres para as funções FIND e SUBSTITUTE funcionarem, ele retornará o texto em B1 .

Por fim, a função HYPERLINK criará um link do URL em B1 com o nome amigável que resulta dos cálculos acima.

Esta maneira de encontrar os últimos caracteres após o delimitador não é algo que eu descobri, mas está em todo lugar e eu usei por tanto tempo que não sei para quem creditar!

(Uma vez que pensei nisso, só tive que adicioná-lo :) Espero que ajude.)