Tutoriel Excel : diviser les cellules de texte, de nombre et de date (séparer en plusieurs colonnes)

Lorsque vous utilisez Excel, vous devrez peut-être diviser le texte d'une cellule en plusieurs cellules à certaines fins. Ce didacticiel est divisé en trois parties : les cellules de texte fractionnées, les cellules de nombre fractionnées et les cellules de date fractionnées. Chaque partie fournit des exemples différents pour vous aider à savoir comment gérer le travail de fractionnement lorsque vous rencontrez le même problème.

---

1 Fractionner les cellules de texte

Cette partie rassemble les situations que vous rencontrerez lors de la division d'une cellule de texte en plusieurs colonnes et fournit les méthodes correspondantes pour vous.

Exemple #1 Fractionner les cellules par virgule, espace ou autre(s) délimiteur(s)

Pour diviser une cellule de texte en plusieurs colonnes par un délimiteur spécifique tel que virgule, espace, tiret, etc., vous pouvez appliquer l'une des méthodes ci-dessous.

Fractionner les cellules par délimiteur avec la fonctionnalité Texte en colonnes

La Texte aux colonnes La fonctionnalité, en tant qu'élément intégré d'Excel, est fréquemment utilisée pour fractionner des cellules. Comme le montre la capture d'écran ci-dessous, pour diviser les cellules de la colonne Chaînes de texte par virgule, vous pouvez appliquer la fonctionnalité Texte aux colonnes comme suit.

1. Sélectionnez la plage de colonnes que vous souhaitez diviser par virgule, cliquez sur Données > Texte aux colonnes.

2. dans le Assistant de conversion de texte en colonnes - Étape 1 sur 3 boîte de dialogue, conservez le Délimité bouton radio sélectionné, puis cliquez sur le Suivant .

3. dans le Assistant de conversion de texte en colonnes - Étape 2 sur 3 boîte de dialogue, spécifiez un délimiteur en fonction de vos besoins (dans ce cas, je vérifie uniquement le Virgule case à cocher), puis cliquez sur la Suivant .

4. Dans la boîte de dialogue de la dernière étape, cliquez sur le bouton pour sélectionner une cellule pour sortir les textes séparés, puis cliquez sur le Finition .

Ensuite, les textes de la plage sélectionnée sont séparés par une virgule et placés dans différentes colonnes, comme indiqué ci-dessous.

Fractionner les cellules par délimiteur avec formule

Vous pouvez appliquer la formule ci-dessous pour diviser les textes dans les cellules par un délimiteur spécifié dans Excel.

Formule générique

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Arguments

Ensuite, allez-y pour appliquer cette formule.

1. Comme le montre la capture d'écran ci-dessous, vous devez d'abord créer une ligne d'aide avec le numéro 1, 2, 3... situé dans différentes cellules.

Notes: Ici, 1, 2, 3... représentent la première, la deuxième et la troisième sous-chaîne de la chaîne de texte.

2. Sélectionnez une cellule sous la cellule numéro 1, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer key pour obtenir la première sous-chaîne de la chaîne de texte. Sélectionnez la cellule de résultat, faites glisser son Poignée de remplissage automatique droite et bas pour obtenir les autres sous-chaînes. Voir capture d'écran :

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Notes: Dans la formule, "," est le délimiteur utilisé pour diviser la chaîne de texte en B5. Vous pouvez les modifier selon vos besoins.

Fractionner les cellules par délimiteur avec un outil incroyable

Recommander ici le Kutools for Excel's Cellules divisés fonctionnalité pour vous aider à diviser facilement les cellules en colonnes ou en lignes séparées par un certain délimiteur dans Excel.

1. Après l'installation Kutools for Excel, sélectionnez la plage dans laquelle vous souhaitez diviser les chaînes de texte, puis cliquez sur Kutools > Fusionner et fractionner > Cellules divisés.

2. dans le Cellules divisés boîte de dialogue, vous devez configurer comme suit.

3. Dans le prochain Cellules divisés boîte de dialogue, sélectionnez une cellule pour sortir les sous-chaînes, puis cliquez sur OK.

Ensuite, les sous-chaînes sont divisées en différentes colonnes ou lignes en fonction de ce que vous avez spécifié à l'étape 2 ci-dessus.

Diviser en colonnes:

Diviser en lignes:

Exemple #2 Fractionner les cellules par une certaine longueur

Pour diviser la chaîne de texte par une certaine longueur, les méthodes suivantes peuvent vous rendre service.

Fractionner les cellules d'une certaine longueur avec la fonction Texte en colonnes

La Texte aux colonnes fonction fournit un Largeur fixe option pour vous aider à diviser les chaînes de texte dans les cellules sélectionnées par une certaine longueur dans Excel.

Comme le montre la capture d'écran ci-dessous, pour diviser les textes de la plage B5:B9 en colonnes tous les 3 caractères, vous pouvez procéder comme suit pour le faire.

1. Sélectionnez les cellules dans lesquelles vous souhaitez diviser les chaînes de texte.

2. Cliquez Données > Texte aux colonnes.

3. dans le Assistant de conversion de texte en colonnes - étape 1 sur 3 boîte de dialogue, sélectionnez le Largeur fixe bouton radio et cliquez Suivant.

4. Puis le Etape 2 de 3 la boîte de dialogue apparaît. dans le Aperçu des données section, cliquez à la position souhaitée sur l'axe pour créer une ligne de rupture (une ligne avec une flèche). Après avoir créé toutes les lignes de rupture, cliquez sur le bouton Suivant bouton pour continuer.

Dans ce cas, je crée des lignes de rupture tous les 3 caractères de la chaîne de texte.

5. Dans la dernière étape de l'assistant, sélectionnez une cellule pour sortir les textes séparés, puis cliquez sur le bouton Finition .

Maintenant, les chaînes de texte dans les cellules sélectionnées sont divisées tous les 3 caractères, comme indiqué dans la capture d'écran ci-dessous.

Divisez les cellules par une certaine longueur avec un outil incroyable

Pour diviser les cellules par une certaine longueur dans Excel, vous pouvez appliquer le Cellules divisés caractéristique de Kutools for Excel pour le faire facilement.

1. Sélectionnez les cellules de chaîne de texte que vous souhaitez diviser par une certaine longueur, cliquez sur Kutools > Fusionner et fractionner > Cellules divisés.

2. dans le Cellules divisés boîte de dialogue, vous devez configurer comme suit.

3. Dans le prochain Cellules divisés boîte de dialogue, sélectionnez une cellule pour placer les textes de fractionnement, puis cliquez sur OK.

Ensuite, les chaînes de texte dans les cellules sélectionnées sont divisées par une certaine longueur et placées dans différentes colonnes.

Exemple #3 Diviser des cellules par un certain mot

Comme le montre la capture d'écran ci-dessous, pour diviser les chaînes de texte dans la plage B5:B9 par un mot entier "ventes", vous pouvez appliquer les formules fournies dans cette section.

Obtenir la sous-chaîne avant un certain mot dans une cellule

L'application d'une formule basée sur les fonctions LEFT et FIND peut aider à obtenir la sous-chaîne avant un certain mot dans une chaîne de texte.

Formule générique

=LEFT(A1,FIND("certain_word", A1)-1)

Arguments

1. Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer key pour obtenir la sous-chaîne avant le certain mot. Sélectionnez cette cellule de résultat, puis faites glisser son Poignée de remplissage automatique vers le bas pour appliquer la formule à d'autres cellules.

=LEFT(B5,FIND("sales",B5)-1)

Obtenir la sous-chaîne après un mot dans une cellule

Après avoir obtenu la sous-chaîne avant le mot, vous devez appliquer la formule ci-dessous pour obtenir la sous-chaîne après.

Formule générique

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Arguments

1. Sélectionnez une cellule vide à côté de la première cellule de résultat (D5).

2. Copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat, faites glisser son Poignée de remplissage automatique vers le bas pour obtenir les autres résultats.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Vous avez maintenant divisé les chaînes de texte en différentes colonnes par un mot entier.

Exemple #4 Fractionner les cellules par saut de ligne

Cette section présente différentes méthodes pour vous aider à diviser les cellules de texte par saut de ligne dans Excel.

Fractionner les cellules par saut de ligne avec la fonctionnalité Texte en colonnes

La fonctionnalité Texte en colonnes peut être appliquée pour diviser les cellules par saut de ligne dans Excel. Vous pouvez procéder comme suit.

1. Sélectionnez la plage de cellules dans laquelle vous souhaitez diviser les textes par saut de ligne.

2. Cliquez Données > Texte aux colonnes.

3. dans le Assistant de conversion de texte en colonnes - Étape 1 sur 3 boîte de dialogue, choisissez le Délimité bouton radio, puis cliquez sur Suivant;

4. dans le Etape 2 de 3 boîte de dialogue, décochez tout Delimiters sélections, vérifiez les Autre case à cocher, puis appuyez sur la Ctrl + J raccourci. Vous pouvez voir que seul un petit point est affiché dans la zone de texte, puis dans le Aperçu des données case, les textes sont séparés par des sauts de ligne. Clique le Suivant .

5. Dans la dernière étape de l'assistant, sélectionnez une cellule de destination pour sortir les textes séparés, puis cliquez sur le bouton Finition .

Ensuite, les textes dans les cellules sélectionnées sont divisés en différentes colonnes par des sauts de ligne.

Fractionner les cellules par saut de ligne avec la formule

Les formules suivantes peuvent également aider à diviser les cellules par saut de ligne dans Excel.

En utilisant le même exemple que ci-dessus, après la division, vous obtiendrez trois sous-chaînes dans différentes colonnes.

Obtenir la sous-chaîne avant le premier saut de ligne

Premièrement, nous pouvons appliquer une formule basée sur la en stock et par Rechercher fonctions pour diviser la sous-chaîne avant le premier saut de ligne dans la cellule.

Formule générique

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Arguments

1. Sélectionnez une cellule vide (D5 dans ce cas), copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser son Poignée de remplissage automatique vers le bas pour obtenir les sous-chaînes avant le premier saut de ligne des autres cellules.

=GAUCHE(B5, RECHERCHE(CHAR(10),B5,1)-1)

Obtenez la sous-chaîne entre les première et deuxième sauts de ligne

Pour obtenir la sous-chaîne entre les sauts de première et de deuxième ligne dans une cellule, la formule suivante peut vous rendre service.

Formule générique

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Arguments

1. Sélectionnez une cellule (E5) à côté de D5, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser son Poignée de remplissage automatique vers le bas pour obtenir les sous-chaînes entre les sauts de première et de deuxième ligne des autres cellules.

=MID(B5, RECHERCHE(CHAR(10),B5) + 1, RECHERCHE(CHAR(10),B5,RECHERCHE(CHAR(10),B5)+1) - RECHERCHE(CHAR(10),B5) - 1 )

Obtenir la sous-chaîne après le deuxième saut de ligne

La première étape consiste à obtenir la sous-chaîne après le deuxième saut de ligne avec la formule ci-dessous.

Formule générique

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) + 1))

Arguments

1. Sélectionnez une cellule (F5 dans ce cas), copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les sous-chaînes après le deuxième saut de ligne des autres cellules.

=DROITE(B5,LEN(B5) - RECHERCHE(CHAR(10), B5, RECHERCHE(CHAR(10), B5) + 1))

Fractionner les cellules par saut de ligne avec VBA

Cette section fournit un code VBA pour vous aider à diviser facilement les textes dans les cellules sélectionnées par saut de ligne dans Excel.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module. Copiez ensuite le VBA ci-dessous dans la fenêtre Code.

Code VBA : diviser les cellules par saut de ligne dans Excel

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. appuie sur le F5 clé pour exécuter le code. Puis un Kutools for Excel boîte de dialogue apparaît, vous devez sélectionner la plage de cellules où vous souhaitez diviser par saut de ligne, et enfin cliquer sur le OK .

Ensuite, les textes dans les cellules sélectionnées sont divisés en différentes colonnes à la fois par des sauts de ligne.

Divisez les cellules par saut de ligne avec un outil incroyable

Voici un outil pratique - Cellules divisés utilité de Kutools for Excel. Avec cet outil, vous pouvez diviser les textes dans les cellules sélectionnées en bloc par saut de ligne en quelques clics seulement.

1. Sélectionnez la plage de cellules dans laquelle vous souhaitez diviser les textes par saut de ligne.

2. Cliquez Kutools > Fusionner et fractionner > Cellules divisés pour activer la fonctionnalité.

3. dans le Cellules divisés boîte de dialogue, vous devez effectuer les réglages suivants.

4. Dans le prochain pop-up Cellules divisés boîte de dialogue, sélectionnez une cellule vide pour placer les textes séparés et cliquez sur OK.

Ensuite, les textes dans les cellules sélectionnées sont divisés en différentes colonnes par des sauts de ligne, comme indiqué dans la capture d'écran ci-dessous.

Exemple #5 Fractionner les cellules par le premier ou le dernier délimiteur uniquement

Parfois, vous devrez peut-être diviser une cellule en deux parties en vous référant uniquement au premier ou au dernier délimiteur, vous pouvez essayer les formules ci-dessous.

Fractionner les cellules par le premier délimiteur avec des formules

Comme le montre la capture d'écran ci-dessous, pour diviser chaque cellule de la plage B5:B9 en deux parties par le premier espace, vous devez appliquer deux formules.

Diviser la sous-chaîne avant le premier espace

Pour diviser la sous-chaîne avant le premier espace, vous pouvez utiliser une formule basée sur la fonction GAUCHE et les fonctions FIND.

Formule générique

=LEFT(cell,FIND("delimiter",cell)-1)

Arguments

1. Sélectionnez une cellule (D5 dans ce cas) pour sortir la sous-chaîne, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites-la glisser Poignée de remplissage automatique vers le bas pour obtenir la sous-chaîne des autres cellules.

=LEFT(B5,FIND(" ",B5)-1)

Diviser la sous-chaîne après le premier espace

Appliquez ensuite la formule ci-dessous pour obtenir la sous-chaîne après le premier espace dans une cellule.

Formule générique

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Arguments

1. Copiez ou entrez la formule ci-dessous dans la cellule E5 et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser son Poignée de remplissage automatique vers le bas pour obtenir la sous-chaîne après le dernier espace des autres cellules.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Notes: Dans les formules, vous pouvez modifier la référence de cellule à la chaîne de texte et le délimiteur en fonction de vos besoins.

Fractionner les cellules par le dernier délimiteur avec des formules

Pour diviser les textes de la plage de cellules (B5:B9) en deux parties par le dernier espace, comme indiqué dans la capture d'écran, les deux formules fournies dans cette section peuvent vous aider à le faire.

Obtenez le texte à gauche du dernier délimiteur

Pour obtenir le texte à gauche du dernier délimiteur dans une cellule, vous pouvez appliquer la formule ci-dessous.

Formule générique

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Arguments

1. Sélectionnez une cellule pour y placer la sous-chaîne de gauche, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser son Poignée de remplissage automatique vers le bas pour appliquer la formule à d'autres cellules.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Obtenez le texte à droite du dernier délimiteur

Nous devons maintenant placer le texte à droite du dernier espace dans ce cas.

Formule générique

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Arguments

1. Sélectionnez une cellule pour y placer la bonne sous-chaîne, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour appliquer la formule à d'autres cellules.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Notes: Dans les formules, vous pouvez modifier la référence de cellule à la chaîne de texte et le délimiteur en fonction de vos besoins.

Exemple #6 Fractionner les cellules par majuscule

Cette section présente une fonction définie par l'utilisateur pour diviser les mots dans les cellules par majuscule.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le code VBA ci-dessous dans la fenêtre Code.

Code VBA : Fractionner les cellules par majuscule dans Excel

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. appuie sur le autre + Q touches pour fermer le Microsoft Visual Basic pour applications fenêtre.

4. Sélectionnez une cellule (D5 dans ce cas) pour afficher le mot à gauche de la première lettre majuscule, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites-la glisser Poignée de remplissage automatique puis vers le bas pour obtenir les autres mots.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Notes: Dans le code, $B5 est la cellule que vous allez diviser, $D:D est la colonne de la cellule de résultat. Veuillez les modifier en fonction de vos propres données.

Exemple #7 Diviser les noms dans les cellules

Supposons que vous ayez une feuille de calcul contenant une colonne de noms complets et que vous souhaitiez diviser le nom complet en colonnes distinctes, telles que séparer le prénom et le nom du nom complet, ou séparer le prénom, le deuxième prénom ou le nom du nom complet. Cette section répertorie les étapes détaillées pour vous aider à résoudre ces problèmes.

Diviser le nom complet en prénom et nom

Comme le montre la capture d'écran ci-dessous, le prénom, le deuxième prénom et le nom de famille sont séparés par un seul espace, pour séparer uniquement le prénom et le nom d'un nom complet et les mettre dans des colonnes différentes, vous pouvez appliquer l'un des méthodes suivantes.

1) Divisez le nom complet en prénom et nom avec des formules

Vous devez appliquer deux formules séparément pour diviser un nom complet en prénom et nom de famille.

Séparer le prénom d'un nom complet

Vous pouvez appliquer une formule basée sur les fonctions GAUCHE et RECHERCHE pour séparer le prénom d'un nom complet.

Formule générique

=LEFT(cell, SEARCH(" ", cell) - 1)

1. Sélectionnez une cellule pour sortir le prénom.

2. Copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites-la glisser Poignée de remplissage automatique vers le bas pour séparer les prénoms des autres noms complets.

=LEFT(B5, SEARCH(" ", B5) - 1)

Maintenant que vous avez séparé tous les prénoms des noms complets dans une plage de cellules spécifiée, continuez d'appliquer la formule ci-dessous pour séparer les noms de famille.

Séparer le nom de famille d'un nom complet

Formule générique

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. Sélectionnez une cellule à côté de la cellule du prénom.

2. Copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les noms de famille d'autres noms complets.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Notes: dans les formules, B5 est la cellule du nom complet que vous souhaitez diviser. Vous pouvez le modifier selon vos besoins.

2) Divisez le nom complet en prénom et nom avec un outil incroyable

La formule est difficile à retenir pour de nombreux utilisateurs d'Excel. Recommandez ici le Noms divisés caractéristique de Kutools for Excel. Avec cette fonctionnalité, vous pouvez facilement diviser le nom complet en prénom et nom en quelques clics seulement.

1. Sélectionnez la plage de cellules de nom complet, cliquez sur Kutools > Fusionner et fractionner > Noms divisés.

2. dans le Noms divisés boîte de dialogue, cochez uniquement Prénom et par Nom de famille boîte dans la Types de fractionnement section, puis cliquez sur OK.

Pourboires: La plage sélectionnée est affichée dans le Plage à diviser boîte, vous pouvez la modifier au besoin.

3. Puis un autre Noms divisés boîte de dialogue apparaît, sélectionnez une cellule de destination et cliquez sur OK.

Ensuite, le nom complet dans les cellules sélectionnées sera divisé en prénom et nom de famille en vrac, comme indiqué dans la capture d'écran ci-dessous.

Diviser le nom complet en prénom, deuxième prénom et nom de famille

Comme le montre la capture d'écran ci-dessous, il existe une colonne de nom complet avec le prénom, le deuxième prénom et le nom de famille séparés par un seul espace. Pour diviser le nom complet en prénom, deuxième prénom et nom de famille et les mettre dans différentes colonnes, les méthodes ci-dessous peuvent vous aider.

1) Divisez le nom complet en prénom, deuxième prénom et nom de famille avec Text to Columns

La fonctionnalité intégrée - Text to Columns peut vous aider à diviser facilement le nom complet en prénom, deuxième prénom et nom de famille dans Excel.

Vous pouvez suivez les étapes mentionnées ci-dessus pour appliquer la fonctionnalité Texte aux colonnes.

Notes: Dans l'assistant de l'étape 2 sur 3, cochez uniquement les Space boîte.

2) Divisez le nom complet en prénom, deuxième prénom et nom de famille avec des formules

Vous pouvez également appliquer des formules pour diviser un nom complet en prénom, deuxième prénom et nom de famille dans Excel.

Formules génériques utilisées pour diviser le nom complet

Diviser le prénom

=LEFT(cell,SEARCH(" ", cell)-1)

Diviser le deuxième prénom

=MID(cell, SEARCH(" ", cell) + 1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Diviser le nom de famille

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Appliquez ensuite les formules dans différentes cellules pour obtenir le prénom, le deuxième prénom et le nom de famille.

1. Dans la cellule D5, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le prénom des autres noms complets.

=LEFT(B5,SEARCH(" ", B5)-1)

2. Entrez la formule ci-dessous dans la cellule E5 et appuyez sur le bouton Entrer touche pour obtenir le deuxième prénom. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les autres deuxièmes prénoms.

=MID(B5, SEARCH(" ", B5) + 1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. Pour obtenir les noms de famille, entrez la formule ci-dessous dans la cellule F5 et appuyez sur Entrer, puis sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique sur les cellules dont vous avez besoin.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Divisez le nom complet en prénom, deuxième prénom et nom de famille avec un outil incroyable

Voici une introduction à Kutools for Excel's Noms divisés fonction, qui vous permet de diviser un nom complet en prénom, deuxième prénom et nom de famille à la fois en quelques clics.

1. Sélectionnez les cellules de nom complet où vous souhaitez diviser, puis cliquez sur Kutools > Fusionner et fractionner > Noms divisés.

2. dans le Noms divisés boîte de dialogue, vous devez configurer comme suit.

3. Dans le prochain pop-up Noms divisés boîte de dialogue, sélectionnez une cellule de destination pour sortir les textes séparés, puis cliquez sur OK. Voir la démo ci-dessous.

Exemple #8 Fractionner le texte et les nombres dans les cellules

En supposant qu'il existe une liste de chaînes de texte avec des textes et des nombres mélangés, comme indiqué dans la capture d'écran ci-dessous, pour séparer le texte et les nombres et les placer dans différentes colonnes, nous vous proposons quatre méthodes.

Fractionner le texte et les nombres avec des formules

Avec les formules suivantes, vous pouvez diviser le texte et les nombres d'une cellule en deux cellules séparées.

Formules génériques

Obtenir du texte de la cellule

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Obtenir des nombres de la cellule

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Appliquez ensuite ces deux formules dans différentes cellules pour obtenir le texte et les nombres séparés.

1. Sélectionnez une cellule vide pour placer le texte de la première chaîne de texte dans la liste, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le texte des autres chaînes de texte de la liste.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. Sélectionnez une cellule (E5) à côté de la première cellule de résultat (D5), copiez ou entrez la formule ci-dessous et appuyez sur Entrer. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les numéros des autres chaînes de texte dans la liste.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Fractionner le texte et les nombres avec Flash Fill (2013 et versions ultérieures)

Si vous utilisez Excel 2013 ou les versions ultérieures, vous pouvez appliquer le Remplissage Flash intégré pour diviser le texte et les nombres d'une cellule à deux colonnes.

Notes: Pour que la fonction Flash Fill fonctionne, vous devez localiser les colonnes de résultats à côté de la colonne de chaîne de texte d'origine. Par exemple, si les chaînes de texte d'origine se trouvent dans la colonne B, le texte et les chiffres séparés doivent être placés dans les colonnes C et D. Voir capture d'écran :

1. Tapez manuellement le texte de la première cellule de chaîne de texte (D5) dans la cellule C5.

2. Continuez à taper le texte de la deuxième cellule de chaîne de texte (D6) dans la cellule C6.

3. Activez la cellule C7, cliquez sur Données > Remplissage Flash.

Ensuite, les textes des autres chaînes de texte sont remplis automatiquement dans les cellules, comme indiqué dans la capture d'écran ci-dessous.

4. Répétez les étapes 1 à 3 pour obtenir les nombres de la colonne D.

Notes:

Fractionner le texte et les nombres avec la fonction définie par l'utilisateur

Si les chaînes de texte d'une liste ne sont pas régulières, les deux méthodes ci-dessus peuvent renvoyer des résultats erronés, comme indiqué dans la capture d'écran ci-dessous.

Présentez ici une fonction définie par l'utilisateur pour vous aider à diviser le texte et les nombres d'une cellule en deux colonnes, peu importe où les nombres se trouvent dans la chaîne de texte. Voir capture d'écran :

1. appuie sur le autre + F11 clés.

2. Dans l'ouverture Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le VBA ci-dessous dans la fenêtre Code.

Code VBA : diviser le texte et les nombres d'une cellule en deux colonnes

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. appuie sur le autre + Q touches pour fermer le Microsoft Visual Basic pour applications fenêtre.

4. Sélectionnez une cellule pour afficher le texte de la première chaîne de texte, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le texte d'autres chaînes de texte dans la même liste.

=SplitText(B5,FALSE)

5. Sélectionnez une cellule à côté de la première cellule de résultat de texte pour afficher les nombres, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les numéros des autres chaînes de texte.

=SplitText(B5,TRUE)

Divisez le texte et les nombres avec un outil incroyable

Voici le moyen le plus simple de diviser le texte et les nombres à la fois d'une cellule en deux colonnes. Appliquer leCellules divisés caractéristique de Kutools for Excel vous aide à gérer ce problème en quelques clics seulement.

1. Sélectionnez les cellules de chaîne de texte dans lesquelles vous souhaitez diviser le texte et les nombres en deux colonnes.

2. Cliquez Kutools > Fusionner et fractionner > Cellules divisés.

3. dans le Cellules divisés boîte de dialogue, vous devez effectuer les réglages suivants.

4. Dans le prochain Cellules divisés boîte de dialogue, sélectionnez une cellule pour afficher le texte et les nombres séparés, puis cliquez sur le bouton OK .

Ensuite, vous pouvez voir que le texte et les nombres dans les cellules sélectionnées sont divisés en deux colonnes à la fois, comme indiqué dans la capture d'écran ci-dessous.

---

2. Fractionner les cellules numériques

Cette partie décrit comment diviser des cellules numériques dans deux situations : diviser un nombre avec plus d'un chiffre en chiffres individuels et diviser un nombre au niveau décimal pour séparer les parties entières et décimales.

Exemple n°1 : diviser les cellules par des chiffres

Si vous souhaitez diviser un nombre avec plusieurs chiffres en chiffres individuels dans différentes colonnes, essayez l'une des méthodes ci-dessous.

Diviser un nombre dans une cellule en chiffres individuels avec une formule

La formule ci-dessous peut aider à diviser un nombre en chiffres individuels et à les placer dans différentes colonnes.

Formule générique

=MID($A1, COLUMNS($A$1:A$1), 1)

Argument

1. Sélectionnez une cellule vide pour sortir le premier chiffre, entrez la formule ci-dessous et appuyez sur le Entrer clé.

=MID($B3, COLUMNS($B$3:B$3), 1)

2. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers les cellules pour obtenir d'autres chiffres. Conservez toutes les cellules de résultat sélectionnées, puis faites glisser la poignée de remplissage automatique vers le bas pour obtenir les chiffres individuels des autres nombres.

Notes: Dans cette formule, B3 est la cellule contenant le nombre à diviser en chiffres individuels, et vous pouvez le modifier si nécessaire.

Diviser un nombre dans une cellule en chiffres individuels avec VBA

Le code VBA ci-dessous peut également aider à diviser un nombre dans une cellule en chiffres individuels dans Excel. Vous pouvez procéder comme suit.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le VBA ci-dessous dans la fenêtre Code.

Code VBA : diviser le nombre en chiffres individuels dans Excel

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. appuie sur le F5 pour exécuter le code, puis un Kutools for Excel boîte de dialogue apparaît, vous devez sélectionner la plage de cellules numériques que vous allez diviser, puis cliquez sur le bouton OK .

4. Le deuxième Kutools for Excel boîte de dialogue apparaît, vous devez sélectionner une cellule pour afficher les chiffres individuels, puis cliquez sur OK.

Notes: Ce code peut également diviser un mot en lettres individuelles.

Ensuite, les nombres dans les cellules sélectionnées sont divisés en chiffres individuels et placés dans différentes colonnes.

Divisez facilement un nombre en chiffres individuels avec un outil incroyable

Kutools for Excel's Cellules divisés est un outil pratique pour vous aider à diviser facilement un nombre en chiffres individuels dans Excel.

1. Après l'installation Kutools for Excel, sélectionnez la plage de cellules numériques que vous allez diviser, cliquez sur Kutools > Fusionner et fractionner > Cellules divisés.

2. dans le Cellules divisés boîte de dialogue, effectuez les réglages suivants.

3. Dans l'ouverture Cellules divisés boîte de dialogue, sélectionnez une cellule vide pour afficher les chiffres, puis cliquez sur le bouton OK .

Ensuite, les nombres dans les cellules sélectionnées sont divisés en chiffres individuels à la fois.

Exemple #2 Fractionner le nombre à la virgule

Cette section décrit quelques méthodes pour diviser un nombre en parties entières et décimales dans Excel.

Fractionner le nombre à la décimale avec des formules

La fonction TRUNC peut être appliquée pour diviser un nombre en décimales dans Excel. Vous pouvez procéder comme suit.

Formules génériques

Obtenir la partie entière

=TRUNC(A1)

Obtenir la partie décimale

=A1-TRUNC(A1)

Argument

Vous pouvez maintenant appliquer ces deux formules pour diviser des nombres dans une plage de cellules spécifiée en décimales dans Excel.

1. Sélectionnez une cellule pour placer la partie entière de la première cellule numérique, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les nombres entiers des autres cellules numériques.

=TRONÇON(B5)

2. Sélectionnez une cellule à côté de la première cellule de résultat pour placer la partie décimale, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les décimales des autres cellules numériques.

=B5-TRONÇON(B5)

Diviser le nombre en décimales avec du texte en colonnes

Vous pouvez appliquer le Texte aux colonnes fonctionnalité pour diviser le nombre en décimales dans Excel.

1. Sélectionnez la plage de cellules numériques que vous diviserez en décimales, puis cliquez sur Données > Texte aux colonnes.

2. dans le Assistant de conversion de texte en colonnes - Étape 1 sur 3 boîte de dialogue, sélectionnez le Délimité bouton radio et cliquez sur le Suivant .

3. dans le Etape 2 de 3 boîte de dialogue, cochez uniquement Autre case à cocher, entrez un point dans la zone de texte, puis cliquez sur le Suivant .

4. dans le Etape 3 de 3 boîte de dialogue, cliquez sur le bouton pour sélectionner une cellule de destination pour sortir les nombres entiers et décimaux fractionnés, et enfin cliquez sur le Finition bouton. Voir la capture d'écran:

Ensuite, vous pouvez voir que les parties entières et décimales sont séparées des cellules sélectionnées, comme indiqué dans la capture d'écran ci-dessous. Cependant, la partie décimale perd son signe négatif qui pourrait être nécessaire pour l'utilisateur.

Fractionner le nombre à la décimale avec Flash Fill (2013 et versions ultérieures)

Dans l'exemple ci-dessus, nous avons introduit l'utilisation de la Remplissage Flash fonction pour diviser le texte et les nombres dans les cellules, et ici nous allons introduire la même méthode pour diviser le nombre en décimales.

1. Entrez quelques exemples. Dans ce cas, on saisit la partie entière de B5 dans C5, on saisit la partie entière de B6 dans C6. Voir capture d'écran :

Notes: Pour les nombres négatifs, n'oubliez pas d'entrer le signe moins ensemble.

3. Sélectionnez les cellules, y compris les quelques exemples dans la colonne que vous souhaitez remplir, cliquez sur Données > Remplissage Flash.

Ensuite, les entiers sont extraits des cellules numériques spécifiées, comme indiqué dans la capture d'écran ci-dessous.

4. Répétez les étapes ci-dessus pour séparer les décimales des mêmes cellules numériques.

---

3. Séparer les cellules de date

Une autre situation fréquemment rencontrée consiste à diviser les cellules de date. Si vous devez diviser les cellules de date en jour, mois et année distincts, ou diviser les cellules de date en date et heure distinctes, utilisez la solution suivante en fonction de vos besoins.

Exemple #1 Diviser les cellules de date en jour, mois et année séparés

En supposant que vous ayez une liste de dates dans la plage B5:B9 et que vous souhaitiez diviser chaque valeur de date en trois colonnes distinctes pour le jour, le mois et l'année. Voici trois méthodes pour vous aider à obtenir le résultat du fractionnement des dates.

Divisez les cellules de date en jour, mois et année avec des formules

Vous pouvez appliquer trois formules basées sur le DATES fonction, MOIS fonction et ANNÉE fonction pour diviser une date en jour, mois et année séparés dans Excel.

Formules génériques

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Argument

1. Créez trois colonnes pour placer le jour, le mois et l'année séparément.

2. Sélectionnez la première cellule dans la colonne Jour, entrez la formule ci-dessous et appuyez sur le bouton Entrer pour obtenir le jour de la première cellule de date (B5). Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les jours des autres cellules de date.

=DAY(B5)

3. Faites la même opération qu'à l'étape 1 pour appliquer les formules suivantes dans le Mois et Année colonne pour obtenir le mois et l'année séparés à partir des cellules de date.

Obtenir le mois d'un rendez-vous

=MONTH(B5)

Obtenir l'année d'une date

=YEAR(B5)

Divisez les cellules de date en jour, mois et année avec Text to Columns

Vous pouvez suivez les étapes ci-dessus pour appliquer la fonctionnalité Texte aux colonnes pour diviser les cellules de date en jour, mois et année séparés dans Excel.

Notes: Dans le Etape 2 de 3 boîte de dialogue, cochez uniquement Autre case et tapez un / symbole dans la zone de texte.

Divisez les cellules de date en jour, mois et année avec un outil incroyable

La démo ci-dessous montre comment diviser la date en mois, jour et année séparés avec le Cellules divisés caractéristique de Kutools for Excel.

Cette fonctionnalité vous aide à obtenir le résultat de fractionner les dates en bloc en quelques clics seulement.

Cliquez pour en savoir plus sur cette fonctionnalité.

Exemple #2 Diviser la date et l'heure dans une cellule

Supposons qu'il existe une liste de dates et d'heures dans B5:B9 et que vous souhaitiez diviser les dates et les heures en colonnes distinctes, cette section vous montrera différentes manières de vous aider à le faire.

Diviser la date et l'heure dans une cellule avec formule

Vous pouvez appliquer les formules suivantes pour diviser la date et l'heure en différentes colonnes dans Excel.

1. Préparez deux colonnes pour placer les dates et les heures.

2. Vous devez définir les cellules de la colonne de date sur Date format et définissez les cellules de la colonne de temps sur Temps le format.

3. Sélectionnez la première cellule dans le Date colonne, entrez la formule ci-dessous et appuyez sur la Entrer clé pour obtenir la date de B5. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les autres dates.

=INT(B5)

4. Appliquez la formule suivante dans la colonne heure pour obtenir les heures dans B5:B9.

=B5-D5

Diviser la date et l'heure dans une cellule avec Flash Fill (2013 et versions ultérieures)

Si vous utilisez Excel 2013 et les versions ultérieures, vous pouvez appliquer la fonction Flash Fill intégrée pour diviser la date et l'heure en différentes colonnes.

1. Créez les colonnes Date et Heure et entrez quelques exemples que vous souhaitez en sortie. Dans le Date colonne, nous écrivons la date de B5 dans C5, et écrivons la date de B6 dans C6. Dans le Temps colonne, on saisit l'heure de B5 en D5 et on insère l'heure de B6 en D6. Voir capture d'écran :

2. Sélectionnez la colonne Date (y compris les quelques exemples) que vous souhaitez remplir, cliquez sur Données > Remplissage Flash.

3. Sélectionnez la colonne Temps (y compris les quelques exemples) que vous souhaitez remplir, puis activez le Remplissage Flash fonctionnalité aussi. Ensuite, les dates et heures dans B5:B9 sont divisées en colonnes séparées, comme indiqué dans la démo ci-dessous.

Maintenant, vous avez appris à diviser des cellules dans Excel avec différentes méthodes dans différentes situations. Vous serez en mesure de prendre la meilleure décision pour déterminer la méthode que vous choisirez en fonction de votre scénario spécifique.