Comment concaténer des cellules en ignorant ou en sautant les cellules vides dans Excel ?

La fonction Concaténer d'Excel peut vous aider à combiner rapidement plusieurs valeurs de cellules en une seule. Si certaines cellules sélectionnées sont vides, cette fonction inclura également ces espaces vides. Cependant, parfois, vous souhaitez uniquement concaténer les cellules contenant des données et ignorer les cellules vides. Comment pouvez-vous réaliser cela dans Excel ?

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec une formule

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec une fonction définie par l'utilisateur

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec Kutools pour Excel

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec une formule

Supposons que j'ai les données suivantes dans des cellules, avec quelques cellules vides. Pour combiner les lignes en une seule cellule, vous pouvez appliquer une formule pour résoudre ce problème.

Veuillez entrer cette formule dans une cellule vide où vous souhaitez afficher le résultat concaténé : =A1&SI(A2<>"";"-"&A2;"")&SI(A3<>"";"-"&A3;"")&SI(A4<>"";"-"&A4;"")&SI(A5<>"";"-"&A5;""), puis faites glisser la poignée de recopie vers les cellules où vous souhaitez appliquer cette formule. Les valeurs des lignes ont été combinées sans inclure les cellules vides, comme le montre la capture d'écran suivante :

Astuce : Si davantage de lignes doivent être concaténées, vous devez utiliser la fonction SI pour joindre les cellules, par exemple : =A1&SI(A2<>"";"-"&A2;"")&SI(A3<>"";"-"&A3;"")&SI(A4<>"";"-"&A4;"")&SI(A5<>"";"-"&A5;"")&SI(A6<>"";"-"&A6;""). Le caractère "–" dans la formule peut être remplacé par n'importe quel autre délimiteur dont vous avez besoin.

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec une fonction définie par l'utilisateur

S'il y a plusieurs cellules à combiner, la formule ci-dessus deviendra trop complexe à exécuter. Vous pouvez donc utiliser la fonction définie par l'utilisateur suivante pour résoudre ce problème.

1. Maintenez les touches Alt + F11 enfoncées dans Excel, ce qui ouvre la fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez sur Insertion > Module, puis collez la macro suivante dans la fenêtre du module.

Code VBA : Concaténer des cellules en ignorant les cellules vides :

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "/"): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

3. Enregistrez et fermez cette fenêtre de code, retournez à la feuille de calcul, puis entrez cette formule : =concatenercellules(A1:A5) dans une cellule vide, et faites glisser la poignée de recopie vers les cellules où vous souhaitez appliquer cette formule, et vous obtiendrez le résultat suivant :

Astuce : Dans le code VBA ci-dessus, vous pouvez remplacer le caractère “/” par n'importe quel autre délimiteur dont vous avez besoin.

Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec Kutools pour Excel

Outre les formules et codes fastidieux, voici un outil pratique que je peux recommander - Kutools pour Excel. Avec son utilitaire Combine puissant, vous pouvez concaténer plusieurs cellules en une seule sans effort.

Après avoir installé Kutools pour Excel, veuillez procéder comme suit :

1. Sélectionnez les valeurs des cellules que vous souhaitez combiner.

2. Cliquez sur Kutools > Fusionner et Diviser > Combiner les lignes/colonnes/cellules et conserver les valeurs, voir capture d'écran :

3. Dans la boîte de dialogue qui apparaît :

• (1.) Choisissez le type de cellules que vous souhaitez combiner. Vous pouvez combiner des colonnes, des lignes ou toutes les cellules en une seule cellule ;
• (2.) Spécifiez un séparateur pour vos contenus concaténés ;
• (3.) Choisissez l'emplacement où placer le résultat, vous pouvez le mettre dans la cellule supérieure ou inférieure ;
• (4.) Spécifiez comment traiter les cellules combinées, vous pouvez conserver ou supprimer le contenu de ces cellules combinées, et vous pouvez également fusionner ces cellules combinées.

4. Après avoir terminé les paramètres, cliquez sur le bouton OK, et les lignes sélectionnées ont été combinées en une seule cellule respectivement, en ignorant automatiquement les cellules vides, voir capture d'écran :

Téléchargez et essayez gratuitement Kutools pour Excel dès maintenant !

Démo : Concaténer des cellules tout en ignorant ou en sautant les cellules vides avec Kutools pour Excel