Separar octetos de endereço IP no Excel

Você já tentou dividir os endereços IP em colunas separadas na planilha do Excel? Talvez o recurso Texto para Colunas possa ajudá-lo a resolver essa tarefa rapidamente, mas, neste artigo, vou falar sobre algumas fórmulas para realizar essa tarefa no Excel.

• Dividir octetos de endereços IP em colunas separadas no Excel

Dividir octetos de endereços IP em colunas separadas no Excel

Normalmente, no Excel, as funções ESQUERDA, EXT.TEXTO, COMPRIMENTO e LOCALIZAR podem ajudá-lo a extrair cada octeto do endereço IP em colunas diferentes. Por favor, siga os seguintes passos:

Dividir o primeiro octeto dos endereços IP:

Para extrair o primeiro octeto do endereço IP, por favor, aplique a fórmula abaixo em uma célula em branco:

E então, arraste a alça de preenchimento para baixo nas células onde deseja aplicar esta fórmula, e todos os primeiros octetos dos endereços IP terão sido extraídos, veja a captura de tela:

Explicação da fórmula:

LOCALIZAR(".";A2)-1: Esta função LOCALIZAR retorna a posição do primeiro ponto da célula A2, subtraindo 1 significa excluir o próprio ponto, você obterá o resultado 2.

ESQUERDA(A2;LOCALIZAR(".";A2)-1)=ESQUERDA(A2; 2): A função ESQUERDA é usada para extrair 2 caracteres do lado esquerdo da string na célula A2.

Dividir o segundo octeto dos endereços IP:

Para obter o segundo octeto dos endereços IP, copie ou insira a seguinte fórmula em uma célula em branco:

Então, arraste a alça de preenchimento para baixo nas células para preencher esta fórmula, e você obterá o resultado que precisa, veja a captura de tela:

Explicação da fórmula:

1. LOCALIZAR(".";A2)+1: Esta função LOCALIZAR retorna a posição do primeiro ponto da célula A2, adicionando 1 significa iniciar a extração a partir do próximo caractere, você obterá o número 4. Esta parte da fórmula é reconhecida como o argumento início_num da função EXT.TEXTO.

2. LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)-LOCALIZAR(".";A2)-1: Esta parte é reconhecida como o argumento num_caract da função EXT.TEXTO.

• LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)= LOCALIZAR(".";A2;4): Esta função LOCALIZAR retornará a posição do segundo ponto. E você obterá o número 6.
• LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)-LOCALIZAR(".";A2)-1=6-3-1: Subtraia a posição do primeiro ponto da posição do segundo ponto, e depois subtraia 1 do resultado para remover um ponto à esquerda. O resultado será 2.

3. EXT.TEXTO(A2;LOCALIZAR(".";A2)+1;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)-LOCALIZAR(".";A2)-1)=EXT.TEXTO(A2; 4; 2): Finalmente, esta função EXT.TEXTO é usada para extrair 2 caracteres começando no quarto caractere da célula A2.

Dividir o terceiro octeto dos endereços IP:

Para extrair o terceiro octeto dos endereços IP, as funções EXT.TEXTO e LOCALIZAR também podem ajudá-lo, por favor, insira ou copie a seguinte fórmula em uma célula em branco:

Então, arraste a alça de preenchimento para baixo para obter os resultados que você precisa, e somente o terceiro octeto dos endereços IP terá sido dividido, veja a captura de tela:

Explicação da fórmula:

1. LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1: Esta função LOCALIZAR aninhada é usada para obter a posição do segundo ponto na célula A2, adicionando 1 significa iniciar a extração a partir do próximo caractere, e você obterá o número 7. Esta parte é reconhecida como o argumento início_num da função EXT.TEXTO.

2. LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)-(LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1): Esta parte da fórmula é reconhecida como o argumento num_caract da função EXT.TEXTO.

• LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)=LOCALIZAR(".";A2; 7): Esta função LOCALIZAR aninhada é usada para obter a posição do terceiro ponto na célula A2, ela retorna o número 8.
• LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)-(LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)=8-7: Subtraia a posição do segundo ponto da posição do terceiro ponto, e o resultado será 1.

3. EXT.TEXTO(A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1;LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)-(LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1))=EXT.TEXTO(A2; 7; 1): Esta função EXT.TEXTO obterá 1 caractere começando no sétimo caractere da célula A2.

Dividir o quarto octeto dos endereços IP:

A fórmula abaixo pode ajudar a extrair o último octeto do endereço IP, por favor, copie ou insira a fórmula em uma célula em branco:

E então, arraste a alça de preenchimento para preencher esta fórmula nas outras células que você precisa, você obterá o resultado conforme mostrado na captura de tela abaixo:

Explicação da fórmula:

1. LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)+1: Esta função LOCALIZAR aninhada é usada para obter a posição do terceiro ponto na célula A2, adicionando 1 significa iniciar a extração a partir do próximo caractere, e você obterá o número 9. Esta parte é reconhecida como o argumento início_num da função EXT.TEXTO.

2. COMPRIMENTO(A2)-LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1):

• COMPRIMENTO(A2): A função COMPRIMENTO retorna o número total de caracteres na célula A2. Ela retornará o número 11.
• COMPRIMENTO(A2)-LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)=11-8: O comprimento total da célula A2 subtrai a posição do terceiro ponto para obter o número de caracteres restantes. Isso retornará o número 3.

3. EXT.TEXTO(A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1)+1;COMPRIMENTO(A2)-LOCALIZAR(".";A2;LOCALIZAR(".";A2;LOCALIZAR(".";A2)+1)+1))=EXT.TEXTO(A2; 9; 3): Por fim, esta função EXT.TEXTO obterá 3 caracteres começando no nono caractere da célula A2.

Funções relacionadas:

Mais artigos: