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
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:
- ESQUERDA:
- A função ESQUERDA extrai o número especificado de caracteres do lado esquerdo de uma string fornecida.
- EXT.TEXTO:
- A função EXT.TEXTO é usada para encontrar e retornar um número específico de caracteres do meio de uma string de texto fornecida.
- LOCALIZAR:
- A função LOCALIZAR é usada para encontrar uma string dentro de outra string e retorna a posição inicial da string dentro da outra.
- COMPRIMENTO:
- A função COMPRIMENTO retorna o número de caracteres em uma string de texto.
Mais artigos:
- Dividir texto e números em uma célula no Excel
- Supondo que os dados de uma célula sejam uma mistura de texto e números, como você pode dividi-los em células de coluna separadas? Este tutorial vai mostrar os passos detalhados para fazer isso usando fórmulas.
- Dividir dimensões em duas partes no Excel
- Este artigo explica como usar uma fórmula para dividir dimensões em uma célula em duas partes sem unidades (comprimento e largura individuais).
- Dividir dimensões em comprimento, altura e largura individuais
- Este artigo explica como dividir dimensões em uma célula em três partes (dimensões individuais que incluem comprimento, altura e largura).
- Dividir cadeias de texto em um caractere específico em uma célula no Excel
- Este tutorial explica como dividir cadeias de texto em um caractere específico em células de coluna separadas com fórmulas no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.