Excel possède des fonctions de texte étonnantes qui peuvent vous aider lorsque vous travaillez avec des données de texte.
Dans certains cas, vous devrez peut-être calculer le nombre total de caractères dans une cellule/plage ou le nombre de fois qu'un caractère spécifique apparaît dans une cellule.
Bien qu'il existe la fonction LEN qui peut compter le nombre de caractères dans une cellule, vous pouvez également faire le reste avec une combinaison de formules (comme nous le verrons plus loin dans les exemples).
Dans ce didacticiel, je couvrirai différents exemples dans lesquels vous pouvez compter des caractères totaux ou spécifiques dans une cellule/plage dans Excel.
Compter tous les caractères d'une cellule
Si vous souhaitez simplement obtenir le nombre total de tous les caractères d'une cellule, vous pouvez utiliser la fonction LEN.
La fonction LEN prend un argument, qui peut être le texte entre guillemets ou la référence de cellule à une cellule contenant le texte.
Par exemple, supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous vouliez compter le nombre total de caractères dans chaque cellule :
Vous trouverez ci-dessous la formule qui le fera :
=LEN(A2)
En soi, la fonction LEN peut ne pas ressembler à grand-chose, mais lorsque vous la combinez avec d'autres formules, elle peut faire des choses merveilleuses (comme obtenir le nombre de mots dans une cellule ou séparer le prénom et le nom).
Remarque : La fonction LEN comptera tous les caractères d'une cellule, qu'il s'agisse d'un caractère spécial, de nombres, de signes de ponctuation et d'espaces (espaces de début, de fin et doubles entre les mots).
Étant donné que la fonction LEN compte chaque caractère d'une cellule, vous pouvez parfois obtenir le mauvais résultat si vous avez des espaces supplémentaires dans la cellule.
Par exemple, dans le cas ci-dessous, la fonction LEN renvoie 25 pour le texte de la cellule A1, alors qu'elle aurait dû être 22. Mais comme elle compte également des caractères d'espacement supplémentaires, vous obtenez le mauvais résultat.
Pour éviter que des espaces supplémentaires ne soient comptés, vous pouvez d'abord utiliser la fonction TRIM pour supprimer les espaces de début, de fin et doubles, puis utiliser la fonction LEN pour obtenir le nombre réel de mots.
La formule ci-dessous fera ceci:
=LEN(COUPER(A2))
Compter tous les caractères dans une plage de cellules
Vous pouvez également utiliser la fonction LEN pour compter le nombre total de caractères dans une plage entière.
Par exemple, supposons que nous ayons le même ensemble de données et cette fois, au lieu d'obtenir le nombre de caractères dans chaque cellule, je veux savoir combien il y en a dans toute la plage.
Vous pouvez le faire en utilisant la formule ci-dessous :
=SOMMEPROD(LEN(A2:A7)))
Laissez-moi vous expliquer comment fonctionne cette formule.
Dans la formule ci-dessus, la partie LEN de la fonction prend toute une plage de cellules et compte les caractères dans chaque cellule.
Le résultat de la fonction LEN serait :
{22;21;23;23;23;31}
Chacun de ces nombres représente le nombre de caractères dans la cellule.
Et lorsque vous utilisez la fonction SOMMEPROD avec, cela ajoutera simplement tous ces nombres.
Maintenant, si vous vous demandez pourquoi ne pouvez-vous pas utiliser SUM au lieu de SUMPRODUCT, la raison en est qu'il s'agit d'un tableau et que SUMPRODUCT peut gérer un tableau, mais pas la fonction SUM.
Cependant, si vous souhaitez toujours utiliser SUM, vous pouvez utiliser la formule ci-dessous (mais n'oubliez pas que vous devez utiliser Control + Shift + Enter pour obtenir le résultat au lieu d'une entrée normale)
=SOMME(LON(A2:A7))
Compter des caractères spécifiques dans une cellule
Comme je l'ai mentionné, la véritable utilité de la fonction LEN est lorsqu'elle est utilisée en combinaison avec d'autres formules.
Et si vous souhaitez compter des caractères spécifiques dans une cellule (il peut s'agir d'une lettre, d'un chiffre, d'un caractère spécial ou d'un espace), vous pouvez le faire avec une combinaison de formules.
Par exemple, supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous vouliez compter le nombre total de mots dans chaque cellule.
Bien qu'il n'y ait pas de formule intégrée pour obtenir le nombre de mots, vous pouvez compter les caractères d'espacement, puis l'utiliser pour connaître le nombre total de mots dans la cellule.
Voici la formule qui vous donnera le nombre total d'espaces dans une cellule :
=LEN(A2)-LEN(REMPLACER(A2," ",""))+1
La formule ci-dessus compte le nombre total de caractères d'espacement, puis ajoute 1 à ce nombre pour obtenir le nombre de mots.
Voici comment fonctionne cette formule :
- La fonction SUBSTITUTE est utilisée pour remplacer tous les caractères d'espace par un blanc. La fonction LEN est ensuite utilisée pour compter le nombre total de caractères lorsqu'il n'y a pas d'espace.
- Le résultat de LEN(SUBSTITUTE(A2,” “,””)) est ensuite soustrait de LEN(A2). Cela nous donne le nombre total de caractères d'espacement présents dans la cellule.
- 1 est ajouté dans la formule et le nombre total de mots serait un de plus que le nombre total de caractères d'espacement (car deux mots sont séparés par un caractère).
Notez que s'il y a des espaces de début, de fin ou doubles, vous obtiendrez le mauvais résultat. Dans un tel cas, il est préférable d'utiliser la fonction TRIM avec la fonction LEN.
Vous pouvez également utiliser la même logique pour rechercher un caractère, un mot ou une phrase spécifique dans une cellule.
Par exemple, supposons que j'ai un ensemble de données comme indiqué ci-dessous où j'ai différents lots, où chaque lot a un alphabet et un numéro pour le représenter (comme A1, J2, etc.)
Voici la formule qui vous donnera le nombre total de fois qu'un lot avec l'alphabet A a été créé chaque mois :
=LEN(B2)-LEN(REMPLACER(B2,"A",""))
La formule ci-dessus utilise à nouveau la même logique - recherchez la longueur du texte dans une cellule avec et sans le caractère que vous souhaitez compter, puis prenez la différence de ces deux.
Dans la formule ci-dessus, j'ai codé en dur le caractère que je veux compter, mais vous pouvez également le mettre dans une cellule, puis utiliser la référence de cellule. Cela le rend plus pratique car la formule serait mise à jour la prochaine fois que vous modifieriez le texte dans la cellule.
Compter des caractères spécifiques à l'aide d'une formule insensible à la casse
Il y a un problème avec la formule utilisée pour compter des caractères spécifiques dans une cellule.
La fonction SUBSTITUTE est sensible à la casse. Cela signifie que vous « A » n'est pas égal à « a ». C'est pourquoi vous obtenez le mauvais résultat dans la cellule C5 (le résultat aurait dû être 3).
Alors, comment pouvez-vous obtenir le nombre de caractères d'un caractère spécifique alors qu'il aurait pu être dans tous les cas (inférieur ou supérieur).
Vous faites cela en rendant votre formule insensible à la casse. Bien que vous puissiez opter pour une formule complexe, j'ai simplement ajouté le nombre de caractères des deux cas (minuscule et majuscule A).
=LEN(B2)-LEN(REMPLACER(B2,"A",""))+LEN(B2)-LEN(REMPLACER(B2,"a",""))
Compter les caractères/chiffres avant et après la décimale
Je ne sais pas pourquoi, mais c'est une requête courante que je reçois de mes lecteurs et que j'ai vue dans de nombreux forums - comme celui-ci
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez compter les caractères avant et après la virgule.
Vous trouverez ci-dessous les formules qui permettront de le faire.
Compter les caractères/chiffres avant la virgule :
=LEN(INT(A2))
Compter les caractères/chiffres après la virgule :
=LEN(A2)-TROUVEZ(".",A2)
Notez que ces formules ne sont destinées qu'aux chiffres significatifs de la cellule. Si vous avez des zéros de début ou de fin ou si vous avez utilisé un formatage de nombre personnalisé pour afficher plus/moins de nombres, les formules ci-dessus vous donneraient toujours des chiffres significatifs avant et après la virgule.
Voici donc quelques-uns des scénarios dans lesquels vous pouvez utiliser des formules pour compter les caractères dans une cellule ou une plage de cellules dans Excel.
J'espère que vous avez trouvé le tutoriel utile!
