Tutoriel Excel: Comment utiliser CONCATENER

Dans vos classeurs Excel, les données ne sont pas toujours structurées en fonction de vos besoins. Parfois, vous souhaiterez peut-être diviser le contenu d'une cellule en cellules individuelles, ou faire le contraire - combiner les données de deux colonnes ou plus dans une seule colonne. Les exemples courants qui nécessitent une concaténation dans Excel sont la jonction de noms et de parties d'adresses, la combinaison de texte avec une valeur basée sur une formule, l'affichage des dates et des heures au format souhaité ...

Dans ce tutoriel, vous apprendrez différentes manières de concaténer des chaînes de texte, des cellules, des plages, des colonnes et des lignes dans Excel à l'aide de la fonction « CONCATENER » et de l'opérateur « & ».

Table des matières

La fonction Excel CONCATENER

Concaténation des valeurs de plusieurs cellules

Opérateur "&" pour concaténer des chaînes dans Excel

Concaténation d'une chaîne de texte et d'une valeur de cellule

Concaténation d'une chaîne de texte et d'une valeur calculée par une formule

Concaténer des chaînes de texte avec des sauts de ligne

Comment concaténer une plage de cellules dans Excel ?

Méthode 1. La touche CTRL pour sélectionner plusieurs cellules à concaténer

Méthode 2. La fonction TRANSPOSE pour obtenir la plage

Opérateur "&" et la fonction CONCATENER, quel est le meilleur à utiliser ?

Notes importantes sur la fonction CONCATENER

La fonction Excel CONCATENER

La fonction CONCATENER est utilisée pour rassembler le contenu de plusieurs cellules. Par exemple, si vous avez une feuille de calcul avec les prénoms dans une colonne et les noms dans une autre colonne, vous pouvez utiliser la fonction CONCATENER pour joindre les prénoms et noms dans une seule colonne.

La syntaxe de la fonction CONCATENER dans Microsoft Excel est comme suit :

CONCATENER(text1; [text2; ... text_n])

Où :

• text1 : le premier élément à concaténer. L'élément peut être une valeur de texte, un nombre ou une référence de cellule.
• [text2] ; .. text_n]: éléments de texte supplémentaires à joindre. Il est possible de gérer jusqu'à 255 éléments, pour un total de 8 192 caractères.

Remarque : Dans Excel 2016, Excel 2019, Excel Online et Excel Mobile, « CONCATENER » est remplacé par la fonction « CONCAT », qui a exactement la même syntaxe. Bien que la fonction CONCATENER soit conservée pour des raisons de compatibilité descendante, il est recommandé d'utiliser CONCAT à la place car Microsoft ne promet pas que CONCATENER sera disponible dans les futures versions d'Excel.

Vous trouverez ci-dessous quelques exemples d'utilisation de la fonction CONCATENER dans Excel.

Concaténation des valeurs de plusieurs cellules

Nous souhaitons que les informations de la colonne « Nom » et « Prénom » apparaissent ensemble dans la même cellule. Cependant, il faudrait beaucoup de temps pour tout taper à la main. Plutôt que de combiner ces données manuellement, nous pouvons utiliser la fonction CONCATENER pour le faire automatiquement et rapidement.

Dans notre exemple, nous pouvons utiliser la fonction de concaténation pour combiner le texte de la colonne A et celui de la colonne B afin de créer un nom combiné dans une nouvelle colonne :

Avant de commencer à écrire la fonction, nous devons insérer une nouvelle colonne « Nom et prénom » dans notre feuille de calcul. Dans notre exemple, nous l'insérerons à droite de la colonne B :

Nous sommes prêts à entrer notre fonction dans la cellule C2. Comme d'habitude, nous commencerons par le signe égal « = » suivi du nom de la fonction et d'une parenthèse ouverte comme suit :

=CONCATENER(

Ensuite, nous devons entrer nos arguments. Les arguments indiquent à la fonction CONCATENER les cellules à combiner. Dans notre exemple, nous souhaitons combiner le texte des cellules A2 et B2. Nous allons donc en faire deux arguments :

=CONCATENER(B2; A2)

Finalement, exécutons cette fonction !

Vous avez peut-être remarqué que le prénom et le nom ne sont pas séparés par un espace. C'est parce que la fonction CONCATENER combinera exactement ce que vous lui demandez de combiner, et rien de plus ! Si vous souhaitez que la ponctuation, les espaces ou tout autre détail apparaissent dans la cellule, vous devez demander à la fonction CONCATENER de l'inclure.

Pour ajouter un espace, nous pouvons simplement ajouter un autre argument au milieu : " " (deux guillemets doubles autour d'un espace). Assurez-vous que les trois arguments sont séparés par des points-virgules :

=CONCATENER(B2; " "; A2)

Parfait ! Nous pouvons maintenant utiliser la poignée de recopie pour copier la formule dans les cellules restantes de notre nouvelle colonne :

Remarque : un moyen rapide de copier la formule dans d'autres cellules de la colonne consiste à sélectionner la cellule contenant la formule et à double-cliquer sur la poignée de recopie.

Notez que Microsoft Excel détermine dans quelle mesure copier les cellules après le double-clic de la poignée de remplissage en fonction des cellules référencées par votre formule. S'il y a des cellules vides dans votre tableau, disons que les cellules A4 et B4 étaient vides dans cet exemple, la formule serait copiée jusqu'à la ligne 3 uniquement. Dans ce cas, vous devez faire glisser manuellement la poignée de remplissage vers le bas pour concaténer toutes les colonnes restantes. Pour plus d’informations sur la copie de formule consulter notre tutoriel : comment étendre une formule.

Opérateur "&" pour concaténer des chaînes dans Excel

Dans Microsoft Excel, l'opérateur « & » est un autre moyen de concaténer des cellules. Cette méthode est très pratique dans de nombreux scénarios, car taper le signe esperluette (&) est beaucoup plus rapide que de taper le mot « CONCATENER ».

De la même manière que la fonction CONCATENER, vous pouvez utiliser « & » dans Excel pour combiner différentes chaînes de texte, valeurs de cellule et résultats renvoyés par d'autres fonctions.

Pour voir l'opérateur de concaténation en action, réécrivons les formules CONCATENER vues ci-dessus :

Pour concaténer les valeurs de A2 et B2, utilisez la formule suivante :

=B2 & A2

Pour concaténer les valeurs de A2 et B2 séparées par un espace, utilisez cette formule :

=B2 & " " & A2

Comme le montre la capture d'écran ci-dessous, la fonction « CONCATENER » et l'opérateur « & » renvoient des résultats identiques :

Concaténation d'une chaîne de texte et d'une valeur de cellule

Il n'y a aucune raison pour que la fonction Excel « CONCATENER » se limite à joindre uniquement les valeurs des cellules. Vous pouvez également l'utiliser pour ajouter diverses chaînes de texte afin de rendre le résultat plus significatif. Par exemple :

=CONCATENER(A2; " "; B2; " présent(e)")

La formule ci-dessus informe l'utilisateur qu'une certaine personne est présente, comme dans la ligne 2 de la capture d'écran ci-dessous. Veuillez noter que nous ajoutons un espace avant le mot « Présent » pour séparer les chaînes de texte concaténées :

Vous pouvez également ajouter une chaîne de texte au début ou au milieu de votre formule de concaténation par la même méthode.

Concaténation d'une chaîne de texte et d'une valeur calculée par une formule

Vous souhaitez voir la date du jour dans la cellule B3 ?

Dans la formule ci-dessous, nous avons utilisé les fonctions « CONCATENER », « TEXTE » et « AUJOURDHUI ». La fonction AUJOURDHUI dans Excel donne la date d'aujourd'hui. Cependant, elle donne le résultat sous forme d’un entier, que nous devons convertir en texte puis en format de date.

Ainsi, la fonction « TEXTE » dans Excel convertit la sortie de la fonction « AUJOURDHUI » au format « jj-mm-aaaa », puis la fonction de concaténation les réunit avec "Aujourd'hui est" et " ". Ainsi, cela donne le résultat ci-dessous :

Encore une fois, outre la fonction « CONCATENER », vous pouvez également travailler l’exemple ci-dessus en utilisant l’opérateur « & ».

Concaténer des chaînes de texte avec des sauts de ligne

Parfois, nos données doivent être séparées par un saut de ligne au lieu d'un espace ou d'un caractère comme indiqué dans les exemples précédents. L'exemple le plus courant est la fusion d'adresses postales importées à partir d'un fichier .csv ou PDF.

Pour le faire, nous ne pouvons pas simplement taper un saut de ligne dans la formule comme un caractère régulier. Ainsi, une fonction CAR spéciale est nécessaire pour fournir le code ASCII correspondant à la formule de concaténation.

Dans Windows, nous utilisons CAR(10) où 10 est le code ASCII pour le saut de ligne. Sur le système Mac, nous utilisons CAR(13) où 13 est le code ASCII pour un retour chariot.

Dans la capture d'écran ci-dessous, nous plaçons les informations d'adresse dans les colonnes A à E, et nous les rassemblons dans la colonne F en utilisant l'opérateur de concaténation « & ». Les valeurs fusionnées sont séparées par une virgule (","), un espace (" ") et un saut de ligne CAR(10) :

La formule utilisée est donc :                                                                                                            

=A2 & " " &B2 & CAR(10) & C2 & CAR(10) & D2 & "," & " " & E2

Pour obtenir le résultat, comme indiqué ci-dessus, nous devons activer l'option «Renvoyer à la ligne automatiquement » pour que les résultats s'affichent correctement. Pour ce faire, utilisez Ctrl + Shift + 1 pour ouvrir la boîte de dialogue « Format de cellule ». Passez à l'onglet « Alignement » et cochez la zone de « Renvoyer à la ligne automatiquement » ou allez dans l'onglet « Accueil » et sous « Alignement », cliquez sur « Renvoyer à la ligne automatiquement ».

De la même manière, vous pouvez séparer les chaînes concaténées avec d'autres caractères tels que :

• Double guillemets (") - CAR(34)
• Barre oblique (/) - CAR(47)
• Astérisque (*) - CAR(42)

Comment concaténer une plage de cellules dans Excel ?

La combinaison de valeurs provenant de plusieurs cellules peut prendre un certain effort car la fonction Excel CONCATENER n'accepte pas les tableaux et nécessite une référence de cellule unique dans chaque argument.

Pour concaténer plusieurs cellules, disons A1 à A4, vous avez besoin de l'une des formules suivantes :

=CONCATENER(A1; A2; A3; A4)

Ou :

=A1 & A2 & A3 & A4

Lorsque vous concaténez une plage assez petite, ce n'est pas méchant d'entrer toutes les références dans la barre de formule. Par contre, une plage plus large serait fastidieuse à ajouter en tapant chaque référence de cellule manuellement. Vous trouverez ci-dessous 2 méthodes de concaténation de plage rapide dans Excel.

Méthode 1. La touche CTRL pour sélectionner plusieurs cellules à concaténer

Pour sélectionner rapidement plusieurs cellules, vous pouvez appuyer sur la touche CTRL et cliquer sur chaque cellule que vous souhaitez inclure dans la formule CONCATENER. Trouvez ci-dessous les étapes détaillées :

1. Sélectionnez une cellule dans laquelle vous souhaitez saisir la formule.
2. Tapez =CONCATENER( dans cette cellule ou dans la barre de formule.
3. Maintenez la touche Ctrl enfoncée et cliquez sur chaque cellule que vous souhaitez concaténer.
4. Relâchez le bouton Ctrl, saisissez la parenthèse fermante « ) » dans la barre de formule et appuyez sur Entrée.

Lorsque vous utilisez cette méthode, vous devez cliquer sur chaque cellule individuelle. La sélection d'une plage avec la souris ajouterait un tableau à la formule, ce que la fonction CONCATENER n'accepte pas.

Méthode 2. La fonction TRANSPOSE pour obtenir la plage

La meilleure façon de combiner du texte de différentes cellules dans une seule cellule consiste à utiliser la fonction de transposition avec la fonction de concaténation.

1. Sélectionnez la cellule dans laquelle vous avez besoin d’obtenir le résultat.
2. Accédez à la barre de formule et insérer la formule =CONCATENER(TRANSPOSE(A1:A8) & " ") sans appuyer sur Entrée.

En fonction de vos paramètres régionaux, vous pouvez également essayer =A1:A8 (au lieu de =TRANSPOSE(A1:A8)).

3. Maintenant, sélectionnez simplement toute la partie intérieure de la fonction de concaténation et appuyez sur F9. Cette action convertira le tout en un tableau.

4. Ensuite, modifiez les points en points-virgules :

5. Supprimez les accolades du début et de la fin du tableau.
6. appuyez sur Entrée.

Notez que dans ce cas, nous avons utilisé un caractère espace comme séparateur (délimiteur). Si vous le souhaitez, vous pouvez utiliser d'autres séparateurs tels qu'une virgule ou un trait d'union.

Comment fonctionne cette formule ?

• Dans cette formule, vous avez utilisé TRANSPOSE et un espace dans une formule CONCATENER.
• Lorsque vous convertissez cette référence en valeurs matérielles, elle renvoie un tableau.
• Dans ce tableau, vous avez du texte de chaque cellule avec des espaces.
• Lorsque vous appuyez sur Entrée, le tout est combiné.

Opérateur "&" et la fonction CONCATENER, quel est le meilleur à utiliser ?

De nombreux utilisateurs se demandent quel est le moyen le plus efficace de concaténer des chaînes dans Excel – la fonction « CONCATENER » ou l’opérateur « & ».

La seule et essentielle différence entre CONCATENER et l'opérateur "&" est la limite de 255 chaînes de la fonction Excel « CONCATENER » contre aucune limitation de ce type lors de l'utilisation de l'esperluette « & ».

Et comme 255 est un très grand nombre puisque dans les tâches de la vie réelle nous n’aurons presque jamais besoin de combiner autant de cordes, le choix de telle ou telle méthode dépend du confort et de la facilité d'utilisation. Certains utilisateurs trouvent les formules « CONCATENER » plus faciles à lire, et d’autres préfèrent utiliser la méthode plus courte "&". Ceci dit, utilisez simplement la technique de concaténation avec laquelle vous vous sentez plus à l'aise et le résultat sera le même !

Notes importantes sur la fonction CONCATENER

Donne le résultat sous forme de chaîne de texte.

• Convertit les nombres en texte lorsqu'ils sont joints.
• Ne reconnaît pas les tableaux. Par conséquent, nous devons fournir chaque référence de cellule séparément.
• L’erreur #VALEUR! se produit lorsqu'au moins un des arguments de la fonction CONCATENER n'est pas valide.
• Les nombres n'ont pas besoin d'être entre guillemets.
• L’erreur #NOM? se produit lorsqu'il manque des guillemets dans un argument texte.
• Dans de nombreux cas, l'utilisation de l'esperluette (&) est plus rapide et plus simple que l'utilisation de CONCATENER pour créer des chaînes.

Il peut y avoir des situations différentes pour vous dans lesquelles vous devez concaténer une plage de cellules en une seule cellule. Et c'est pourquoi, nous avons mis à votre disposition ces différentes méthodes simples et rapides. Choisissez donc la solution de concaténation qui convient le plus à vos besoins !

Télécharger le fichier Excel de ce tutoriel