Passer au contenu principal

À l'opposé de Concaténer (cellules divisées) dans Excel – 4 méthodes simples

La fonction Concaténer dans Excel est utilisée pour fusionner le contenu de plusieurs cellules en une seule cellule, mais vous devrez parfois faire l'inverse, c'est-à-dire diviser le contenu d'une cellule en plusieurs cellules. Cette opération est très utile pour l’analyse et l’organisation des données. Cet article présentera quatre méthodes simples pour y parvenir, vous aidant à gérer vos données plus efficacement dans Excel, que vous soyez un utilisateur débutant ou avancé.

Effectuer l'opposé de CONCATENATE dans Excel


Effectuer l'opposé de CONCATENATE avec des formules

Pour effectuer l'inverse de la fonction CONCATENATE, vous pouvez utiliser des formules permettant de diviser les chaînes de texte en plusieurs cellules en fonction d'un délimiteur spécifique.

  1. Entrez ou copiez la formule suivante dans une cellule vide où vous souhaitez placer le résultat, puis faites glisser la poignée de recopie vers les cellules pour remplir cette formule. Les valeurs des cellules de la cellule A2 ont été divisées en cellules séparées, voir capture d'écran :
    =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
    Notes: Dans la formule ci-dessus, vous pouvez également diviser les valeurs des cellules en plusieurs cellules en utilisant différents séparateurs. Remplacez simplement le espace entre les premiers guillemets avec le délimiteur de votre choix.
  2. Continuez ensuite à faire glisser la poignée de recopie vers la plage de cellules à laquelle vous souhaitez appliquer cette formule. Cela divisera toutes les valeurs de cellule en colonnes distinctes, comme illustré ci-dessous :
Conseil: Formule pour Excel 365

Si vous utilisez Excel 365, vous trouverez une fonction plus simple et plus efficace : TEXTESPLIT qui est disponible pour diviser les données en plusieurs colonnes.

  1. Veuillez appliquer la formule suivante, après avoir appuyé sur Entrer clé, chaque élément séparé par un espace va automatiquement dans une colonne individuelle, voir capture d'écran :
    =TEXTSPLIT(A2, " ")
  2. Ensuite, sélectionnez les cellules contenant le résultat puis faites glisser la poignée de recopie vers le bas pour appliquer la formule et obtenir les résultats restants, voir capture d'écran :

Effectuez l’opposé de CONCATENATE avec une fonctionnalité puissante

Kutools for Excel fournit une interface conviviale Cellules divisés fonctionnalité, avec cette fonctionnalité intelligente, vous pouvez rapidement diviser le contenu de la cellule en plusieurs colonnes ou lignes en fonction de n'importe quel séparateur selon vos besoins.

Notes: Si vous souhaitez l'utiliser Cellules divisés fonctionnalité, s'il vous plaît télécharger et installer Kutools pour Excel d'abord.

Après avoir téléchargé et installé Kutools for Excel, puis clique Kutools > Fusionner et fractionner > Cellules divisés pour permettre cela Cellules divisés fonctionnalité. Dans la boîte de dialogue, veuillez définir les options comme ci-dessous :

  1. Cliquez pour sélectionner la liste de données que vous souhaitez séparer du Plage divisée section;
  2. En vertu des Normes sur l’information et les communications, les organismes doivent rendre leurs sites et applications Web accessibles. Ils y parviennent en conformant leurs sites Web au niveau AA des Web Content Accessibility Guidelines (WCAG). Type section, sélectionnez Fractionner en colonnes l'option;
  3. Extrait du Divisé par , veuillez choisir le séparateur sur lequel vous souhaitez diviser le contenu de la cellule. Ici, je choisirai Space;
  4. Puis clique OK bouton;
  5. Dans une autre boîte de dialogue, cliquez sur une cellule dans laquelle vous souhaitez placer le résultat ;
  6. Cliquez OK pour obtenir le résultat.
Notes: Pour appliquer cette fonctionnalité, veuillez télécharger et installer Kutools pour Excel d'abord.

Effectuez l’opposé de CONCATENATE avec la fonction Flash Fill

Excel 2013 et les versions ultérieures ont introduit une fonctionnalité remarquable connue sous le nom de Flash Fill. Cet outil vous aide non seulement à remplir automatiquement les cellules avec des données, mais vous permet également de diviser le contenu des cellules de manière transparente. Dans cette section, j'explorerai comment utiliser la fonctionnalité Flash Fill pour diviser des données dans Excel.

  1. Dans une cellule adjacente (B2) à côté de votre colonne de données d'origine où vous souhaitez diviser les données, tapez le prénom souhaité - Lucky. Puis appuyez Entrer touche pour passer à la cellule suivante. Voir capture d'écran :
  2. Puis appuyez Ctrl + E pour remplir le reste des cellules, voir capture d'écran :
  3. Suivez la même procédure pour les colonnes Nom de famille et Anniversaire, et vous obtiendrez le résultat sous forme de démonstration ci-dessous :
Notes:
  • Flash Fill nécessite que les données de sortie soient adjacentes aux données d'origine sans aucune colonne vide entre les deux.
  • Les résultats obtenus grâce à la fonctionnalité Flash Fill sont statiques, ce qui signifie que si des mises à jour sont apportées aux données d'origine, vous devrez réexécuter la fonctionnalité pour refléter ces modifications.
  • Pour en savoir plus sur cette fonctionnalité Fill Flash, veuillez consulter ceci Maîtrisez la saisie de données Excel avec Flash Fill tutoriel.

Effectuez l'opposé de CONCATENATE avec la fonctionnalité Texte en colonne

En fait, dans Excel, la fonctionnalité Texte en colonnes est à l'opposé de la fonction de concaténation, vous pouvez l'appliquer pour diviser les valeurs des cellules en plusieurs cellules à l'aide des séparateurs dont vous avez besoin.

  1. Sélectionnez les cellules que vous souhaitez diviser. Et puis, cliquez Données > Texte aux colonnes, voir capture d'écran:
  2. In Étape 1 de l'assistant Convertir du texte en colonnes, sélectionnez Délimité option, puis cliquez sur Suivant bouton, voir capture d'écran:
  3. In Étape 2 de l'assistant, spécifiez le séparateur que vous souhaitez utiliser sous le Delimiters option. Ici, je vais vérifier Space. Et puis continuez à cliquer sur le Suivant bouton, voir capture d'écran:
  4. In Étape 3 de l'assistant, vérifier GénèreJe suis sous le Format de données de colonne, puis sélectionnez une cellule dans laquelle vous souhaitez localiser les valeurs de fractionnement à partir du dentaire champ. Enfin, cliquez sur Finition bouton, voir capture d'écran:
  5. Désormais, les valeurs des cellules sélectionnées ont été divisées en plusieurs colonnes, voir capture d'écran :

Chacune de ces méthodes répond aux différentes préférences des utilisateurs et niveaux de complexité des données, permettant une manipulation et une organisation efficaces des données dans Excel. Que vous ayez besoin de diviser des noms, des dates ou toute autre donnée structurée, ces techniques offrent des solutions précieuses pour améliorer vos compétences Excel et vos capacités de gestion de données. Si vous souhaitez découvrir davantage de trucs et astuces Excel, notre site Web propose des milliers de didacticiels, veuillez cliquez ici pour y accéder. Merci d'avoir lu et nous sommes impatients de vous fournir des informations plus utiles à l'avenir !


Articles connexes:

  • Diviser les chaînes de texte par délimiteur en plusieurs lignes - 3 astuces rapides
  • Normalement, vous pouvez utiliser la fonction Texte en colonne pour diviser le contenu des cellules en plusieurs colonnes par un délimiteur spécifique, tel qu'une virgule, un point, un point-virgule, une barre oblique, etc. Mais, parfois, vous devrez peut-être diviser le contenu des cellules délimitées en plusieurs lignes et répétez les données des autres colonnes comme illustré ci-dessous. Avez-vous de bons moyens de gérer cette tâche dans Excel ? Ce didacticiel présentera quelques méthodes efficaces pour effectuer ce travail dans Excel.
  • Divisez une grande table en plusieurs petites tables
  • Si vous avez une grande feuille de calcul qui contient plusieurs colonnes et des centaines ou des milliers de données de lignes, vous souhaitez maintenant diviser cette grande table en plusieurs petites tables en fonction de la valeur de la colonne ou du nombre de lignes pour obtenir les résultats suivants. Comment pourriez-vous gérer cette tâche dans Excel?
  • Diviser le nom complet en prénom et nom
  • Supposons que vous ayez une liste de noms comme le montre la première capture d'écran dans une seule colonne ci-dessous, et que vous devez diviser le nom complet dans la colonne du prénom , la colonne du deuxième prénom et la colonne du nom de famille comme illustré ci-dessous. Voici quelques méthodes délicates pour vous aider à résoudre ce problème.
  • Diviser le texte en colonnes séparées par lettre majuscule
  • En supposant que vous disposiez d'une plage de données pour laquelle vous souhaitez diviser le contenu de la cellule en colonnes séparées par la lettre majuscule comme illustré dans la capture d'écran suivante, avez-vous de bonnes idées pour résoudre ce problème dans Excel?
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Many thanks for your wonderful website and the helpful solutions.
I am using a form on my website to collect CVs and the excel output is like below:

Degree | College Name | Discipline | Year of Graduation | GPA
Masters | Oxford | Mathematics | 2020 | 88
Bachelors | Cambridge | Chemistry | 2016 | 76
Diploma | George's School | Arts | 2012 | 94

All the above lies in a single cell in excel / CSV file.

Can anybody please help me how I can rearrange all this into an excel table with each cell showing part of the entries.

I appreciate your solutions.

best regards,
Nasser
This comment was minimized by the moderator on the site
Hello, Emami

To split the data you provided, both the formula and Text to Column feature can help you. please do as this:
Method1: formula: (After pasting the formula, drag the fill handle right to get the first line result, and then drag the fill handle down to get other results.)
=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-reverse-concatenate.png

Method2: Text to Column:
By using Text to Column feature step by step, and in step3, please enter | character into Other box:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-reverse-concatenate-2.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Dear skyyang,
Many thanks for the reply.
First, I cannot use the text to column method because this process has to be done automatically by a formula.
Second, the problem is the table output I receive from csv file (the form entry) is saved in a single cell not in three rows.
ie. All three rows (A1:A4) are stored in one single cell not 4 cell in 4 rows.
I hope you understand my problem and can provide me with a solution.

thanks.
This comment was minimized by the moderator on the site
Hello, Emami,
If your data in a single cell, first, you should split the data into multiple rows based on the line breaks. to split the data into rows, please use this code:
Sub SplitCells()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    lLFs = VBA.Len(Rng) - VBA.Len(VBA.Replace(Rng, vbLf, ""))
    If lLFs > 0 Then
        Rng.Offset(1, 0).Resize(lLFs).Insert shift:=xlShiftDown
        Rng.Resize(lLFs + 1).Value = Application.WorksheetFunction.Transpose(VBA.Split(Rng, vbLf))
    End If
Next
End Sub


Applying the code to split the data into multiple rows, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-split-data.png

Then, you can use this formula to split them into multiple columns as you need.
=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",999)),COLUMNS($A:A)*999-998,999))


Please have a try, hope it can help you, if you have any other problem, please comment here.
This comment was minimized by the moderator on the site
I believe it has something to do with having over a certain amount of value in a cell. How does one use this formula with large strings?
This comment was minimized by the moderator on the site
Thank you for this. I had a strange occurence where two strings of text produced #Value!, while all the rest was fine. Any idea why this would happen?
This comment was minimized by the moderator on the site
just want to know if in a cell content i.e (123). can it be splitted to multiple column seperately as cell1 as 1, cell2 as 2 and cell3 as 3
This comment was minimized by the moderator on the site
my query is below
from given data
A B
1 Capital Account Capital Account 6,62,73,000.00
2 SHARE CAPITAL A/C SHARE CAPITAL A/C 67,17,300.00
3 SHARE PREMIUM A/C SHARE PREMIUM A/C 5,95,55,700.00


I need in column C (C + B-A) i.e
C
1 6,62,73,000.00

2 67,17,300.00

3 5,95,55,700.00
This comment was minimized by the moderator on the site
sorry I m not see perfect in first time
this formula is perfect and correct
This comment was minimized by the moderator on the site
you are A Rong Talling thissss formula
This comment was minimized by the moderator on the site
Excellent directions!
This comment was minimized by the moderator on the site
oh snap, i got it above...using text to columns then split option = 'space'
This comment was minimized by the moderator on the site
Thank you so much, helpful indeed. What if there is no comma delimiter in between the words like, "Steve Simpson" and you still want to split?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations