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
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
Notes importantes sur la fonction CONCATENER
La fonction Excel CONCATENER
La fonctionCONCATENERest 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 fonctionCONCATENERpour joindre les prénoms et noms dans une seule colonne.
La syntaxe de la fonctionCONCATENERdans 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 CONCATENERseradisponible 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 fonctionCONCATENERpour le faire automatiquement et rapidement.
Dans notre exemple, nous pouvons utiliser la fonction de concaténation pour combiner le texte de la colonneAet celui de la colonneBafin 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 cellulesA2etB2. 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 fonctionCONCATENERcombinera 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 valeursdeA2et B2, utilisez la formule suivante :
=B2 & A2
Pour concaténer les valeursdeA2et 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 celluleB3?
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 fonctionCARspéciale est nécessaire pour fournir le code ASCII correspondant à la formule de concaténation.
DansWindows, nous utilisonsCAR(10)où 10 est le code ASCII pour le saut de ligne. Sur le systèmeMac, nous utilisonsCAR(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 colonnesAàE, et nous les rassemblons dans la colonneFen utilisant l'opérateur de concaténation « & ». Les valeurs fusionnées sont séparées par une virgule (","), un espace (" ") et un saut de ligneCAR(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, utilisezCtrl + Shift + 1pour 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 toucheCTRLet cliquer sur chaque cellule que vous souhaitez inclure dans la formuleCONCATENER. Trouvez ci-dessous les étapes détaillées :
- Sélectionnez une cellule dans laquelle vous souhaitez saisir la formule.
- Tapez=CONCATENER(dans cette cellule ou dans la barre de formule.
- Maintenez la toucheCtrlenfoncée et cliquez sur chaque cellule que vous souhaitez concaténer.
- Relâchez le boutonCtrl, saisissez la parenthèse fermante«)» dans la barre de formule et appuyez surEntré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 fonctionCONCATENERn'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.
- Sélectionnez la cellule dans laquelle vous avez besoin d’obtenir le résultat.
- 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)).
- Maintenant, sélectionnez simplement toute la partie intérieure de la fonction de concaténation et appuyez surF9. Cette action convertira le tout en un tableau.
- Ensuite, modifiez les points en points-virgules:
- Supprimez les accolades du début et de la fin du tableau.
- 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éTRANSPOSEet un espace dans une formuleCONCATENER.
- 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 lalimite de 255 chaînesde la fonction Excel «CONCATENER» contreaucune limitationde 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
