Compter les valeurs séparées par des virgules dans une cellule

Dans ce tutoriel, vous découvrirez comment combiner les fonctions NBCAR, SUPPRESPACE et SUBSTITUE pour compter facilement les valeurs séparées par des virgules dans une cellule Excel.

Comment compter les valeurs séparées par des virgules dans une cellule Excel ?

Comme le montre la capture d’écran ci-dessous, la colonne B contient une liste de chaînes séparées par des virgules, et vous souhaitez compter les valeurs présentes dans chaque cellule. Suivez les étapes suivantes pour y parvenir.

Formule générique

=IF(ISBLANK(B3),«»,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",«»))+1)

Arguments

Comment utiliser cette formule ?

1. Sélectionnez une cellule vide pour afficher le résultat. Dans cet exemple, la cellule D3 est choisie.

2. Saisissez la formule ci-dessous, puis appuyez sur la touche Entrée. Ensuite, faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules.

=IF(ISBLANK(B3),«»,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",«»))+1)

Remarque : dans cette formule, B3 est la cellule contenant la chaîne séparée par des virgules dont vous souhaitez compter les valeurs. Vous pouvez l’adapter selon vos besoins.

Comment cette formule fonctionne-t-elle ?

=IF(ISBLANK(B3),«»,LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",«»))+1)

1. LEN(TRIM(B3)) : La fonction SUPPRESPACE supprime tous les espaces superflus de la chaîne « AA, BB, CC, DD » et ne conserve qu’un seul espace entre les mots. La fonction NBCAR calcule ensuite la longueur totale de « AA, BB, CC, DD » et renvoie le résultat 14.

2. LEN(SUBSTITUTE(TRIM(B3),",",«»))

• SUBSTITUTE(TRIM(B3),",",«»)Comme expliqué ci-dessus, la fonction SUPPRESPACE supprime ici tous les espaces superflus de « AA, BB, CC, DD » et renvoie le résultat « AA, BB, CC, DD ». Ensuite, la fonction SUBSTITUE remplace toutes les virgules dans « AA, BB, CC, DD » par une chaîne vide, ce qui donne « AA BB CC DD ».
• LEN(«AA BB CC DD»)La fonction NBCAR renvoie le nombre de caractères contenus dans la chaîne de texte « AA BB CC DD », soit un total de 11 caractères.

3. 14-11+1 : Ici, on soustrait la longueur totale de « AA BB CC DD » à celle de « AA, BB, CC, DD » pour obtenir le nombre total de virgules dans la chaîne (14 - 11 = 3). Comme il y a toujours un mot après la dernière virgule, vous devez ajouter 1 à ce chiffre pour obtenir le nombre total de mots séparés par des virgules dans la chaîne. Le résultat est donc 4.

4. SI(ESTVIDE(B3);«»;14) : la fonction SI indique ici que si B3 est vide, elle renvoie une chaîne vide ; sinon, elle renvoie le nombre 14. Comme B3 contient une valeur, la fonction SI retourne le nombre 14.

Fonctions associées

Formules associées