fbpx

Cliquez ici pour découvrir les 7 étapes pour créer
des tableaux croisés dynamiques comme un pro sur Excel

Tutoriel réalisé avec Excel 2016

Je vous propose, aujourd’hui, un article invité d’Alexandre d’Excel Formation.

Dans ce tutoriel, nous allons voir comment compter simplement le nombre fois qu’un caractère, ou une chaîne de caractères, se répète dans une cellule. Dans un premier temps, nous utiliserons une série de formules que nous imbriquerons les unes dans les autres. Puis, dans un second temps, nous verrons comment utiliser l’outil de développement d’Excel pour créer une fonction personnalisée pour arriver au même résultat, en une poignée de secondes seulement.

Compter les caractères par une formule Excel

Pour commencer, voyons comment créer une formule qui va nous permettre de compter le nombre de fois qu’un simple caractère se répète dans une chaîne.

Cette formule va consister à comparer le nombre de caractères contenus :

  • Tout d’abord dans la chaîne principale, saisie par exemple dans la cellule $A$1 ;
  • Que l’on compare avec la même chaîne de caractère à laquelle nous allons ôter le caractère que nous souhaitons compter :

Compter le nombre de caractères Excel

 

Pour l’exemple, nous souhaitons savoir combien de fois nous retrouvons la lettre « o » dans la phrase « Découvrez nos formations gratuites sur votreassistante.net et sur excelformation.fr ».

Pour commencer, nous allons saisir cette phrase dans la cellule A7 :

Compter le nombre de caractères d'une cellule Excel

 

Pour calculer le nombre de caractères contenus dans une chaîne, nous allons utiliser la formule NBCAR(). Cette formule n’admet qu’un seul paramètre nommé « texte » et qui correspond au texte (saisi entre guillemets ou qui peut être une référence à une autre cellule contenant du texte) dont nous souhaitons connaître le nombre de caractères : =NBCAR(A7)

NBcar sur Excel

 

Pour obtenir une chaîne de caractères amputée d’un caractère spécifique, nous allons utiliser la formule SUBSTITUE() qui permet de remplacer un ou plusieurs caractères par une chaîne que l’on définit. Cette formule utilise les trois paramètres suivants :

  • texte : il s’agit du texte d’origine sur lequel nous souhaitons effectuer l’opération de substitution ;
  • ancien_texte : il s’agit de la lettre ou du texte que nous souhaitons voir remplacé par un autre ;
  • nouveau_texte : il s’agit du texte que nous souhaitons insérer à la place d’ancien_texte.

Ici, nous allons remplacer tous les « o » par des espaces vides dans le texte saisi dans la cellule A7 : =SUBSTITUE(A7;"o";"")

Substitue sur Excel

 

Nous allons ensuite compter le nombre de caractères de cette nouvelle chaîne en utilisant à nouveau la formule NBCAR(), mais cette fois-ci sur la cellule B9 : =NBCAR(B9)

Compter le nombre de caractères Excel avec NBcar

 

Pour finir, nous allons calculer la différence entre ces deux comptages en réalisant une simple soustraction du résultat contenu dans la cellule B10 avec celui contenu en cellule B8 : =B8-B10

Soustraction sur Excel

 

Pour simplifier la construction du document, nous pouvons également réaliser cette série d’opérations en une seule cellule, en imbriquant les formules les unes dans les autres : =NBCAR(A7)-NBCAR(SUBSTITUE(A7;"o";""))

NBcar et Substitue sur Excel

 

Si cette fois nous souhaitons calculer le nombre de fois qu’une chaîne constituée de plusieurs caractères se répète, il suffit de diviser le résultat obtenu par le nombre de caractères contenus dans cette chaîne. Pour cela, nous allons utiliser à nouveau la formule NBCAR().

Cette fois, nous souhaitons savoir combien de fois le texte « formation » se répète dans le texte suivant « Découvrez nos Formations gratuites sur votreassistante.net et sur excelformation.fr » : =(NBCAR(A7)-NBCAR(SUBSTITUE(A7;"formation";"")))/NBCAR("formation")

NBcar et Substitue pour répétition Excel

 

Attention !! Si nous saisissons simplement la formule ci-dessus avec la phrase d’exemple « Découvrez nos Formations gratuites sur votreassistante.net et sur excelformation.fr » (avec un « F » majuscule sur le mot « Formations« ), nous allons immédiatement nous apercevoir que le résultat retourné (« 1 ») est erroné. En effet, la formule SUBSTITUE() est sensible à la casse, ce qui signifie qu’elle fait la différence entre les minuscules et les majuscules. Ainsi, l’occurrence « Formations« , qui commence par une majuscule, n’est pas remplacée par une espace vide comme dans les exemples précédents. Pour régler ce problème, nous allons devoir modifier le premier paramètre de la formule SUBSTITUE() afin d’utiliser la valeur contenue dans la cellule A7 en minuscule.

Pour ce faire, nous allons utiliser la formule MINUSCULE() : =(NBCAR(A7)-NBCAR(SUBSTITUE(MINUSCULE(A7);"formation";"")))/NBCAR("formation")

NBcar, Substitue et Minuscule pour répétition Excel

 

Le résultat retourné (« 2 ») est maintenant correct !

La formule que nous venons de créer fonctionne à merveille, par contre, compte tenu de sa longueur, elle est difficilement lisible, et peut être compliquée à réutiliser. C’est pour ce genre de traitement un peu complexe qu’Excel permet de créer des fonctions personnalisées, en VBA.

 

Créer une fonction personnalisée

Pour créer une fonction personnalisée, il nous faut nous rendre dans l’outil de développement de Microsoft Excel. Il existe plusieurs méthodes pour cela, la plus simple étant d’effectuer un clic-droit sur l’onglet de la feuille de calcul, puis de choisir Visualiser le code.

Visualiser le code VBA sur Excel

 

VBA s’ouvre alors sur une feuille blanche qui va nous permettre de saisir du code intégré directement dans la feuille de calcul. Pour créer une fonction personnalisée, nous allons insérer un nouveau module (Menu Insertion, puis Module afin que VBA ouvre à nouveau une feuille blanche) :

Insertion d'un module Excel VBA

 

Une fonction personnalisée est une macro-commande dont la particularité va être de renvoyer un résultat. Nous allons saisir la fonction suivante dont nous verrons le détail juste après :

Function compterCaracteres(texte As String, compte As String) As Long
compterCaracteres = (Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte)
End Function

Créer une fonction personnalisée sur Excel en VBA

  • Function : pour créer une nouvelle fonction personnalisée, nous utilisons le mot clé « Function » ;
  • compteCaracteres : pour définir un nom pour cette fonction : « compteCaracteres » ;
  • (texte As String, compte As String) : pour demander à l’utilisateur de saisir deux informations qui devront prendre la forme d’une chaîne de caractères (variable typée en tant que string). Tout d’abord, le texte dans lequel nous souhaitons effectuer le décompte (qui peut être soit un texte saisi entre guillemets, soit une référence à une cellule), et, en second paramètre, une sous-chaîne que nous allons vouloir décompter (celle-ci peut également être soit un texte saisi entre guillemets, soit une référence à une cellule) ;
  • As Long : pour spcifier à Excel que le résultat retourné devra obligatoirement être sous la forme d’un grand nombre ;
  • compteCaracteres = (Len(texte) – Len(Replace(texte, compte, «  »))) / Len(compte) : le cœur de notre fonction personnalisée se trouve dans cette ligne. Nous affectons comme résultat à la fonction le nombre de fois que nous retrouvons la sous-chaîne « compte » dans la chaîne principale « texte ». Nous n’allons pas rentrer dans le détail du calcul « (Len(texte) – Len(Replace(texte, compte, «  »))) / Len(compte)« , il s’agit en effet de l’équivalent en VBA de la formule que nous venons de créer juste au-dessus ;
  • End Function : pour indiquer à Excel que notre fonction personnalisée est maintenant terminée.

 

Pour utiliser cette fonction, nous allons procéder de la même manière que pour l’insertion de n’importe quelle autre formule Excel :

  • Nous commençons par saisir le signe égal (« = ») ;
  • Puis le nom de la fonction : « =compteCaracteres »

Utiliser une fonction personnalisée sur Excel en VBA

  • Puis l’ensemble des arguments nécessaires : =compterCaracteres(A7;"formation")

Arguments d'une fonction personnalisée Excel VBA

 

Vous noterez au passage que notre formule est sensible à la casse, tout comme la formule que nous avions utilisée précédemment. Pour dénombrer indifféremment les caractères en minuscules et ceux en majuscules, il nous faudra donc utiliser la formule MINUSCULE() comme ceci : =compterCaracteres(MINUSCULE(A7);"formation")

Minuscule dans une fonction personnalisée Excel VBA

Nous aurions pu intégrer directement le remplacement de la chaîne « texte » par son équivalent sous forme minuscule, mais le fait de procéder comme nous le faisons permet de laisser à l’utilisateur le choix de différencier ou non les différentes casses.

 

À titre d’exemple, voici la fonction compteCaracteres() modifiée faire directement abstraction des différences entre les majuscules et les minuscules :

Function compterCaracteres(texte As String, compte As String) As Long
texte = LCase(texte) ' Modification de la variable texte en minuscule
compterCaracteres = (Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte)
End Function

Compter le nombre de caractères Excel avec une fonction personnalisée

Nous utilisons ici la fonction LCase() qui permet de passer une chaîne de caractères en minuscule. Il s’agit d’un équivalent en VBA de la formule MINUSCULE().

Le fichier complet est disponible à cette adresse.

Téléchargez « Comment compter les répétitions de caractères dans une cellule et créer une fonction personnalisée sur Excel ? » au format PDF

Cliquez ici pour découvrir les 7 étapes pour créer
des tableaux croisés dynamiques comme un pro sur Excel

Cliquez ici pour découvrir comment créer des listes déroulantes
en cascade dans un formulaire Excel

lectus et, libero mi, accumsan ut ut mattis vel, ante.