[VIDEO] Assembler des cellules avec la fonction CONCATENER()

Ce nouvel article va nous permettre d'inaugurer une nouvelle série d’articles dans lesquels nous reviendrons sur les principales formules d'Excel, de manière détaillée. À cette occasion, la première formule que nous allons voir ici est la formule CONCATENER() qui permet de joindre des chaînes de caractères les unes à la suite des autres. 

Télécharger le fichier d'exemple

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel vidéo

Partie 1 :

Partie 2 :

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier de travail dans le lien disponible en fin d’article (cliquez ici pour y accéder).

1. La fonction CONCATENER()

La fonction CONCATENER() permet de d’assembler des chaînes de caractères en les mettant bout à bout les unes à la suite des autres (jusque 255 chaînes au maximum, pour un total de 8192 caractères)

La fonction CONCATENER() s'utilise de la manière suivante :

=CONCATENER(texte1;[texte2],…)

Où :

• texte1 : argument obligatoire, il s'agit de l'élément à joindre en premier
• texte2,… : ensemble des autres chaînes à joindre à la suite

Seul le premier paramètre est obligatoire mais évidemment l'intérêt de cette fonction réside dans le fait de pouvoir lui adjoindre plusieurs paramètres les uns à la suite des autres

Comme pour toutes les fonctions sur Excel, chaque paramètre est séparé par un point-virgule, et les plages de données sont définies par deux points.

La fonction CONCATENER() fonctionne sur les versions suivantes d'Excel :

• Excel 2016
• Excel 2013
• Excel 2011 for Mac
• Excel 2010
• Excel 2007

2. Exemples

   2.1. Saisie des chaînes en paramètre

Pour commencer, nous allons voir un exemple simple : nous allons demander à Excel de concaténer deux chaînes de caractères que nous allons saisir directement en paramètres, afin récupérer une seule chaîne de caractères qui sera constituée par l'ensemble de ces deux chaînes.

=CONCATENER("Bienvenue sur ";"excelformation.fr")

   Résultat → « Bienvenue sur excelformation.fr »

Note : lorsque nous saisissons directement une chaîne de caractères dans une formule, celle-ci doit être entourée par des guillemets.

   2.2. Faire référence à des cellules

Évidemment nous pouvons tout aussi bien utiliser des références à des cellules :

=CONCATENER(A29;A30)

   Résultat → « Bienvenue sur excelformation.fr »

Dans ce nouvel exemple, le résultat retourné est exactement le même. En effet, les cellules auxquelles la formule CONCATENER() fait références ont une valeur identique à celle des chaînes de caractères saisie dans le premier exemple.

Attention lorsque qu’Excel concatène les chaînes de caractères les unes à la suite des autres il n'y a pas de caractère de séparation entre chacune d’entre elles. Sur les deux exemples que nous venons de voir, l'espace entre les deux chaînes de caractères est intégré directement dans la première. Mais nous pourrions tout autant saisir trois chaînes de caractères, en insérant au centre une chaîne constituée uniquement d’un espace :

=CONCATENER("Bienvenue sur";" ";"excelformation.fr")

   Résultat → « Bienvenue sur excelformation.fr »

   2.3. Inclure des guillemets dans une chaîne

Pour insérer des guillemets (« " ») dans une chaîne de caractères il est nécessaire de les doubler (les saisir deux fois), afin qu’Excel ne les interprète pas comme une fin de chaîne :

=CONCATENER("Bienvenue sur ""excelformation.fr"", le site de référence sur Excel")

   Résultat → « Bienvenue sur "excelformation.fr", le site de référence sur Excel »

En début de chaîne, il est donc nécessaire d'ouvrir un premier guillemet pour démarrer la chaîne de caractères puis de saisir directement à la suite les doubles guillemets. De la même manière à la fin de d’une chaîne de caractères, il va être nécessaire d'afficher trois fois les guillemets :

=CONCATENER("""Bienvenue sur excelformation.fr, le site de référence sur Excel""")

   Résultat → « "Bienvenue sur excelformation.fr, le site de référence sur Excel" »

Enfin, si la chaîne de caractères est uniquement constituée de guillemets il faudra cette fois insérer quatre fois les guillemets afin :

• d'ouvrir la chaîne de caractères,
• saisir les guillemets,
• puis fermer la chaîne de caractères

=CONCATENER("""";"Bienvenue sur excelformation.fr, le site de référence sur Excel";"""")

   Résultat → « "Bienvenue sur excelformation.fr, le site de référence sur Excel" »

   2.4. CONCATENER() et les tableaux de données

La formule CONCATENER() peut également être utilisée au sein de tableaux de données, il suffit alors de sélectionner chacune des cellules à la suite des autres en les séparant par un point-virgule.

=CONCATENER([@[Colonne 1]];" ";[@[Colonne 2]];" ";[@[Colonne 3]];[@[Colonne 4]])

   2.5. Imbriquer des formules dans CONCATENER()

Il est également possible d'intégrer des calculs directement en tant que paramètre dans la formule CONCATENER() :

=CONCATENER(100;"HT = ";100*1,2;"TTC")

   Résultat → « 100HT = 120TTC »

Excel effectue les calculs en même temps qu'il concatène les cellules.

3. Alternatives

Il existe trois alternatives à la formule CONCATENER() que nous allons voir ci-après.

   3.1. La formule TEXTE()

La première de ces alternatives consiste à utiliser la formule TEXTE() qui permet de mettre en forme un texte, afin d'ajouter des éléments dans le résultat retourné :

=TEXTE(A66;"0,00€")

   Résultat → 10,23€

   3.2. Utiliser le formatage de cellule

Nous pouvons également modifier le formatage de la cellule pour changer son affichage. Pour cela, après avoir saisie une valeur numérique, il suffit d’effectuer un clic droit sur la cellule, puis de sélectionner « format de cellules ».

Dans la section « monétaire », nous pouvons afficher directement le symbole euros.

Il est également possible de personnaliser l'affichage dans section « Personnaliser », par exemple « 0,00"€ HT" », puis valider en cliquant sur « Ok ».

L’avantage de cette alternative par rapport à la première est que le format numérique est préservé. Ainsi il restera possible d’effectuer des calculs faisant référence à cette cellule.

   3.3. L’esperluette

Enfin, la dernière alternative consiste à utiliser le symbole esperluette (que l’on appelle également « et commercial »). Ainsi il n'est plus nécessaire d'utiliser une formule, ce symbole permet de concaténer les cellules ou les valeurs :

=A66&"€"

  Résultat → 10,23€

Cette dernière alternative est plus simple et plus rapide à utiliser que la formule CONCATENER(), elle est donc vouée à remplacer cette dernière.

4. Les fonctions CONCAT() et JOINDRE.TEXTE()

Dans l’exemple que nous venons de le voir ici, nous pouvons relever deux inconvénients :

• Chaque cellule doit être sélectionnée indépendamment l’une de l’autre, impossible de choisir directement une plage de cellule ! Ici, cela ne pose pas de problème particulier, mais si nous souhaitons effectuer cette opération sur un grand nombre de cellules, nous serons alors vite bloqués… En effet, pour rappel la fonction CONCATENER() peut permettre d’assembler jusqu’à 255 textes…
• De plus, comme nous avons pu nous en rendre compte, il n’y a aucun caractère de séparation entre chaque texte concaténé… il faut donc intercaler ces derniers entre chaque argument (un espace " ", une virgule ", ", un tiret "-",…)

C’est pour corriger ces limitations qu’Excel 2019 (et 365) a introduit les nouvelles fonctions CONCAT() et JOINDRE.TEXTE().

La première fonctionne à peu près de la même manière que la fonction CONCATENER() que nous avons vu juste avant, qu’elle est d’ailleurs amenée à remplacer (la fonction CONCATENER() reste uniquement disponible pour maintenir une compatibilité avec les anciennes versions).

L’avantage de la fonction CONCAT() sur CONCATENER() est que nous pouvons maintenant passer une plage de cellules en argument :

=CONCAT(A9:B9)

La seconde fonction (JOINDRE.TEXTE()) offre la même possibilité, et nous pouvons en plus de cela préciser une chaîne de caractère à utiliser en tant que séparateur de chacune des chaînes accolée :

=JOINDRE.TEXTE(" ";VRAI;A9:B9)

Comme vous pouvez le constater, celle-ci fonctionne un peu différemment.

En effet, nous commençons par renseigner la chaîne de caractères à utiliser en tant que séparateur des textes assemblés.

Ensuite, le second argument permet de spécifier si nous souhaitons ignorer les cellules laissées vides (ce qui permet de ne pas retrouver deux caractères de séparations l’un à la suite des autres).

Enfin, c’est seulement à partir du troisième argument que nous pouvons venir renseigner les coordonnées des cellules à concaténer, que ce soit par une succession de cellules, ou de plages de plusieurs cellules.

Seulement, comme nous venons de le voir, il est nécessaire de disposer au minimum de la version 2019 d’Excel pour être en mesure d’utiliser ces fonctions.

5. La fonction CONCAT_TEXTE()

C’est pourquoi je vous propose de créer notre propre fonction de concaténation, laquelle sera compatible avec toutes les versions d’Excel.

Pour cela, nous allons bien entendu devoir passer par le développement d’une petite macro-commande en VBA.

Pour que ce tutoriel soit accessible à tout le monde, nous allons revenir dans le détail sur aspect de cette dernière.

Si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.

Pour commencer, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et nous allons commencer par créer un nouveau module.

Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous allons pouvoir créer la fonction en utilisant le mot clé Function, suivi du nom de la fonction :

Lorsque nous validons en appuyant sur la touche [Entrée], Excel ajoute automatiquement des parenthèses dans lesquelles nous allons pouvoir insérer des arguments.

Nous allons tout d’abord voir que l’utilisateur peut renseigner la plage des cellules que celui-ci souhaite assembler, puis dans un deuxième argument quel est le séparateur à appliquer.

Function CONCAT_TEXTE(plage As Range,  Optional separateur As String = ", ")
 
End Function

L’argument plage est typé en tant que Range afin que l’utilisateur puisse l’utiliser pour renseigner une plage de cellules.

Le second argument lui est une chaîne de caractères, il est donc typé en tant que String.

Par ailleurs, le mot clé Optional utilisé ici permet de rendre l’utilisation de cet argument facultatif, et lorsque celui-ci est omis, nous utiliserons par défaut une virgule, suivie d’un espace.

Celui étant fait, nous allons commercer par introduire deux arguments :

    Dim texte As String
    Dim cellule As Range 

• La première variable, texte va nous permettre de construire la chaîne concaténée, il s’agit donc d’une variable de type String,
• La seconde variable, cellule va quant à elle permettre de passer en revue toutes les cellules de la plage de cellules donnée par l’utilisateur, il s’agit donc d’une variable de type Range

Pour passer en revue toutes les cellules contenues dans une plage, nous allons utiliser une boucle For… Each, que nous avons déjà eu l’occasion de découvrir dans le chapitre dédié de la formation sur l’apprentissage de VBA (cliquez ici pour en savoir plus).

Pour utiliser cette boucle, nous allons devoir utiliser une variable de type Range (cellule) :

    For Each cellule In plage    
    Next

Nous allons maintenant pouvoir construire notre chaîne de caractère en utilisant la variable texte :

    For Each cellule In plage
        texte = texte & cellule &  separateur
    Next

Comme vous pouvez le constater, pour créer notre chaîne concaténée, nous allons simplement utiliser la fonctionnalité de concaténation de VBA, c’est-à-dire l’esperluette (le symbole « & »)!

Seulement ici, nous ajoutons automatiquement le séparateur en fin de chaîne, ce qui signifie que nous le retrouverons une dernière fois en toute fin de chaîne…

Pas de problème, il suffit alors de simplement retirer le nombre de caractères correspondant, en utilisant la fonction Left() :

    texte = Left(texte, Len(texte) -  Len(separateur))

Nous limitons ici le nombre de caractères de la chaîne texte au nombre de caractères de la chaîne au total, à laquelle nous soustrayons le nombre de caractères contenus dans le séparateur.

Il ne reste ensuite plus qu’à retourner la valeur de la variable texte en tant que résultat de la fonction CONCAT_TEXTE() :

    CONCAT_TEXTE = texte

Maintenant, nous pouvons revenir dans la feuille de calcul afin d’utiliser notre fonction fraichement développée, comme nous utiliserions n’importe quelle fonction Excel :

=CONCAT_TEXTE(A9:B9)

Ici, nous laissons à Excel le soin d’utiliser le séparateur par défaut, mais nous pourrions bien entendu en spécifier un autre :

=CONCAT_TEXTE(A9:B9;" / ")