Séparer les nombres des unités de mesure

Cet article vous explique comment utiliser une formule pour séparer les nombres de leurs unités de mesure.

Comment séparer les nombres des unités de mesure ?

Les trois formules ci-dessous vous permettent de séparer les nombres de leurs unités de mesure, afin qu’ils s’affichent dans deux colonnes distinctes.

Premièrement, localisez le premier caractère de l’unité

1. Sélectionnez une cellule vide pour y afficher le résultat.

2. Saisissez ou copiez la formule ci-dessous dans cette cellule, puis appuyez sur la touche Entrée. Sélectionnez la cellule contenant le résultat, puis faites glisser la poignée de recopie pour appliquer la formule aux autres cellules selon vos besoins.

=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

Deuxièmement, séparez les nombres des unités

1. Sélectionnez une cellule vide pour afficher le résultat.

2. Saisissez la formule ci-dessous dans cette cellule, puis appuyez sur la touche Entrée. Sélectionnez la cellule contenant le résultat et faites glisser la poignée de recopie pour appliquer la formule aux autres cellules selon vos besoins.

=VALUE(LEFT(B5,C5-1))

Enfin, récupérez les unités

1. Sélectionnez une cellule vide pour y afficher le résultat.

2. Saisissez la formule ci-dessous dans cette cellule, puis appuyez sur la touche Entrée. Sélectionnez la cellule contenant le résultat, puis faites glisser la poignée de recopie pour appliquer la formule aux autres cellules selon vos besoins.

=TRIM(RIGHT(B5,LEN(B5)-C5+1))

Comment ces formules fonctionnent-elles ?

=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

• 1. MID(B5,{1,2,3,4,5,6,7,8,9},1): La fonction STXT extrait les premières 9 valeurs en B5 et renvoie le résultat sous forme de tableau : {"1",«m»,«q»,«»,«»,«»,«»,«»,«»} ;
• VALUE({"1",«m»,«q»,«»,«»,«»,«»,«»,«»}) : La fonction CNUM convertit les nombres contenus dans Format de texte en nombres réels et renvoie le résultat suivant : {1,#VALEUR!, #VALEUR!, #VALEUR!, #VALEUR!, #VALEUR!, #VALEUR!, #VALEUR!, #VALEUR!} ;
• 2. ISNUMBER({1,#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}) *{1,2,3,4,5,6,7,8,9}La fonction ESTNUMÉRIQUE vérifie la présence de nombres dans le tableau et renvoie {VRAI; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX}. Ce tableau est ensuite multiplié par un autre tableau contenant 9 nombres, ce qui donne {1; 0; 0; 0; 0; 0; 0; 0; 0}.
• 3. MAX({1,0,0,0,0,0,0,0,0,})+1Ensuite, la fonction MAX renvoie la plus grande valeur « 1 » du tableau, qui correspond à la position du dernier chiffre dans la cellule B5. Il suffit d’ajouter 1 pour obtenir la position du premier caractère de l’unité, soit ici 1 + 1 = 2.

=VALUE(LEFT(B5,C5-1)):

• La fonction GAUCHE extrait 1 caractère (2 – 1 = 1) à partir de la gauche de B5 et renvoie « 1 ». La fonction CNUM convertit ensuite cette valeur, initialement au format texte, en un nombre réel.

=TRIM(RIGHT(B5,LEN(B5)-C5+1))

• 1. LEN(B5)La fonction NBCAR calcule la longueur totale de la cellule B5, ce qui donne un résultat de 3.
• 2. TRIM(RIGHT(B5,2))La fonction DROITE extrait deux caractères à partir de la droite de la cellule B5 et renvoie « mg ». La fonction SUPPRESPACE supprime tous les espaces éventuels entre le nombre et l’unité dans ce résultat.

Remarque : La première formule présentée dans cet article fonctionne correctement uniquement pour les nombres comptant au plus 9 caractères.

Fonctions associées

Formules associées