Eu tenho uma URL no seguinte formato:
http://www.example.com/page.html?param1=asdf¶m2=asdfg¶m3=asdfgh
Quero extrair o valor de param2 ou 3 do URL e também remover esse parâmetro específico do URL. Alguma idéia de como fazer isso usando o Excel?
Responder1
Não acredito que o Excel tenha funções integradas 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 sua URL esteja na célula A1e 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, pode ser necessário substituir todos os pontos e vírgulas nas fórmulas por vírgulas.
Explicação detalhada
Para obter o valor de um determinado parâmetro, primeiro precisamos localizá-lo na URL:
=SEARCH(B1 & "="; A1)
OSEARCH()A função localiza o primeiro parâmetro (o parâmetro fornecido) dentro do segundo parâmetro (a URL) e retorna o número da posição inicial onde ele 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 param1poderá retornar a localização, por exemplo, param10se ocorrer anteriormente no URL.
Com o parâmetro encontrado, precisamos retornar a parte (ou substring) da URL começando onde o valor do parâmetro começa e terminando logo antes do próximo E comercial. Para fazer isso, usamos oMID()função, que leva três parâmetros: a string da qual retornar a substring, a posição na qual iniciar e o número de caracteres a retornar.
=MID(A1; SEARCH(B1 & "="; A1); LEN(A1))
Também estamos usando oLEN()função, que simplesmente retorna o comprimento da string fornecida (neste caso, o URL). Por enquanto, este é apenas um espaço reservado (o terceiro parâmetro não se tornou opcional até o Excel 2010), mas será útil mais tarde, quando quisermos o valor do último parâmetro.
Primeiro precisamos mover a posição inicial do início do próprio parâmetro até onde seu valor começa. Para isso, adicionamos à localização do parâmetro dentro da string seu comprimento, bem como 1 (para o =sinal).
=MID(A1; SEARCH(B1 & "="; A1) + LEN(B1) + 1; LEN(A1))
Melhor ainda, o valor retornado começa no local correto. Para fazer com que ele pare no local correto, precisamos localizar o próximo sinal de E comercial, que significa onde o próximo parâmetro começa.
=SEARCH("&"; A1; SEARCH(B1 & "="; A1))
Estamos usando a SEARCH()função novamente, desta vez adicionando um terceiro parâmetro especificando a posição para iniciar a pesquisa - não estamos interessados em e comercial precedendo o parâmetro fornecido, apenas aqueles depois dele.
Para obter o comprimento do valor do parâmetro acima, precisamos subtrair a posição onde 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á "e" comercial no final do URL. Para esse caso, podemos usar oIFERROR()função para detectar o erro que o Excel fornece e retornar algum número fixo. Uma boa escolha é LEN(A1)- é garantido que o comprimento da string seja maior que o comprimento de qualquer uma de suas substrings, e se passarmos isso como o terceiro argumento para MID(), ele retornará todos os caracteres da posição fornecida até o final do 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 B1contiver um parâmetro que não existe na URL, o texto acima retornará um #VALUEerro. Para retornar uma string vazia (ou qualquer outro valor apropriado) nesse caso, envolva tudo 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 é bastante semelhante, usando oREPLACE()função para substituir uma determinada substring da URL (que é definida mais ou menos da mesma forma que acima) por uma string vazia.
Você provavelmente pode simplificar um pouco as fórmulas movendo os blocos comumente usados (como SEARCH(B1 & "="; A1)) para suas próprias colunas e referenciando essas células em vez de digitar a fórmula várias vezes. Essas colunas adicionais podem então ser ocultadas da visualização.