Comment compter le nombre de mots contenus dans une cellule Excel ?
Dans ce tutoriel, je vais vous montrer comment compter rapidement le nombre de mots contenus dans une ou plusieurs cellules Excel. Nous y détaillerons tout d’abord la méthodologie de mise en place de la formule complète, afin d’aboutir en fin de tutoriel sur la création d’une formule personnalisée qui nous permettra de compter très rapidement le nombre de mots.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Rappel d’utilisation de la fonction NBCAR()
Comme nous l’avons vu dans l’introduction de ce tutoriel, je vais vous montrer comment mettre en place une formule qui va nous permettre de compter le nombre de mots contenus dans une cellule, et même dans une plage composée de plusieurs cellules :
Mais avant de découvrir la méthode qui permet de calculer facilement le nombre de mots contenus dans une cellule, je vous propose de voir rapidement comment compter rapidement le nombre de caractères que nous pouvons retrouver dans cette même cellule.
Pour cela, il suffit en effet d’utiliser la fonction NBCAR(), laquelle est très simple à utiliser, étant donné qu’il suffit de passer en unique paramètre la référence à la cellule pour laquelle nous souhaitons connaître le nombre de caractères :
=NBCAR(B8)
2. Compter le nombre de répétitions d’un caractère donné
Maintenant que nous savons comment compter le nombre de caractères contenus dans un texte, nous allons pouvoir adapter cette formule pour compter le nombre de fois qu’un caractère donné est répété dans cette même chaîne.
C’est en effet en comptant simplement le nombre d’espaces contenues dans un texte que nous serons en mesure d’en dénombrer le nombre de mots.
Et pour effectuer ce calcul, il suffit de compter la différence entre :
- Le nombre de caractères qui composent ce texte,
- Et le nombre de caractères contenus dans le même texte, que nous aurons au préalable retraité afin de faire disparaître le caractère à dénombrer.
Et comme nous l’avons déjà vu à de nombreuses reprises dans des tutoriels précédents, pour faire disparaître un caractère donné d’une chaîne de caractère, nous pouvons utiliser la fonction SUBSTITUE().
Cette fonction permet en effet d’effectuer un remplacement de caractères, et il suffit d’utiliser une chaîne vide en tant que paramètre nouveau_texte :
=NBCAR(SUBSTITUE(B8;" ";""))+1
Maintenant, pour compter le nombre d’espaces contenues dans la chaîne, il suffit de calculer la différence entre les deux :
=NBCAR(B8)-NBCAR(SUBSTITUE(B8;" ";""))
3. Compter le nombre de mots
Et voilà, maintenant que nous connaissons le nombre d’espace dans la chaîne, nous pouvons en déterminer le nombre de mots en incrémentant simplement la valeur obtenue d’un mot, étant donné qu’il y a toujours deux espaces autour d’un mot, à l’exception du premier et du dernier mot.
=NBCAR(B8)-NBCAR(SUBSTITUE(B8;" ";""))+1
Bien entendu, les choses seraient trop simples s’il suffisait de compter le nombre d’espaces pour en connaître le nombre de mots.
Il existe en effet d’autres caractères qui peuvent séparer deux mots, tels que le tiret ou l’apostrophe.
Nous allons donc devoir adapter la formule pour en tenir compte, sous peine d’obtenir un résultat erroné :
En effet, la phrase « Bonjours, comment vas-tu » contient quatre mots, et non pas trois comme retournés par la formule.
Pour corriger cette erreur, nous allons devoir encapsuler la référence de la cellule B9 dans une autre fonction SUBSTITUE(), laquelle va ici pour permettre de remplacer les tirets par des espaces :
=NBCAR(B9)-NBCAR(SUBSTITUE(SUBSTITUE(B9;"-";" ");" ";""))+1
La formule est maintenant bien corrigée.
Nous allons maintenant pouvoir prendre en compte l’apostrophe, exactement de la même manière :
=NBCAR(B10)-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(B10;"'";" ");"-";" ");" ";""))+1
Ensuite, il peut arriver qu’il y ait des erreurs dans les espaces (comme des espaces situées en début ou fin de chaîne, ou alors des espaces répétées) :
Pour cela, nous allons pouvoir utiliser la fonction SUPPRESPACE() que nous avons découverte récemment, et qui permet justement de corriger ces anomalies :
Pour finir, nous pouvons également nous retrouver en présence d’un point d’interrogation, lequel sera considéré par un mot, car celui-ci sera précédé d’une espace.
Mais pas de problème, nous savons maintenant comment faire disparaître ces deux caractères : avec la fonction SUBSTITUE() évidemment :
=NBCAR(SUBSTITUE(SUPPRESPACE(B12);" ?";""))-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUPPRESPACE(B12);" ?";"");"'";" ");"-";" ");" ";""))+1
Attention, ici ces deux caractères sont à supprimer dans les deux calculs de nombre de caractères.
Bien entendu, il peut encore exister quelques exceptions que nous n’avons pas abordées dans ce tutoriel, mais je pense que nous avez bien compris le fonctionnement pour pouvoir adapter la formule à vos propres besoins.
4. Compter le nombre de mots contenus dans une chaîne de plusieurs caractères
Maintenant, pour compter le nombre de caractères contenus dans une plage de plusieurs caractères, il suffit d’insérer la formule que nous venons de mettre en place en tant qu’argument de la fonction SOMMEPROD(), laquelle pourra ainsi en effectuer la somme :
=SOMMEPROD(NBCAR(SUBSTITUE(SUPPRESPACE(B8:B12);" ?";""))-NBCAR(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUPPRESPACE(B8:B12);" ?";"");"'";" ");"-";" ");" ";""))+1)
L’ensemble des mots contenus dans notre petit tableau, aux coordonnées B8 à B12 sont donc au nombre de 27.
5. Créer une fonction personnalisée pour compter les mots
Comme vous pouvez facilement le noter ici, l’inconvénient de la formule que nous venons de mettre en place est que celle-ci est assez longue à mettre en place…
Pour éviter une perte de temps trop importante, nous allons la transformer en une fonction personnalisée en quelques clics !
Pour cela, nous commençons par lancer l’enregistreur de macro-commande en cliquant sur le bouton dédié en bas dans la barre d’état Excel
Excel nous lance alors une fenêtre pour donner un nom à notre macro-commande que nous appelons par exemple nombreMots
Ensuite, nous cliquons sur le bouton [OK] pour lancer l’enregistreur de macro qui va se charger de convertir en lignes de code VBA tout ce que nous allons effectuer sur la feuille de calcul.
Cela étant fait, nous allons simplement copier le contenu de la formule, en la sélectionnant, puis en effectuant un clic et en cliquant sur le bouton Copier :
Ensuite, nous allons la coller dans une cellule vide :
Cela étant fait, nous pouvons arrêter l’enregistrement en cliquant à nouveau sur le bouton situé en bas à gauche :
La macro en pratiquement terminée, nous allons juste la modifier très légèrement.
Pour cela, nous lançons VBE qui est l’outil dans lequel sont saisies et enregistrées les macro-commandes.
Pour le lancer rapidement, nous appuyons sur les touches [Alt]+[F11].
Nous retrouvons alors la macro-commande et nous pouvons constater qu’Excel nous la même traduite en anglais, qui est la langue de fonctionnement de VBA.
La première chose à faire va alors être de changer le type de macro pour en faire une fonction. Pour cela, il suffit de modifier le mot-clé Sub par Function :
Function nombreMots()
…
End Function
Excel modifie alors automatiquement la ligne « End Sub » en « End Function ».
Nous allons maintenant ajouter un argument à notre fonction en le saisissant entre les parenthèses :
Function nombreMots(plageCellules As Range)
Nous demanderons donc à l’utilisateur de renseigner la référence de la plage des cellules, cet argument est donc typé en tant que Range.
Puis, nous modifions la référence contenue dans la formule en remplaçant « R[-1]C[-4]:R[3]C[-4] » par l’adresse de l’argument (« plageCellules.Address ») :
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(LEN(SUBSTITUTE(TRIM(" & plageCellules.Address & "),"" ?"",""""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(" & plageCellules.Address & "),"" ?"",""""),"" ’"","" ""),""-"","" ""),"" "",""""))+1)"
Pour finir, il ne reste plus qu’à retourner à la fonction le résultat de cette formule, en utilisant la fonction VBA Evaluate :
nombreMots = Evaluate("=SUMPRODUCT(LEN(SUBSTITUTE(TRIM(" & plageCellules.Address & "),"" ?"",""""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(" & plageCellules.Address & "),"" ?"",""""),"" ’"","" ""),""-"","" ""),"" "",""""))+1)")
Pour tester notre fonction, il suffit de l’appeler dans Excel comme toutes les autres fonctions :