Formule générique
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Sommaire
Pour séparer un nombre d'une valeur unitaire, vous pouvez utiliser une formule basée sur plusieurs fonctions: MAX, ISNUMBER, VALUE et MID. Dans l'exemple illustré, la formule en C5 est:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Remarque: il s'agit d'une formule expérimentale qui utilise une constante de tableau codée en dur, définie ici pour référence et commentaire. Testé par hasard uniquement, alors faites attention si vous utilisez ou adaptez.
Explication
Parfois, vous rencontrez des données qui mélangent des unités directement avec des nombres (c.-à-d. 8 km, 12 V, 7,5 heures). Malheureusement, Excel traitera les nombres dans ce format comme du texte et vous ne pourrez pas effectuer d'opérations mathématiques sur ces valeurs.
Pour séparer un nombre d'une valeur unitaire, vous devez déterminer la position du dernier nombre. Si vous ajoutez 1 à cette position, vous avez le début du texte de l'unité. Cette formule utilise ce concept pour déterminer où commence l'unité de mesure.
Dans l'exemple illustré, la formule en C5 est:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Cette formule utilise la fonction MID pour extraire les 9 premières valeurs de B5, un caractère à la fois. Le résultat est un tableau comme celui-ci:
("8","0","v","","","","","","")
Nous utilisons ensuite la fonction VALUE pour convertir des nombres au format texte en nombres réels. Le résultat est:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Nous exécutons ce tableau via ISNUMBER pour obtenir:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Multipliez ensuite ce nombre par un autre tableau avec 9 nombres pour obtenir:
(1,2,0,0,0,0,0,0,0)
Ensuite, nous utilisons MAX pour obtenir la plus grande valeur, qui est la position du "dernier nombre".
Enfin, nous ajoutons 1 à la position pour obtenir la position de "début de l'unité".
Enfin, nous utilisons cette position avec les fonctions standard GAUCHE et DROITE pour séparer les nombres des unités:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Notez que la constante de tableau numérique codée en dur est un hack pour plus de commodité et ne gérera que les valeurs brutes jusqu'à 9 caractères de longueur.
