Version du 13 octobre 2019 à 01:43

Présentation

Sous Excel il peut être utile de convertir une IP sous la forme WWW.XXX.YYY.ZZZ en un nombre entier et le présent article explique comment y parvenir avec une seule formule.

Procédure

Ici il s'agit d'isoler chaque membre de l'IP et de le multiplier par une puissance de 256 à savoir WWW * 256³ + XXX * 256² + YYY * 256 + ZZZ
Ceci s'obtient avec la formule suivante qui convertit une IP placée dans la cellule A1.

Exemple : IP 132.213.15.23 → 2228555543 (132 * 16777216 + 213 * 65536 + 15 * 256 + 23)

La formule se décompose en 4 parties qui, dans un premier temps, donnent les 4 membres de l'IP et où chaque point est remplacé par une lettre (respectivement A, B et C) par une imbrication de la commande SUBSTITUE :

SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1) qui transforme l'IP 132.213.15.23 en 132A213B15C23

Les formules pour chacun des 4 membres se décomposent comme suit :

GAUCHE(A1;TROUVE(".";A1)-1)

STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)

STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)

DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C")))

Ensuite il suffit de multiplier chacun des membres par la puissance de 256 correspondant à la position du nombre (1, 2, 3 ou 4) :

(16777216*GAUCHE(A1;TROUVE(".";A1)-1))

(65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1))

(256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1))

(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))))

Puis de réunir les quatre en une seule opération pour obtenir le total :

=  (16777216*GAUCHE(A1;TROUVE(".";A1)-1)) + 
(65536*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("A";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + 
(256*STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))+1;CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("B";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-1)) + 
(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C");NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))-CHERCHE("C";SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"A";1) ;".";"B";1) ;".";"C"))))