Sommer les nombres trouvés dans des textes avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Sommer les nombres des textes

Il est intéressant de savoir que les fonctions Texte.Avant et Texte.Apres peuvent être impliquées dans des raisonnements matriciels. Elles peuvent ainsi agir sur plusieurs cellules à la fois.



Sur l'exemple de la capture, nous travaillons sur une liste de produits achetés où les quantités sont mélangées aux désignations. Pourtant, dans une cellule du dessous, nous parvenons à réaliser l'extraction de tous les chiffres, à les convertir en nombres et à les sommer pour livrer le total des quantités achetées et tout cela, avec une seule formule.

Classeur Excel à télécharger
Nous suggérons d'appuyer les démonstrations sur un classeur Excel hébergeant une liste mélangeant des chiffres et des textes.

• Télécharger le classeur additions-nombres-textes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons la liste des courses en colonne C. Les prix unitaires des articles sont mentionnés en colonne D. Bien que les quantités achetées ne soient pas explicitement séparées des désignations, nous tenterons de calculer les totaux hors taxes pour chaque ligne, en colonne E. Mais surtout, dans une technique matricielle comme nous l'avons annoncé, nous souhaitons calculer la somme des quantités achetées, en cellule C12.

Sommer les quantités extraites
Avec la fonction Texte.Avant, il est très simple d'isoler la donnée placée devant le premier espace d'une chaîne. Il s'agit de la quantité ici. Mais ce qui est moins habituel, c'est qu'il est possible de la faire agir sur une plage de cellules complète pour réunir toutes ces quantités extraites ensemble. Dès lors, nous sommes tentés de faire intervenir la fonction somme sur la matrice résultante pour produire l'addition de ces valeurs. Et c'est ce que nous proposons d'essayer.

• Sélectionner la case de la quantité totale à calculer, en cliquant sur sa cellule C12,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire la fonction d'addition, suivie d'une parenthèse, soit : Somme(,
• Lui imbriquer la fonction pour prélever avant, suivie d'une parenthèse, soit : Texte.Avant(,
• Désigner toute la liste des courses en sélectionnant la plage de cellules C4:C10,
• Taper un point-virgule (;) pour passer dans l'argument du délimiteur,
• Puis, inscrire un espace entre guillemets, soit : " ",

Comme nous l'avons déjà appris, en l'absence de précision supplémentaire, c'est le premier espace qui va être considéré. Il va en résulter les extractions de toutes les quantités à sommer.

• Fermer la parenthèse de la fonction Texte.Avant,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider la formule par la touche Entrée du clavier,

Forcer la conversion des quantités
La valeur retournée est le chiffre zéro. Le résultat n'est donc pas à la hauteur de nos attentes mais tout s'explique. Ces chiffres sont extraits à partir de chaînes de textes. Donc, ils sont par défaut considérés comme des éléments de texte par Excel. L'astuce consiste à appliquer une fonction particulière de conversion sur la matrice des quantités extraites.

• En cellule C12, adapter la précédente syntaxe comme suit :

=SOMME(VALEURNOMBRE(TEXTE.AVANT(C4:C10;" ")))

• Puis, valider l'adaptation par la touche Entrée du clavier,

Cette fois tout rentre dans l'ordre. Toutes les quantités décelées par ce raisonnement matriciel sont parfaitement extraites, converties en nombres et sommées à l'issue, par le jeu donc de l'imbrication de ces fonctions. Le résultat livré est fidèle à celui de la capture fournie en préambule. 28 articles ont bien été achetés. Et si vous modifiez l'une ou l'autre quantité dans l'une ou l'autre ligne, vous avez le plaisir de constater que le résultat du calcul se met automatiquement à jour.

Les totaux hors taxes
Nous proposons de continuer de nous amuser avec ces quantités extraites à partir de chaînes de textes. Pour calculer les totaux hors taxes, la technique est plus évidente. La simple multiplication de la quantité extraite par le prix unitaire, suffit à forcer la conversion. Nul besoin de la fonction ValeurNombre cette fois. Nous suggérons de commencer avec une technique classique, celle d'une formule bâtie sur la première ligne et à répliquer sur celles du dessous.

• Sélectionner la case du premier total à calculer en cliquant sur sa cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction pour prélever avant, suivie d'une parenthèse, soit : Texte.Avant(,
• Désigner la première chaîne en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument du délimiteur,
• Inscrire un espace entre guillemets, soit : " ",
• Fermer la parenthèse de la fonction Texte.Avant,
• Taper le symbole de l'étoile pour annoncer la multiplication à suivre,
• Désigner le premier prix unitaire par ses coordonnées, soit : D4,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous gardons active la cellule du résultat.



Et comme vous pouvez le voir, le calcul est parfaitement juste. 3 mangues à 1,19 € pièce conduisent bien à un total acheté de 3,57 €.

• Cliquer et glisser la poignée de la cellule E4 jusqu'en cellule E10,

Cette fois, ce sont tous les totaux hors taxes que nous obtenons à partir de ces quantités extraites et multipliées par les prix unitaires respectifs. Nous aurions pu être plus directs avec une technique matricielle. Pour cela, après avoir supprimé ces résultats, il aurait fallu :

• Sélectionner la plage de cellules E4:E10,
• Bâtir la formule suivante : =TEXTE.AVANT(C4:C10;" ")*D4:D10,
• Et la valider par CTRL + Entrée pour la propager sur la plage présélectionnée.