Como Classificar Endereços IP – Excel e Google Planilhas

Este tutorial demonstra como classificar endereços IP no Excel e no Google Planilhas

Classificar endereços IP

Os endereços IP consistem em quatro números (com um comprimento máximo de três dígitos cada) separados por pontos (pontos finais). O Excel não consegue classificar corretamente os dados nesse formato. Primeiro, você precisa adicionar zeros à esquerda onde for necessário para que todos os quatro números tenham três caracteres. Digamos que você tenha a lista de endereços IP abaixo

Para classificá-los corretamente, siga estas etapas

1. Na célula C2, insira esta fórmula que adicionará zeros à esquerda a cada parte numérica dos endereços IP:

=TEXTO(ESQUERDA(B1;PROCURAR(".";B1;1)-1);"000")&"."&TEXTO(EXT.TEXTO(B1;PROCURAR(".";B1;1)+1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)-PROCURAR(".";B1;1)-1);"000")& "."&TEXTO(EXT.TEXTO(B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1;PROCURAR(".";B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1)-PROCURAR(".";B1;PROCURAR(".";B1;1)+1)-1);"000")&"."&TEXTO(DIREITA(B1;NÚM.CARACT(B1)-PROCURAR(".";B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1));"000")

2. Arraste a fórmula para baixo até a última linha preenchida (6).

3. Agora todos os números de endereços IP têm os zeros à esquerda necessários. Antes de classificar, copie e cole como valores. Selecione o intervalo com as fórmulas (C1:C6), clique com o botão direito do mouse na área selecionada e escolha Copiar (ou use o atalho de teclado CTRL + C ).

4. Clique com o botão direito do mouse na primeira célula da próxima coluna (D1) e selecione o ícone Colar valores (ou use o atalho Colar valores ).

5. Exclua a coluna com fórmulas e classifique os dados formatados. Clique em algum lugar no intervalo de dados formatados (coluna C) e, na faixa de opções, vá para Início > Classificar e Filtrar > Classificar de A a Z.

Como resultado, os endereços IP na coluna B são classificados corretamente e você pode excluir a coluna auxiliar (C)

Como Funciona a Fórmula?

A fórmula complexa da Etapa 1 usa as funções TEXTO, ESQUERDA, NUM.CARACT, DIREITA e PROCURAR para adicionar zeros à esquerda, de modo que cada um dos quatro números tenha um comprimento de três

• A função PROCURAR encontra pontos no endereço IP.
• As funções ESQUERDA, NUM.CARACT e DIREITA extraem cada número.
• Finalmente, a função TEXTO formata cada número para ter um comprimento de três caracteres (“000”). Isso significa que um número tem um ou dois dígitos, dois ou um zeros à esquerda são adicionados, respectivamente. O E comercial (&)  é usado para unir todos os números separados por pontos.

Classificar Endereços IP no Google Planilhas

Usando a mesma fórmula da Etapa 1 acima, você também pode classificar endereços IP no Google Planilhas.

1. Na célula C2, insira a fórmula e arraste-a até a última linha preenchida (6).

=TEXTO(ESQUERDA(B1;PROCURAR(".";B1;1)-1);"000")&"."&TEXTO(EXT.TEXTO(B1;PROCURAR(".";B1;1)+1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)-PROCURAR(".";B1;1)-1);"000")& "."&TEXTO(EXT.TEXTO(B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1;PROCURAR(".";B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1)-PROCURAR(".";B1;PROCURAR(".";B1;1)+1)-1);"000")&"."&TEXTO(DIREITA(B1;NÚM.CARACT(B1)-PROCURAR(".";B1;PROCURAR(".";B1;PROCURAR(".";B1;1)+1)+1));"000")

2. Agora todos os números de endereços IP têm zeros à esquerda e um comprimento de três. Antes de classificar, copie e cole como valores. Selecione o intervalo com fórmulas (C1:C6), clique com o botão direito do mouse na área selecionada e escolha Copiar (ou use CTRL + C ).

3. Clique com o botão direito do mouse na primeira célula da próxima coluna (D1), clique em Colar especial e escolha Somente valores (ou use o atalho CTRL + SHIFT + V ).

4. Exclua a coluna com fórmulas e classifique os dados formatados. Clique em algum lugar no intervalo de dados formatados (coluna C) e, no menu, vá para Dados > Classificar página > Classificar a página pela coluna C (A a Z).

Como resultado, os endereços IP na coluna B são classificados corretamente e você pode excluir a coluna auxiliar (C)