Google Sheets considère les nombres mélangés avec du texte comme du texte uniquement. Ainsi, en règle générale, vous ne pouvez pas additionner des cellules contenant des nombres et du texte dans une colonne sur Google Sheets.
Si votre opérateur de saisie de données est nouveau dans le métier, vous risquez de rencontrer des situations qui mettent à l’épreuve vos compétences en manipulation de données. Un opérateur de saisie de données inexpérimenté peut causer les erreurs courantes suivantes dans votre feuille de calcul.
Un problème courant que vous pouvez rencontrer dans votre feuille de calcul est une colonne/ligne ou une série de données contenant des nombres mélangés avec du texte (caractères alphanumériques). Généralement, vous pouvez rencontrer de telles entrées dans les colonnes avec des symboles monétaires ainsi que des unités de mesure.
Comment additionner une colonne contenant des nombres et des nombres mélangés avec du texte sur Google Sheets ? Plongeons dans les détails.
Erreur lors de l’addition de cellules contenant des nombres et du texte dans une colonne
Puis-je utiliser la fonction SOMME pour additionner les valeurs de la colonne C dans l’exemple de jeu de données suivant ?
Non, vous ne pouvez pas utiliser la fonction SOMME pour additionner une colonne avec du contenu mixte comme indiqué ci-dessous :
=SOMME(C2:C6)
La formule ci-dessus renverra 0 car il n’y a aucune valeur purement numérique dans la colonne C. Dans la colonne C, tous les nombres sont mélangés avec des unités de mesure, représentées sous forme de chaînes de caractères/texte.
Dans Google Sheets, pour traiter de telles colonnes contenant du contenu mixte dans les calculs, vous devez d’abord supprimer les parties textuelles des nombres.
Apprenez comment supprimer le texte des nombres dans une colonne et ensuite additionner dans Google Sheets. Ce n’est pas un processus compliqué.
Comment additionner des cellules contenant des nombres et du texte dans une colonne sur Google Sheets
Voici la formule pour additionner une colonne avec du contenu mixte dans les cellules, en utilisant les fonctions Google Sheets telles que SOMME, SIERREUR, FormuleMatricielle, SPLIT et REGEXREMPLACE.
Formule #1 (pour une colonne) :
=FormuleMatricielle(SOMME(SIERREUR(SPLIT(REGEXREMPLACE(C2:C&"", "[^0-9.]+", "|"), "|"))))
Explication de la formule
Nous traitons avec une matrice/plage, nous devons donc utiliser la formule matricielle avec d’autres fonctions non matricielles dans cette formule. Maintenant, commençons par expliquer la formule étape par étape.
La fonction REGEXREMPLACE remplace tous les textes de la colonne C par le symbole « | ».
=FormuleMatricielle(REGEXREMPLACE(C2:C&"", "[^0-9.]+", "|"))
Savez-vous pourquoi j’ai utilisé C2:C& » » au lieu de C2:C ?
Ceci est fait pour convertir les nombres dans la plage en chaînes de caractères textuelles. Sinon, si des nombres sont présents dans la colonne C, la formule renverrait une erreur VALEUR ! car la fonction REGEXREMPLACE attend des valeurs textuelles dans le premier paramètre.
La fonction SPLIT divise le résultat ci-dessus en fonction du « | » comme délimiteur.
=FormuleMatricielle(SPLIT(REGEXREMPLACE(C2:C&"", "[^0-9.]+", "|"), "|"))
La fonction SPLIT n’est pas nécessaire si vous utilisez « » au lieu du symbole « | » dans REGEXREMPLACE.
J’ai choisi d’utiliser le symbole « | » car parfois une des cellules peut contenir plusieurs nombres comme suit : 350 MT, 450 MT
Dans ce cas, si vous utilisez « » au lieu de |, REGEXREMPLACE renverra ceci comme un seul nombre entier comme 350450. Je veux un séparateur entre comme 350 | 450 afin de pouvoir le diviser en deux nombres distincts.
La fonction SIERREUR est utilisée pour supprimer toutes les autres erreurs liées aux cellules vides dans la colonne.
=FormuleMatricielle(SIERREUR(SPLIT(REGEXREMPLACE(C2:C&"", "[^0-9.]+", "|"), "|")))
Enfin, la fonction SOMME additionne toutes les valeurs.
Selon notre exemple, si vous supprimez la fonction SOMME du début de la formule, vous pouvez l’utiliser pour extraire uniquement les nombres de la colonne. Ainsi, elle peut être utile pour nettoyer vos données Google Sheets.
Vous pouvez utiliser ma formule dans n’importe quelle colonne car c’est une formule matricielle. Cela signifie que cette formule peut additionner une colonne entière contenant des caractères alphanumériques. Vous devez simplement modifier les références de cellules dans cette formule pour qu’elle fonctionne.
Somme de caractères alphanumériques dans une ligne sur Google Sheets
Puis-je utiliser la formule ci-dessus dans une ligne ?
Pourquoi pas ? Avec ma formule, vous pouvez non seulement additionner des cellules contenant des nombres et du texte dans une colonne, mais aussi dans une ligne. Cependant, vous devez modifier un peu la formule.
Si les nombres avec du texte (caractères alphanumériques) sont dans C2:G2, au lieu d’utiliser C2:G2& » », utilisez TRANSPOSER(C2:G2)& » ».
Formule #2 (pour une ligne) :
=FormuleMatricielle(SOMME(SIERREUR(SPLIT(REGEXREMPLACE(TRANSPOSER(C2:G2)&""; "[^0-9.]+"; "|"); "|"))))
Ici, utilisez la fonction TRANSPOSER pour transposer la ligne en colonne.
Somme de caractères alphanumériques dans un tableau 2D
Pour additionner des caractères alphanumériques (cellules contenant des nombres et du texte) dans un tableau 2D, vous devez adapter la formule #1.
Dans l’exemple suivant, je dois additionner des cellules contenant des nombres et du texte dans B2:F3, un tableau 2D. Pour cela, nous devons convertir les valeurs en une seule colonne et utiliser notre formule précédente.
Cela signifie que vous devez remplacer C2:C dans notre formule #1 par TOCOL(B2:F3) ; la fonction TOCOL transforme la plage de cellules en une seule colonne.
Formule #3 (pour un tableau 2D) :
=FormuleMatricielle(SOMME(SIERREUR(SPLIT(REGEXREMPLACE(TOCOL(B2:F3)&""; "[^0-9.]+"; "|"); "|"))))
Note : Vous pouvez utiliser CONCATENER au lieu de TOCOL.
J’espère que vous avez appris comment additionner une colonne où les nombres sont mélangés avec du texte sur Google Sheets.
Ressources
Nous avons utilisé REGEXREMPLACE pour supprimer le texte et faciliter l’addition des cellules contenant des nombres et du texte sur Google Sheets. Voici quelques sujets connexes qui discutent de scénarios impliquant du texte, des nombres et des sommes.
- Additionner des valeurs textuelles en fonction de scores attribués sur Google Sheets
- Utiliser SOMME.SI dans une colonne de texte et de nombre sur Google Sheets
- Extraire tous les nombres du texte et les additionner sur Google Sheets
- Ajouter du texte personnalisé aux nombres sur Google Sheets (avec support de calcul)
