Remover parâmetro de URL no Excel

1

Eu tenho um URL da seguinte forma:

http://www.example.com/page.html?param1=asdf&param2=asdfg&param3=asdfgh

Eu quero extrair o valor de param2 ou 3 do URL, bem como remover esse parâmetro específico do URL. Alguma idéia de como fazer isso usando o Excel?

    
por Utsab Saha 24.09.2012 / 14:21

1 resposta

6

Eu não acredito que o Excel tenha funções internas para lidar com URLs, então você terá que recorrer à combinação criativa de funções regulares de manipulação de strings como LEN() , MID() e SEARCH() .

Supondo que seu URL esteja na célula A1 e o parâmetro cujo valor você deseja extrair esteja na célula B1 , tente as seguintes fórmulas.

Para obter o valor do parâmetro fornecido:

=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")

Para remover o parâmetro e seu valor do URL:

=IFERROR(REPLACE(A1; SEARCH(B1 & "="; A1); IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) + 1; LEN(A1)); ""); A1)

Observação: dependendo das configurações regionais, talvez seja necessário substituir todos os pontos-e-vírgulas nas fórmulas por vírgulas.

Explicação detalhada

Para obter o valor do parâmetro determinado, primeiro precisamos localizá-lo na URL:

=SEARCH(B1 & "="; A1)

A função SEARCH() localiza o primeiro parâmetro (o parâmetro fornecido) dentro do segundo parâmetro (o URL) e retorna o número da posição inicial onde ocorre. Observe que acrescentamos o sinal de igual ao nome do parâmetro, para ter certeza de que estamos procurando a coisa correta. Caso contrário, a pesquisa por param1 pode, em vez disso, retornar a localização de, digamos, param10 , se ocorrer antes no URL.

Com o parâmetro encontrado, precisamos retornar a parte (ou substring) da URL, começando de onde o valor do parâmetro começa e terminando imediatamente antes do próximo E comercial. Para fazer isso, usamos a função MID() , que leva três parâmetros: a string da qual retornar a subcadeia, a posição na qual iniciar e o número de caracteres a serem retornados.

=MID(A1; SEARCH(B1 & "="; A1); LEN(A1))

Também estamos usando a função LEN() , que simplesmente retorna o comprimento da string dada (neste caso a URL). Este é apenas um marcador de posição por enquanto (o terceiro parâmetro não se tornou opcional até o Excel 2010), mas será útil mais tarde quando desejarmos o valor do último parâmetro.

Primeiramente, precisamos mover a posição inicial do começo do parâmetro para onde seu valor começa. Para fazer isso, adicionamos à localização do parâmetro dentro da string seu comprimento, assim como 1 (para o sinal = ).

=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; LEN(A1))

Isso é melhor, o valor retornado começa no local correto. Para que ele pare no local correto, precisamos localizar o próximo sinal de e comercial, que significa onde o próximo parâmetro será iniciado.

=SEARCH("&"; A1; SEARCH(B1 & "="; A1))

Estamos usando a função SEARCH() novamente, desta vez adicionando um terceiro parâmetro especificando a posição para iniciar a pesquisa em - não estamos interessados em "e comercial" precedendo o parâmetro fornecido, apenas os que o seguem.

Para obter o comprimento do valor do parâmetro acima, precisamos subtrair a posição em que o parâmetro começa, o comprimento do parâmetro e novamente 1 para o sinal = .

=SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1

Isso funciona para todos os parâmetros, exceto o último, porque não há nenhum "e" final no final do URL. Para esse caso, podemos usar a função IFERROR() para detectar o erro que o Excel fornece e retorna um número fixo. Uma boa escolha é LEN(A1) - o comprimento da string é garantidamente maior que o tamanho de qualquer substrings, e se passarmos isso como o terceiro argumento para MID() , ele retornará todos os caracteres do dado posição ao final da string, que é exatamente o que precisamos.

=IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))

Combinando isso com o acima, obtemos a seguinte fórmula:

=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1)))

Uma última coisa: se B1 contiver um parâmetro que não existe no URL, o acima retornará um erro #VALUE . Para retornar uma string vazia (ou qualquer outro valor apropriado) em tal caso, envolva a coisa toda em outro IFERROR() :

=IFERROR(MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; IFERROR(SEARCH("&"; A1; SEARCH(B1 & "="; A1)) - SEARCH(B1 & "="; A1) - LEN(B1) - 1; LEN(A1))); "")

A fórmula para remover o parâmetro e seu valor da URL é bem parecida, usando as REPLACE() função para substituir uma determinada substring da URL (que é definida mais ou menos da mesma forma que acima) com uma string vazia.

Você provavelmente pode simplificar um pouco as fórmulas movendo blocos comumente usados (como SEARCH(B1 & "="; A1) ) para suas próprias colunas e fazendo referência a essas células em vez de digitar a fórmula várias vezes. Essas colunas adicionais podem ser ocultadas da exibição.

    
por 24.09.2012 / 14:45