Passer au contenu principal

Comment concaténer des cellules ignorer ou ignorer les blancs dans Excel?

Excel's Enchaîner La fonction peut vous aider à combiner rapidement plusieurs valeurs de cellule dans une cellule, s'il y a des cellules vides dans les cellules sélectionnées, cette fonction combinera également les blancs. Mais, parfois, vous voulez simplement concaténer des cellules avec des données et ignorer les cellules vides, comment pourriez-vous le terminer dans Excel?

Concaténer les cellules ignorer ou ignorer les blancs avec la formule

Concaténer les cellules ignorer ou ignorer les blancs avec la fonction définie par l'utilisateur

Concaténer les cellules ignorer ou ignorer les espaces avec Kutools for Excel


Supposons que je dispose des données de cellules suivantes remplies de cellules vides, pour combiner les lignes en une seule cellule, vous pouvez appliquer une formule pour le résoudre.

doc combiner sauter les blancs 1

Veuillez entrer cette formule dans une cellule vide où vous souhaitez mettre le résultat concaténé, =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""), puis faites glisser la poignée de recopie vers les cellules auxquelles vous souhaitez appliquer cette formule, et les valeurs des lignes ont été combinées sans les cellules vides comme illustré ci-dessous:

doc combiner sauter les blancs 2

Pourboires: S'il y a plus de lignes à concaténer, vous devez utiliser la fonction IF pour joindre les cellules, comme =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,""). Le "-" Le caractère de la formule peut être remplacé par tout autre délimiteur dont vous avez besoin.


Concaténer plusieurs cellules, ignorer ou ignorer les espaces:

Kutools for Excel's Combiner Lignes, colonnes ou cellules sans perte de données La fonction peut vous aider à combiner ou concaténer plusieurs lignes, colonnes ou cellules sans perdre de données mais en ignorant ou en ignorant les cellules vides. Cliquez pour télécharger Kutools for Excel!

doc combiner sauter les blancs 7

Kutools for Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!


Si plusieurs cellules doivent être combinées, la formule ci-dessus sera trop complexe à exécuter, vous pouvez donc utiliser la fonction définie par l'utilisateur suivante pour la résoudre.

1. Maintenez le Alt + F11 clés dans Excel, et il ouvre le Microsoft Visual Basic pour applications fenêtre.

2Cliquez sur insérer > Moduleet collez la macro suivante dans le Module Fenêtre.

Code VBA: les cellules de concaténation ignorent les blancs:

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, revenez à la feuille de calcul, puis entrez cette formule: = cellules concaténées (A1: A5) dans une cellule vide et faites glisser la poignée de recopie vers les cellules pour appliquer cette formule, et vous obtiendrez le résultat suivant:

doc combiner sauter les blancs 3

Pourboires: Dans le code VBA ci-dessus, vous pouvez modifier le "/”À tout autre délimiteur dont vous avez besoin.


Sauf la formule et le code ennuyeux, ici, je peux recommander un outil pratique-Kutools for Excel, avec son puissant Combiner utilitaire, vous pouvez concaténer plusieurs cellules en une seule cellule sans effort.

Kutools for Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours

Après l'installation de Kutools for Excel, procédez comme suit:

1. Sélectionnez la valeur des cellules que vous souhaitez combiner.

2Cliquez sur Kutools > Fusionner et fractionnerCombinez des lignes, des colonnes ou des cellules sans perdre de données, 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, combiner des lignes et combiner toutes les cellules en une seule cellule;
  • (2.) Spécifiez un séparateur pour votre contenu concaténé;
  • (3.) Choisissez l'emplacement pour mettre le résultat, vous pouvez mettre le résultat dans la cellule supérieure ou la cellule 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.

doc combiner sauter les blancs 5

4. Après avoir terminé les réglages, cliquez sur Ok bouton, et les lignes sélectionnées ont été combinées dans une cellule séparément, et il a ignoré les blancs automatiquement, voir capture d'écran:

doc combiner sauter les blancs 6

Téléchargez et essayez gratuitement Kutools for Excel Now!


Kutools for Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!

Meilleurs outils de productivité bureautique

🤖 Aide à l'IA Kutools: Révolutionner l'analyse des données en s'appuyant sur : Exécution intelligente   |  Générer du code  |  Créer des formules personnalisées  |  Analyser les données et générer des graphiques  |  Invoquer les fonctions Kutools...
Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Description


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would you change the "/" sign for CHAR(10) or line break?
This comment was minimized by the moderator on the site
Hello, Alan,To combine the cells with the line break, you can apply the below simple formula:=TEXTJOIN(CHAR(10),TRUE,A1:A5)
After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
Please try, thank you!
This comment was minimized by the moderator on the site
super, thank Q
This comment was minimized by the moderator on the site
I had issues with the formula provided causing leading delimiters, so I did this instead and it works well. This is concatenating cells horizontally while ignoring blank cells and results in no extra commas.

=IF(A2="", "", A2)&IF(A2="", "", ", ")&IF(B2="", "", B2)&IF(B2="", "", ", ")&IF(C2="", "", C2)
This comment was minimized by the moderator on the site
This worked a treat, thanks so much
This comment was minimized by the moderator on the site
Hi, used the macro and changed the "/" to a comma "," but got a lot of commas and it appeared to add all the blank cells.
I am doing a nested if statement to determine the appropriate sorting in the database. Is this enough to make the blank cell 'active' so that the macro sees this and adds it to the text string? How to work around that?
thanks much
This comment was minimized by the moderator on the site
Hello, Melinda,
the above vba code works well in my worksheet, you just need to change the separator / to comma as below:

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

and then apply this formula:=concatenatecells(A1:A5)
This comment was minimized by the moderator on the site
thanks!
I found my problem was in the logic statement that I used to select data for these cells that I was trying to text string. I used a " " instead of "" for the false statement. That was picked up by the macro and used as a space bar and came out , , , , , text, , , ,
So I went back and took out the space and just have the "" and then the macro worked great.
Of course I am learning macros so that's another adventure.
thanks much!!
This comment was minimized by the moderator on the site
Thank you, it was very helpful!
This comment was minimized by the moderator on the site
Please help, i dont always have a value in my first column, that couses that I end up with a seperator infort of the final result. Is there a way around this?
This comment was minimized by the moderator on the site
ever get tis figured out? same boat.
This comment was minimized by the moderator on the site
I don't know how to do it all in one cell, but I added a row with this formula: =IF(LEFT(U20,1)=",",RIGHT(U20,LEN(U20)-2),U20).
My separator was a comma and a space ", " so I used -2 for LEN. U20 is the cell with the concatenated &if formula. The logic of this formula is that if the first character from the left equals a comma, then delete the first two characters; otherwise leave it alone.

Hope this helps.
This comment was minimized by the moderator on the site
Thank you! This saved hours of frustration on my part! Works as a charm!
This comment was minimized by the moderator on the site
The VBA script is wrong, because the output of the formula puts a huge space between the delimiters.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations