Separar octetos de endereço IP no Excel

Já tentou dividir endereços IP em colunas separadas numa folha de cálculo do Excel? A funcionalidade *Texto em Colunas* pode ajudá-lo a resolver esta tarefa rapidamente, mas neste artigo vamos explorar algumas fórmulas para o fazer diretamente 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, NÚM.CARACT e PROCURAR permitem-lhe extrair cada octeto do endereço IP para colunas distintas. Siga estes passos:

Dividir o primeiro octeto dos endereços IP:

Para extrair o primeiro octeto do endereço IP, aplique a fórmula abaixo numa célula vazia:

Em seguida, arraste a alça de preenchimento para as células às quais pretende aplicar esta fórmula e todos os primeiros octetos dos endereços IP terão sido extraídos, conforme ilustrado na imagem:

Explicação da fórmula:

FIND(".",A2)-1: Esta função PROCURAR devolve a posição do primeiro ponto na célula A2; subtrair 1 permite excluir o próprio ponto, obtendo assim o resultado 2.

LEFT(A2,FIND(".",A2)-1)=LEFT(A2, 2): A função ESQUERDA extrai os 2 primeiros caracteres do início da cadeia de texto na célula A2.

Dividir o segundo octeto dos endereços IP:

Para obter o segundo octeto dos endereços IP, copie ou introduza a seguinte fórmula numa célula vazia:

Depois, arraste a alça de preenchimento para as células a fim de preencher esta fórmula e obterá o resultado pretendido, conforme ilustrado na imagem:

Explicação da fórmula:

1. FIND(".",A2)+1: Esta função PROCURAR devolve a posição do primeiro ponto na célula A2; ao somar 1, indica-se que a extração deve começar no carácter seguinte, obtendo assim o número 4. Esta parte da fórmula corresponde ao argumento núm_início da função EXT.TEXTO.

2. FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1: Esta parte corresponde ao argumento núm_caract da função EXT.TEXTO.

• FIND(".",A2,FIND(".",A2)+1)= FIND(".",A2,4): Esta função PROCURAR devolve a posição do segundo ponto — e o resultado será o número 6.
• FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1=6-3-1: Subtrai a posição do primeiro ponto à posição do segundo ponto e, em seguida, subtrai 1 ao resultado — ou seja, elimina um ponto inicial. O resultado é 2.

3. MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)=MID(A2, 4, 2): Por fim, esta função EXT.TEXTO extrai dois caracteres a partir do quarto carácter 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 PROCURAR também podem ser úteis. Introduza ou copie a seguinte fórmula numa célula vazia:

Depois, arraste a alça de preenchimento para obter os resultados pretendidos e apenas o terceiro octeto dos endereços IP terá sido separado, conforme ilustrado na imagem:

Explicação da fórmula:

1. FIND(".",A2,FIND(".",A2)+1)+1: Esta função PROCURAR aninhada obtém a posição do segundo ponto na célula A2; ao adicionar 1, indica-se que a extração deve começar no carácter seguinte, resultando na posição 7. Esta parte corresponde ao argumento núm_início da função EXT.TEXTO.

2. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1): Esta parte da fórmula corresponde ao argumento núm_caract da função EXT.TEXTO.

• FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)=FIND(".",A2, 7): Esta função PROCURAR aninhada obtém a posição do terceiro ponto na célula A2, devolvendo o número 8.
• FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1)=8-7: Subtrai a posição do segundo ponto à do terceiro ponto, obtendo o resultado 1.

3.MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 7, 1):Esta função EXT.TEXTO obtém um carácter a partir do sétimo carácter da célula A2.

Dividir o quarto octeto dos endereços IP:

A fórmula abaixo ajuda a extrair o último octeto do endereço IP. Copie-a ou introduza-a numa célula vazia:

Em seguida, arraste a alça de preenchimento para preencher esta fórmula nas outras células necessárias e obterá o resultado conforme ilustrado na imagem:

Explicação da fórmula:

1. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1: Esta função PROCURAR aninhada identifica a posição do terceiro ponto na célula A2; ao somar 1, indica-se que a extração deve começar no carácter seguinte, obtendo assim o número 9. Esta parte corresponde ao argumento núm_início da função EXT.TEXTO.

2. LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1):

• LEN(A2): A função NÚM.CARACT devolve o número total de caracteres na célula A2. Obterá o número 11.
• LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",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, resultando no valor 3.

3. MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 9, 3): Por fim, esta função EXT.TEXTO obtém os 3 caracteres a partir do nono carácter da célula A2.

Funções relacionadas utilizadas:

Mais artigos: