comment diviser une chaîne de texte dans Excel par une virgule, un espace, un caractère ou un masque
le tutoriel explique comment diviser des cellules dans Excel à l’aide de formules et de la fonction diviser le texte. Vous apprendrez à séparer le texte par une virgule, un espace ou tout autre délimiteur, et comment diviser les chaînes en texte et en nombres.
diviser le texte d’une cellule en plusieurs cellules est la tâche à laquelle tous les utilisateurs D’Excel ont affaire de temps en temps., Dans l’un de nos articles précédents, nous avons discuté de la façon de diviser les cellules dans Excel en utilisant la fonction Texte en colonne, le remplissage Flash et le complément Split Names. Aujourd’hui, nous allons examiner en profondeur comment vous pouvez diviser des chaînes à l’aide de formules et de la fonctionnalité diviser le texte.,
- Comment diviser les cellules dans Excel à l’aide de formules
- diviser la chaîne par virgule, deux points, barre oblique, tiret ou autre délimiteur
- diviser la chaîne par saut de ligne
- formules pour diviser le texte et les nombres
- Comment séparer les cellules avec la fonction de>
comment diviser du texte dans Excel à l’aide de formules
pour diviser une chaîne dans Excel, vous utilisez généralement la fonction left, right ou Mid en combinaison avec Find ou search., À première vue, certaines formules peuvent sembler complexes, mais la logique est en fait assez simple, et les exemples suivants vous donneront quelques indices.
diviser la chaîne par une virgule, Un deux-points, une barre oblique, un tiret ou un autre délimiteur
lors du fractionnement de cellules dans Excel, la clé consiste à localiser la position du délimiteur dans la chaîne de texte. Selon votre tâche, cela peut être fait en utilisant une recherche insensible à la casse ou une recherche sensible à la casse. Une fois que vous avez la position du délimiteur, utilisez la fonction DROITE, GAUCHE ou milieu pour extraire la partie correspondante de la chaîne de texte., Pour une meilleure compréhension, considérons l » exemple suivant.
supposons que vous ayez une liste de références du motif Item-Color-Size et que vous souhaitiez diviser la colonne en 3 colonnes distinctes:
comment diviser une chaîne par saut de ligne dans Excel
pour diviser le texte par espace, utilisez des formules similaires à celles La seule différence est que vous aurez besoin de la fonction CHAR pour fournir le caractère de saut de ligne car vous ne pouvez pas le taper directement dans la formule.,
supposons que les cellules que vous souhaitez diviser ressemblent à ceci:
prenez les formules de l’exemple précédent et remplacez un trait d’union (« -« ) par CHAR(10) où 10 est le code ASCII pour le saut de ligne.
Et voici à quoi ressemble le résultat:
comment diviser le texte et les nombres dans Excel
pour commencer, il n’y a pas de solution universelle qui fonctionnerait pour toutes les chaînes alphanumériques. La formule à utiliser dépend du modèle de chaîne particulier. Vous trouverez ci-dessous les formules pour les deux scénarios.,
Split string of « text + number » pattern
supposons que vous ayez une colonne de chaînes avec du texte et des nombres combinés, où un nombre suit toujours le texte. Vous voulez casser les chaînes d’origine pour que le texte et les nombres apparaissent dans des cellules séparées, comme ceci:
le résultat peut être obtenu de deux manières différentes.,
Méthode 1: compter les chiffres et extraire autant de caractères
la façon la plus simple de diviser une chaîne de texte où le nombre vient après le texte est la suivante:
pour extraire des nombres, vous recherchez dans la chaîne tous les nombres possibles de 0 à 9, obtenez le total des nombres et renvoyez,
avec la chaîne D’origine en A2, la formule est la suivante:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
pour extraire du texte, vous calculez le nombre de caractères de texte que la chaîne contient en soustrayant le nombre de chiffres extraits (C2) de la longueur totale de la chaîne d’origine en A2. Après cela, vous utilisez la fonction LEFT Pour renvoyer autant de caractères depuis le début de la chaîne.,
=LEFT(A2,LEN(A2)-LEN(C2))
Où A2 est la chaîne d’origine, et C2 est l’extrait numéro, comme illustré dans la capture d’écran:
Méthode 2: Retrouver la position de la 1ère chiffres dans une chaîne de caractères
Une autre solution serait d’utiliser la formule suivante pour déterminer la position du premier caractère dans la chaîne de caractères:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
L’explication détaillée de la formule »la logique peut être trouvé ici.,
Une fois la position du premier chiffre trouvée, vous pouvez diviser le texte et les nombres en utilisant des formules très simples à gauche et à droite.
Pour extraire le texte:
=LEFT(A2, B2-1)
Pour extraire le numéro:
=RIGHT(A2, LEN(A2)-B2+1)
Où A2 est la chaîne d’origine, et B2 est la position du premier numéro., le premier chiffre, vous pouvez intégrer la formule MIN dans les fonctions gauche et droite:
formule pour extraire le texte:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
formule pour extraire les nombres:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
chaîne fractionnée du motif « nombre + texte »
Si vous lorsque le texte apparaît après le nombre, vous pouvez extraire les nombres avec la formule suivante:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
la formule est similaire à celle décrite dans l’exemple précédent, sauf que vous utilisez la fonction left au lieu de RIGHT pour obtenir le nombre du côté gauche de la chaîne.,
une Fois que vous avez les chiffres, l’extrait de texte en soustrayant le nombre de chiffres de la longueur totale de la chaîne d’origine:
=RIGHT(A2,LEN(A2)-LEN(B2))
Où A2 est la chaîne d’origine et B2 est l’extrait du nombre, comme illustré dans la capture d’écran ci-dessous:
Voici comment vous pouvez diviser des chaînes dans Excel en utilisant différentes combinaisons de fonctions différentes., Comme vous le voyez, les formules sont loin d’être évidentes, vous pouvez donc télécharger L’exemple de classeur Excel Split Cells pour les examiner de plus près.
Si comprendre les arcanes des formules Excel n’est pas votre occupation préférée, vous aimerez peut-être la méthode visuelle pour diviser les cellules dans Excel, qui est démontrée dans la partie suivante de ce tutoriel.,
comment diviser des cellules dans Excel avec la fonction Split Text
Une autre façon de diviser une colonne dans Excel est d’utiliser la fonction Split Text incluse dans notre Ultimate Suite for Excel, qui fournit les options suivantes:
- diviser une cellule par caractère
- diviser une cellule par chaîne
- diviser une cellule par masque (motif)
pour rendre les choses plus claires, examinons de plus près chaque option, une à la fois.
diviser les cellules par caractère
choisissez cette option chaque fois que vous souhaitez diviser le contenu de la cellule à chaque occurrence du caractère spécifié.,
pour cet exemple, prenons les chaînes du motif Item-Color-Size que nous avons utilisé dans la première partie de ce tutoriel. Comme vous vous en souvenez peut-être, nous les avons séparés en 3 colonnes différentes en utilisant 3 formules différentes. Et voici comment vous pouvez obtenir le même résultat en 2 étapes rapides:
- En supposant que vous avez Ultimate Suite installé, sélectionnez les cellules à diviser et cliquez sur L’icône de texte divisé dans L’onglet Données Ablebits.,
- Le volet de texte divisé s’ouvrira sur le côté droit de votre fenêtre Excel, et vous procéderez comme suit:
- développez le groupe de caractères divisé par, et sélectionnez l’un des délimiteurs prédéfinis ou tapez tout autre caractère dans la boîte personnalisée.
- choisissez de diviser les cellules en colonnes ou en lignes.
- examinez le résultat dans la section Aperçu, puis cliquez sur le bouton Diviser.
terminé! La tâche qui nécessitait 3 formules et 5 fonctions différentes ne prend plus que quelques secondes et un clic sur un bouton.
diviser les cellules par chaîne
Cette option vous permet de diviser les chaînes en utilisant n’importe quelle combinaison de caractères comme délimiteur. Techniquement, vous divisez une chaîne en parties en utilisant une ou plusieurs sous-chaînes différentes comme limites de chaque partie.,
Par exemple, pour diviser une phrase par les conjonctions « et » et « ou », développez le groupe Split by strings, et entrez les chaînes de délimitation, une par ligne:
en conséquence, la phrase source est séparée à chaque occurrence de chaque délimiteur:
Et voici un autre exemple réel., Supposons que vous ayez importé une colonne de dates à partir d’une source externe, qui se présente comme suit:
5.1.2016 12:20
5.2.2016 14:50
ce format n’est pas conventionnel pour Excel, et donc aucune des fonctions de Date ne reconnaîtrait aucun des éléments de date ou d’heure. Pour diviser le jour, le mois, l’année, les heures et les minutes en cellules séparées, entrez les caractères suivants dans la zone diviser par chaînes:
- Dot (., pour séparer le jour, le mois et l’année
- deux points (:) pour séparer les heures et les minutes
- espace pour séparer la date et l’heure
appuyez sur le bouton Diviser, et vous obtiendrez immédiatement le résultat:
cellules par masque (motif)
séparer une cellule par masque signifie diviser une chaîne en fonction d’un motif.
cette option est très pratique lorsque vous devez diviser une liste de chaînes homogènes en certains éléments ou sous-chaînes., La complication est que le texte source ne peut pas être divisé à chaque occurrence d’un délimiteur donné, seulement à certaines occurrences spécifiques. L’exemple suivant rendra les choses plus faciles à comprendre.
supposons que vous ayez une liste de chaînes extraites d’un fichier journal:
ce que vous voulez, c’est avoir la date et l’heure, le cas échéant, le code d’erreur et les détails de l’exception dans 3 colonnes distinctes., Vous ne pouvez pas utiliser un espace comme délimiteur car il y a des espaces entre la date et l’heure, qui doivent apparaître dans une colonne, et il y a des espaces dans le texte d’exception, qui doivent également apparaître dans une colonne.
la solution consiste à diviser une chaîne par le masque suivant: *ERROR:*Exception:*
où l’astérisque (*) représente un nombre quelconque de caractères.
Les deux-points (:) sont inclus dans les délimiteurs car nous ne voulons pas qu’ils apparaissent dans les cellules résultantes.,
et maintenant, développez la section diviser par masque dans le volet Texte divisé, tapez le masque dans la zone Entrée délimiteurs, et cliquez sur Diviser:
le résultat ressemblera à ceci:
Un grand avantage de cette méthode est la flexibilité.,vous voulez qu’ils apparaissent dans différentes colonnes, utilisez ce masque:
* *ERROR:*Exception:*
traduit en anglais simple, le masque demande au complément de diviser les chaînes d’origine en 4 parties:
- tous les caractères avant le 1er espace trouvé dans la chaîne (date)
- caractères entre le 1er espace et le mot ERROR: (time)
- texte entre ERROR: Et Exception: (code d’erreur)
- tout ce qui vient après exception: (texte d’exception)
j’espère que vous avez aimé cette façon rapide et simple de diviser des chaînes dans Excel., Si vous êtes curieux de l’essayer, une version d’évaluation est disponible en téléchargement ci-dessous. Je vous remercie pour la lecture et j’espère vous voir sur notre blog la semaine prochaine!
téléchargements
Excel Diviser des Cellules, les formules (.xlsx fichier)
Ultimate Suite 14 jours version entièrement fonctionnelle (.,fichier zip)
- Comment fractionner des cellules dans Excel
- Comment faire pour séparer les noms dans Excel: split nom et prénom dans les différentes colonnes
- Comment diviser date et heure dans Excel
- Comment faire pour fusionner deux colonnes dans Excel sans perdre les données