Excel: CONCATÉNATION pour les nuls (et les autres)

La concaténation en Excel est l'une des opérations les plus importantes dans la conception des formules plus avancées. Cet article explique comment effectuer la concaténation manuellement avec l'opérateur esperluette (&) et de manière automatique avec les trois fonctions Excel conçues pour la concaténation : CONCATENER, CONCAT et JOINDRE.TEXTE.

L'une des opérations les plus importantes dans les formules Excel est la concaténation. Dans les formules Excel, la concaténation est le processus qui consiste à joindre une valeur à une autre pour former une chaîne de texte. Les valeurs jointes peuvent être du texte codé en dur, des références de cellules ou des résultats d'autres formules.

Il existe deux manières principales de concaténer dans Excel :

• Manuellement avec l'opérateur esperluette (&)
• Automatiquement avec une fonction comme CONCAT ou JOINDRE.TEXTE

Dans l'article ci-dessous, je vais d'abord me concentrer sur la concaténation manuelle avec l'opérateur esperluette (&), car cela devrait être votre solution de choix pour les problèmes de concaténation de base. Ensuite, nous verrons les trois fonctions Excel dédiées à la concaténation : CONCATENER, CONCAT et JOINDRE.TEXTE. Ces fonctions peuvent avoir un sens lorsque vous devez concaténer plusieurs valeurs en même temps. Comme pour tant de choses dans Excel, le plus important est de comprendre d'abord les bases.

Qu'est-ce que la concaténation ?

La concaténation est l'opération consistant à joindre des valeurs pour former du texte. Par exemple, pour joindre "A" et "B" avec concaténation, vous pouvez utiliser une formule comme celle-ci :

="A"&"B" >>> renvoie "AB"
L'esperluette (&) est l'opérateur de concaténation d'Excel. À moins que vous n'utilisiez l'une des fonctions de concaténation d'Excel, vous verrez toujours l'esperluette dans une formule qui effectue la concaténation. Il est important de comprendre que le résultat de la concaténation est toujours du texte, même lorsque la concaténation implique des nombres. Par exemple:

="X"&50 >>> renvoie "X50"
=100&50 >>> renvoie "10050" contrairement à =100+50 qui renvoie: 150
Les deux formules ci-dessus renvoient des valeurs de texte, même si certaines des valeurs jointes sont numériques. En fait, vous pouvez transformer un nombre en texte en concaténant une chaîne vide ("") au nombre comme ceci :

=99&"" ou =""&99 >>> renvoient "99"

  Cela peut être très utile par exemple lors de l'utilisation des fonctions RECHERCHEV ou RECHERCHEX dans une recherche qui implique des nombres et du texte. Notez que dans tous les exemples ci-dessus, les valeurs de texte apparaissent entre guillemets (""), tandis que les valeurs numériques ne sont pas entre guillemets. Par exemple, alors que 50 est une valeur numérique, "50" est une valeur textuelle.

Formule de concaténation de base

Pour expliquer comment fonctionne la concaténation dans une formule dans une feuille de calcul Excel, commençons par un exemple simple qui montre comment concaténer une chaîne de texte à une valeur dans une cellule. Avec la valeur 50 dans la cellule C3, cette formule renverra "La cellule C3 contient 50":

="La cellule C3 contient "&C3

Il y a trois choses à noter dans la formule ci-dessus :

• Le texte est entouré de guillemets doubles ("")
• L'esperluette rejoint le texte et la cellule C3
• La référence de cellule (C3) n'est pas entre guillemets

En bref, les valeurs de texte codées en dur sont entre guillemets doubles, tandis que les références de cellule, les opérateurs mathématiques et les noms de fonction ne sont pas entre guillemets.

Étendons maintenant le message texte ci-dessus pour ajouter un point (.) à la fin. Dans l'écran ci-dessous, la formule dans la cellule E3 est :

="La cellule C3 contient "&C3&"."
Dans la nouvelle formule ci-dessus, vous pouvez remarquer deux choses:

• Nous avons besoin d'une autre esperluette (&) pour ajouter le point
• Le point est du texte et doit être placé entre guillemets (".")

Naturellement, il s'agit d'une formule Excel standard qui se recalculera automatiquement. Si nous changeons la valeur de C3 en 1033, le nouveau résultat sera "La cellule C3 contient 1033". Comme vous pouvez le voir ci-dessus, la formule est restée la même mais le résultat a changé:

Cet exemple montre les bases de la concaténation dans Excel avec l'opérateur esperluette (&). Examinons maintenant un exemple de concaténation avec une logique de formule de base pour personnaliser un message.

Concaténation avec la logique conditionnelle

La concaténation n'a rien de spécial, vous pouvez donc mélanger la logique de formule conditionnelle selon vos besoins. Dans la formule ci-dessous, nous utilisons la même structure de base que dans l'exemple ci-dessus, avec un message différent :

="Votre score est "&C3&"."
Nous commençons le message, concaténons la valeur de C3 et concaténons le point. Étendons maintenant la formule avec une logique conditionnelle.

Supposons que nous voulons ajouter le texte "Vous avez réussi!" à la fin du message lorsque le score est de 75 ou plus. Pour ce faire, nous pouvons ajouter la fonction SI comme ceci :

="Votre score est "&C3&"."&SI(C3>=75;" Vous avez réussi!";"")
Cela semble compliqué, alors décomposons-le en plusieurs parties.

La partie 1 est la même que précédemment :

="Votre score est "&C3&"."

La partie 2 ajoute une logique conditionnelle basée sur la fonction SI :

SI(C3>=75;" Vous avez réussi!";"")

Si le score dans la cellule C3 est supérieur ou égal à 75, la fonction SI renvoie " Vous avez réussi!". Sinon, elle renvoie une chaîne vide (""). La formule finale concatène simplement la partie 1 à la partie 2.

Formule finale: ="Votre score est "&C3&"."&SI(C3>=75;" Vous avez réussi!";"")

  Notez que…

• Nous avons besoin d'une autre esperluette (&) pour joindre la partie 1 et la partie 2, puisque SI est un nom de fonction.
• Nous avons inclus un espace (" ") au début de " Beau travail !" afin que ce texte ne soit pas directement "collé" à la valeur des points.

Concaténation avec formatage des dates

En Excel, les dates sont considérés comme des nombres et il est donc nécessaire de gérer leur affichage dans une concaténation par le formatage des nombres de date :

Dans la feuille de calcul ci-dessous, la formule dans la cellule E3 est :

="Le rapport est dû le "&C3

Notez que la date apparaît sous la forme d'une valeur numérique brute. Le résultat n'est donc pas utilisable tel quel et on devra adapter la formule…

La formule modifiée utilise la fonction Excel TEXTE pour appliquer un format de date lors de la concaténation :

="Le rapport est dû le "&TEXTE(C3;"jj mmmm")

Excel fournit un grand nombre de formats numériques que vous pouvez utiliser avec la fonction TEXTE.

Concaténation avec formatage des nombres

Un aspect délicat de la concaténation dans Excel est le formatage des nombres. Les formats de nombre dans Excel sont un moyen puissant de contrôler l'affichage des valeurs numériques, mais ils ne font pas partie du nombre. Cela signifie que le formatage des nombres sera perdu lorsque vous concaténerez un nombre formaté.

Par exemple, dans la feuille de calcul ci-dessous, la cellule C3 contient un montant formaté. Cependant, lorsque cette cellule fait partie d'une concaténation, le résultat est le suivant:

="Ventes de la région "&C3&" ont atteint "&D3

Comme vous pouvez le voir, le formatage du montant est perdu

Pour remédier à ce problème, vous pouvez ajouter, comme pour les dates, une mise en forme numérique en utilisant la fonction TEXTE dans votre formule. La formule en D6 est :

="Ventes de la région "&C3&" ont atteint "&TEXTE(D3;"#.##0 €")

Dans cette formule, nous plaçons le nombre en D3 dans la fonction TEXTE et fournissons un format de nombre de devise sans décimales ( "#.##0 €" ). Le format du tableau est donc répliqué et le résultat est plus lisible:

Fonctions de concaténation en Excel

Jusqu'à présent, nous nous sommes concentrés sur la concaténation manuelle avec l'opérateur esperluette (&). Dans cette section, nous examinerons les fonctions fournies par Excel pour faciliter la concaténation : CONCATENER, CONCAT et JOINDRE.TEXTE. En général, CONCATENER et CONCAT sont des alternatives à la concaténation manuelle avec l'opérateur esperluette (&), tandis que JOINDRE.TEXTE fournit des options plus avancées pour travailler avec plusieurs valeurs. Personnellement, j'utilise l'opérateur esperluette (&) comme approche par défaut et n'utilise les fonctions ci-dessous qu'en cas de besoin.

Fonction CONCATENER

La fonction CONCATENER est une ancienne fonction maintenant remplacée par la fonction CONCAT. CONCATENER vous permet d'effectuer uniquement une concaténation simple. Les valeurs à concaténer et les éventuels délimiteurs sont fournis sous forme d'arguments séparés, comme illustré dans l'exemple ci-dessous :

Le principal avantage de la fonction CONCATENER est que les valeurs sont fournies sous forme d'arguments séparés, sans avoir besoin d'une esperluette (&).

Les formules dans les cellules H3:H6 sont :

  Notez que les valeurs de texte codées en dur doivent toujours être placées entre guillemets doubles (""), tout comme la concaténation manuelle avec l'opérateur &.

Fonction CONCAT

La fonction CONCAT remplace la fonction CONCATENER dans les nouvelles versions d'Excel. La grande différence entre les deux fonctions est que CONCAT acceptera une plage de valeurs:

Les formules dans H3:H7 sont :

  Notez que la première formule montre que vous pouvez toujours utiliser la même syntaxe que CONCATENER . La deuxième formule fournit une plage directement à CONCAT en tant qu'argument unique. La possibilité de fournir une plage est le principal avantage de CONCAT par rapport à CONCATENER.

Si vous souhaitez utiliser un délimiteur, vous devrez l'insérer manuellement entre chaque cellule. Ou bien utiliser la fonction suivante – JOINDRE.TEXTE.

Fonction JOINDRE.TEXTE

Enfin, il y a la fonction JOINDRE.TEXTE. Comme la fonction CONCAT, JOINDRE.TEXTE est capable d'accepter une plage ou un tableau de valeurs à concaténer. Cependant, JOINDRE.TEXTE fournit deux fonctionnalités supplémentaires qui le rendent particulièrement utile :

• Possibilité d'accepter un délimiteur personnalisé
• Possibilité d'ignorer les valeurs vides

La syntaxe de la fonction JOINDRE.TEXTE est le suivant:

=JOINDRE.TEXTE(délimiteur; Ignorer_vide; Texte1; [Texte2]; …)

• Le premier argument, Délimiteur, est le délimiteur à utiliser lors de la jointure des valeurs.
• Le deuxième argument, Ignorer_vide, est un booléen (VRAI ou FAUX) qui indique si JOINDRE.TEXTE doit ignorer ou traiter les valeurs vides.
• Les arguments restants: Texte1, Texte2, etc. représentent les valeurs à joindre.

La feuille de calcul ci-dessous montre JOINDRE.TEXTE en action :

Les formules dans les cellules H3:H9 sont :

  Comme vous pouvez le constater, la première formule utilise une chaîne vide ("") comme délimiteur, ce qui oblige JOINDRE.TEXTE à joindre directement les valeurs. Les deux formules suivantes fournissent toutes un (ou plusieurs) caractère comme délimiteur.
Les trois dernières formules montrent le comportement de JOINDRE.TEXTE quant à l'argument qui détermine si les valeurs vides doivent ou non être prises en compte.

Pour aller plus loin en Excel…

Maintenant que la concaténation n'a plus de secret pour vous, je vous propose une sélection d'autres articles et tutoriels pour rendre votre travail en Excel plus agréable et plus efficace…

• Liste de toutes les fonctions Excel (plus de 500 fonctions au même endroit)
• Grand tutoriel pour maîtriser les Tableaux Croisés Dynamiques en une seule heure
• Les explications détaillées des principales fonctions Excel: SI, SOMME.SI, NB.SI, RECHERCHEV, RECHERCHEX,…
• Calculer l'âge actuel et l'ancienneté en Excel
• Calculer le POURCENTAGE dans Excel
• Tout sur Excel Online gratuit
• … et n'oubliez pas de jeter un coup d'œil aux Outils Excels gratuits à télécharger