Séparer les octets d’une adresse IP dans Excel

Avez-vous déjà essayé de séparer des adresses IP en colonnes distinctes dans une feuille Excel ? La fonction « Texte en colonnes » vous permet d’accomplir cette tâche rapidement, mais dans cet article, nous explorerons différentes formules pour réaliser cette opération directement dans Excel.

• Diviser les octets d’adresses IP en colonnes distinctes dans Excel

Diviser les octets d’adresses IP en colonnes distinctes dans Excel

Généralement, dans Excel, les fonctions GAUCHE, STXT, NBCAR et TROUVE vous permettent d’extraire chaque octet d’une adresse IP dans des colonnes distinctes. Voici la marche à suivre :

Extraire le premier octet des adresses IP :

Pour extraire le premier octet d’une adresse IP, appliquez la formule suivante dans une cellule vide :

Ensuite, faites glisser la poignée de recopie vers les cellules auxquelles vous souhaitez appliquer cette formule : tous les premiers octets des adresses IP seront ainsi extraits, comme illustré ci-dessous.

Explication de la formule :

TROUVE(".",A2)-1 : Cette fonction TROUVE renvoie la position du premier point dans la cellule A2. En soustrayant 1, on exclut le point lui-même, ce qui donne le résultat 2.

GAUCHE(A2,TROUVE(".",A2)-1)=GAUCHE(A2, 2): La fonction GAUCHE extrait les 2 premiers caractères à partir de la gauche de la chaîne située dans la cellule A2.

Extraire le deuxième octet des adresses IP :

Pour obtenir le deuxième octet des adresses IP, copiez ou saisissez la formule suivante dans une cellule vide :

Ensuite, faites glisser la poignée de recopie vers les cellules concernées pour appliquer cette formule et obtenir le résultat souhaité, comme illustré ci-dessous :

Explication de la formule :

1. TROUVE(".",A2)+1 : Cette fonction TROUVE renvoie la position du premier point dans la cellule A2. En y ajoutant 1, l’extraction commence dès le caractère suivant — ce qui vous donne ici le nombre 4. Cette partie de la formule constitue l’argument numéro_départ de la fonction STXT.

2. TROUVE(".",A2,TROUVE(".",A2)+1)-TROUVE(".",A2)-1 : Cette partie constitue l’argument nb_caractères de la fonction STXT.

• TROUVE(".",A2,TROUVE(".",A2)+1)= FIND(".",A2,[[PH_26]]) : Cette fonction TROUVE renvoie la position du deuxième point. Vous obtiendrez le nombre 6.
• TROUVE(".",A2,TROUVE(".",A2)+1)-TROUVE(".",A2)-1=6-3-1 : Soustrayez la position du premier point de celle du deuxième point, puis retirez 1 au résultat pour exclure le point initial. Le résultat est 2.

3. STXT(A2,TROUVE(".",A2)+1,TROUVE(".",A2,TROUVE(".",A2)+1)-TROUVE(".",A2)-1)=STXT(A2, 4, 2): Enfin, cette fonction STXT extrait deux caractères à partir du quatrième caractère de la cellule A2.

Extraire le troisième octet des adresses IP :

Pour extraire le troisième octet des adresses IP, les fonctions STXT et TROUVE vous seront également utiles. Saisissez ou copiez la formule suivante dans une cellule vide :

Ensuite, faites glisser la poignée de recopie vers les cellules nécessaires afin d’obtenir les résultats attendus. Seul le troisième octet des adresses IP sera extrait, comme illustré ci-dessous :

Explication de la formule :

1. TROUVE(".",A2,TROUVE(".",A2)+1)+1 : Cette formule imbriquée avec la fonction TROUVE permet de localiser la position du deuxième point dans la cellule A2. En y ajoutant 1, on démarre l’extraction juste après ce caractère, ce qui donne la position 7. Ce résultat constitue l’argument numéro_départ de la fonction STXT.

2. TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)-(TROUVE(".",A2,TROUVE(".",A2)+1)+1) : Cette partie de la formule correspond à l’argument nb_caractères de la fonction STXT.

• TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)=FIND(".",A2, [[PH_51]]) : Cette fonction TROUVE imbriquée permet d’obtenir la position du troisième point dans la cellule A2 ; elle renvoie le nombre 8.
• TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)-(TROUVE(".",A2,TROUVE(".",A2)+1)+1)=8-7 : Soustrayez la position du deuxième point de celle du troisième point. Le résultat est 1.

3. STXT(A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1,TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)-(TROUVE(".",A2,TROUVE(".",A2)+1)+1))=STXT(A2, 7, 1) : Cette fonction STXT extrait un caractère à partir du septième caractère de la cellule A2.

Extraire le quatrième octet des adresses IP :

La formule ci-dessous permet d’extraire le dernier octet d’une adresse IP. Copiez-la ou saisissez-la dans une cellule vide :

Ensuite, faites glisser la poignée de recopie pour appliquer cette formule aux autres cellules concernées. Vous obtiendrez alors le résultat illustré ci-dessous :

Explication de la formule :

1. TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)+1 : Cette fonction TROUVE imbriquée permet d’obtenir la position du troisième point dans la cellule A2. En y ajoutant 1, l’extraction commence dès le caractère suivant, ce qui donne le nombre 9. Cette partie constitue l’argument numéro_départ de la fonction STXT.

2. NBCAR(A2)-TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1):

• NBCAR(A2) : La fonction NBCAR renvoie le nombre total de caractères contenus dans la cellule A2. Vous obtiendrez le chiffre 11.
• NBCAR(A2)-TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)=11-8 : La longueur totale de la cellule A2, à laquelle on soustrait la position du troisième point, donne le nombre de caractères restants. Vous obtenez ainsi le chiffre 3.

3. STXT(A2,TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1)+1,NBCAR(A2)-TROUVE(".",A2,TROUVE(".",A2,TROUVE(".",A2)+1)+1))=STXT(A2, 9, 3): Enfin, cette fonction STXT extrait trois caractères à partir du neuvième caractère de la cellule A2.

Fonctions associées utilisées :

Autres articles :