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

Lors de l'utilisation d'Excel, vous pourriez avoir besoin de diviser le texte d'une cellule en plusieurs cellules pour diverses raisons. Ce tutoriel est divisé en trois parties : diviser les cellules de texte, diviser les cellules de nombres et diviser les cellules de dates. Chaque partie fournit différents exemples pour vous aider à comprendre comment gérer la tâche de division lorsque vous rencontrez le même problème.

1 Diviser les cellules de texte

Cette partie recense les situations que vous rencontrerez lorsque vous diviserez une cellule de texte en plusieurs colonnes et fournit les méthodes correspondantes pour vous aider.

Exemple n°1 Diviser les cellules par une virgule, un espace ou d'autres délimiteurs

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

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

La fonctionnalité Texte en colonnes, en tant que fonction intégrée d'Excel, est fréquemment utilisée pour diviser les cellules. Comme le montre la capture d'écran ci-dessous, pour diviser les cellules dans la colonne Text Strings par une virgule, vous pouvez appliquer la fonctionnalité Texte en colonnes comme suit.

1. Sélectionnez la plage de colonnes que vous souhaitez diviser par une virgule, puis cliquez sur Données > Convertir en colonnes.

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

3. Dans la boîte de dialogue Assistant de conversion de texte en colonnes – Étape 2 sur 3, spécifiez un délimiteur selon vos besoins (dans ce cas, je ne coche que la case Virgule), puis cliquez sur le bouton Suivant.

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

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

Diviser les cellules par délimiteur avec une formule

Vous pouvez appliquer la formule ci-dessous pour diviser les textes dans les cellules à l'aide d'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, passez à l'application de cette formule.

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

Remarque : Ici, 1, 2, 3... représentent respectivement le premier, le deuxième et le 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 dans celle-ci et appuyez sur la touche Entrée pour obtenir le premier sous-ensemble de la chaîne de texte. Sélectionnez la cellule résultat, faites glisser son Poignée de Recopie vers la droite et vers le bas pour obtenir les autres sous-ensembles. Voir capture d'écran :

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

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

Diviser les cellules par délimiteur avec un outil incroyable

Ici, nous recommandons la fonction Diviser les cellules de Kutools pour Excel pour vous aider à diviser facilement des cellules en colonnes ou lignes séparées selon un délimiteur spécifique dans Excel.

1. Après avoir installé Kutools pour Excel, sélectionnez la plage où vous souhaitez diviser les chaînes de texte, puis cliquez sur Kutools > Fusionner et Diviser > Diviser les cellules.

2. Dans la boîte de dialogue Diviser les cellules, vous devez configurer comme suit.

3. Dans la boîte de dialogue Diviser les cellules suivante, sélectionnez une cellule pour afficher les sous-chaînes, puis cliquez sur OK.

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

Diviser en colonnes :

Diviser en lignes :

Exemple n°2 Diviser les cellules par une longueur spécifique

Pour diviser une chaîne de texte selon une longueur spécifique, les méthodes suivantes peuvent vous être utiles.

Diviser les cellules par une longueur spécifique avec la fonctionnalité Texte en colonnes

La fonctionnalité Texte en colonnes propose une option Largeur fixe pour vous aider à diviser des chaînes de texte dans les cellules sélectionnées selon une longueur spécifique dans Excel.

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

1. Sélectionnez les cellules où vous souhaitez diviser les chaînes de texte.

2. Cliquez sur Données > Convertir en colonnes.

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

4. Ensuite, la boîte de dialogue Étape 2 sur 3 apparaît. Dans la section Aperçu des données, cliquez à l'endroit souhaité 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 pour continuer.

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

5. Dans l'étape finale de l'assistant, sélectionnez une cellule pour afficher les textes séparés, puis cliquez sur le bouton Terminer.

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

Diviser les cellules par une longueur spécifique avec un outil incroyable

Pour diviser des cellules par une longueur spécifique dans Excel, vous pouvez utiliser la fonction Diviser les cellules de Kutools pour Excel pour le faire facilement.

1. Sélectionnez les cellules de chaîne de texte que vous souhaitez diviser selon une longueur spécifique, puis cliquez sur Kutools > Fusionner et Diviser > Diviser les cellules.

2. Dans la boîte de dialogue Diviser les cellules, vous devez configurer comme suit.

3. Dans la boîte de dialogue Diviser les cellules suivante, sélectionnez une cellule pour placer les textes divisés, puis cliquez sur OK.

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

Exemple #3 Diviser les 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 « sales », vous pouvez appliquer les formules fournies dans cette section.

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

Appliquer une formule basée sur les fonctions GAUCHE et TROUVER peut aider à obtenir la sous-chaîne avant un mot spécifique dans une chaîne de texte.

Formule générique

=GAUCHE(A1,TROUVE("certain_word", A1)-1)

Arguments

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

=GAUCHE(B5,TROUVE("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 celui-ci.

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 saisissez la formule ci-dessous dans la cellule, puis appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat, faites glisser son Poignée de Recopie 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 Diviser les cellules par saut de ligne

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

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

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

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

2. Cliquez sur Données > Convertir en colonnes.

3. Dans la boîte de dialogue Assistant de conversion de texte en colonnes – Étape 1 sur 3, choisissez le bouton radio Délimiteur puis cliquez sur Suivant ;

4. Dans la boîte de dialogue Étape 2 sur 3, désactivez toute sélection existante dans Délimiteurs, cochez la case Autre, puis appuyez sur le raccourci Ctrl + J. Vous pouvez constater qu'un simple point est affiché dans la zone de texte, et ensuite, dans la zone Aperçu des données, les textes sont divisés par des sauts de ligne. Cliquez sur le bouton Suivant.

5. Dans l'étape finale de l'assistant, sélectionnez une cellule de destination pour afficher les textes séparés, puis cliquez sur le bouton Terminer.

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

Diviser les cellules par saut de ligne avec une 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 des colonnes différentes.

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

Tout d'abord, nous pouvons appliquer une formule basée sur les fonctions GAUCHE et RECHERCHE pour diviser la sous-chaîne avant le premier saut de ligne dans la cellule.

Formule générique

=GAUCHE(cellule; RECHERCHE(CAR(10);cellule;1)-1)

Arguments

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

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

Obtenir la sous-chaîne entre le premier et le deuxième sauts de ligne

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

Formule générique

=STXT(cellule;RECHERCHE(CAR(10);cellule)+1;RECHERCHE(CAR(10);cellule;RECHERCHE(CAR(10);cellule)+1)-RECHERCHE(CAR(10);cellule)-1)

Arguments

1. Sélectionnez une cellule (E5) à côté de D5, copiez ou saisissez la formule ci-dessous dans celle-ci, puis appuyez sur la touche Entrée. Sélectionnez la cellule de résultat et faites glisser son Poignée de Recopie automatique vers le bas pour obtenir les sous-chaînes entre les premiers et deuxièmes sauts de ligne des autres cellules.

=STXT(B5; RECHERCHE(CAR(10);B5) + 1; RECHERCHE(CAR(10);B5;RECHERCHE(CAR(10);B5)+1) - RECHERCHE(CAR(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

=DROITE(cellule;NBCAR(cellule) - RECHERCHE(CAR(10);cellule; RECHERCHE(CAR(10); cellule) + 1))

Arguments

1. Sélectionnez une cellule (F5 dans ce cas), copiez ou saisissez la formule ci-dessous dans celle-ci, puis appuyez sur la touche Entrée. Sélectionnez la cellule contenant le résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les sous-chaînes après le deuxième saut de ligne des autres cellules.

=DROITE(B5;NBCAR(B5) - RECHERCHE(CAR(10); B5; RECHERCHE(CAR(10); B5) + 1))

Diviser 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. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.

2. Dans la fenêtre Microsoft Visual Basic for Applications, cliquez sur Insert > Module. Ensuite, copiez le code 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. Appuyez sur la touche F5 pour exécuter le code. Ensuite, une boîte de dialogue Kutools for Excel apparaît, vous devez sélectionner la plage de cellules où vous souhaitez diviser par saut de ligne, et enfin cliquer sur le bouton OK.

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

Diviser les cellules par saut de ligne avec un outil incroyable

Ici, nous vous présentons un outil pratique – l'utilitaire Diviser les cellules de Kutools pour Excel. Avec cet outil, vous pouvez diviser en masse les textes dans les cellules sélectionnées par saut de ligne en seulement quelques clics.

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

2. Cliquez sur Kutools > Fusionner et Diviser > Diviser les cellules pour activer la fonctionnalité.

3. Dans la boîte de dialogue Diviser les cellules, vous devez effectuer les réglages suivants.

4. Dans la boîte de dialogue Split Cells qui apparaît ensuite, sélectionnez une cellule vide pour placer les textes séparés, puis 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 le montre la capture d'écran ci-dessous.

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

Parfois, vous pourriez avoir besoin de diviser une cellule en deux parties en utilisant uniquement le premier ou le dernier délimiteur. Vous pouvez essayer les formules ci-dessous.

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

Comme le montre la capture d'écran ci-dessous, pour diviser chaque cellule dans la plage B5:B9 en deux parties à partir du 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 les fonctions GAUCHE et TROUVER.

Formule générique

=GAUCHE(cellule;TROUVE("délimiteur";cellule)-1)

Arguments

1. Sélectionnez une cellule (D5 dans ce cas) pour afficher la sous-chaîne, copiez ou saisissez la formule ci-dessous dans celle-ci, puis appuyez sur la touche Entrée. Sélectionnez la cellule de résultat, puis faites glisser son Poignée de Recopie vers le bas pour obtenir la sous-chaîne des autres cellules.

=GAUCHE(B5,TROUVE(" ",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

=DROITE(cellule;NBCAR(cellule)-TROUVE("délimiteur";cellule))

Arguments

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

=DROITE(B5;NBCAR(B5)-TROUVE(" ";B5))

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

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

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

Obtenir 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

=GAUCHE(B5;RECHERCHE("^";SUBSTITUE(cellule;"délimiteur";"^";NBCAR(cellule)-NBCAR(SUBSTITUE(cellule;"délimiteur";""))))-1)

Arguments

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

=GAUCHE(B5;RECHERCHE("^";SUBSTITUE(B5;" ";"^";NBCAR(B5)-NBCAR(SUBSTITUE(B5;" ";""))))-1)

Obtenir le texte à droite du dernier délimiteur

Maintenant, nous devons extraire le texte à droite de la dernière espace dans ce cas.

Formule générique

=TRIM(DROITE(SUBSTITUE(cellule,"délimiteur",REPT("délimiteur",NBCAR(cellule))),NBCAR(cellule)))

Arguments

1. Sélectionnez une cellule pour placer la bonne sous-chaîne, copiez ou entrez la formule ci-dessous dans celle-ci, puis appuyez sur la touche Entrée. Sélectionnez la cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour appliquer la formule à d'autres cellules.

=TRIM(DROITE(SUBSTITUE(B5," ",REPT(" ",NBCAR(B5))),NBCAR(B5)))

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

Exemple #6 Diviser les cellules par lettre majuscule

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

1. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.

2. Dans la fenêtre Microsoft Visual Basic for Applications, cliquez sur Insert > Module, puis copiez le code VBA ci-dessous dans la fenêtre Code.

Code VBA : Diviser les cellules par lettre 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. Appuyez sur les touches Alt + Q pour fermer la fenêtre Microsoft Visual Basic for Applications.

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

=SIERREUR(ObtenirNom($B5,COLONNES($D:D)),"")

Remarque : 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 séparées, par exemple séparer le prénom et le nom de famille du nom complet, ou encore séparer le prénom, le deuxième prénom ou le nom de famille du nom complet. Cette section répertorie des étapes détaillées pour vous aider à résoudre ces problèmes.

Diviser le nom complet en prénom et nom de famille

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 ne diviser que le prénom et le nom de famille à partir d'un nom complet et les placer dans des colonnes différentes, vous pouvez appliquer l'une des méthodes suivantes.

1) Diviser le nom complet en prénom et nom de famille avec des formules

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

Diviser le prénom d'un nom complet

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

Formule générique

=GAUCHE(cellule, RECHERCHE(" ", cellule) - 1)

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

2. Copiez ou saisissez la formule ci-dessous dans la cellule, puis appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son Poignée de Recopie automatique vers le bas pour extraire les prénoms des autres noms complets.

=GAUCHE(B5, RECHERCHE(" ", B5) - 1)

Maintenant que vous avez divisé tous les prénoms à partir des noms complets dans la plage de cellules spécifiée, passez à l'étape suivante pour appliquer la formule ci-dessous afin de diviser les noms de famille.

Diviser le nom de famille à partir d'un nom complet

Formule générique

=DROITE(cellule, NBCAR(cellule) - RECHERCHE("^", SUBSTITUE(cellule," ", "^", NBCAR(cellule) - NBCAR(SUBSTITUE(cellule, " ", "")))))

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

2. Copiez ou saisissez la formule ci-dessous dans la cellule, puis appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les noms de famille à partir d'autres noms complets.

=DROITE(B5, NBCAR(B5) - RECHERCHE("^", SUBSTITUE(B5," ", "^", NBCAR(B5) - NBCAR(SUBSTITUE(B5, " ", "")))))

Remarque : Dans les formules, B5 est la cellule contenant le nom complet que vous souhaitez diviser. Vous pouvez la modifier selon vos besoins.

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

La formule est difficile à mémoriser pour de nombreux utilisateurs d'Excel. Ici, nous recommandons la fonction Diviser le nom de Kutools pour Excel. Avec cette fonction, vous pouvez facilement diviser un nom complet en prénom et nom de famille en seulement quelques clics.

1. Sélectionnez la plage de cellules contenant les noms complets, puis cliquez sur Kutools > Fusionner et diviser > Diviser le nom.

2. Dans la boîte de dialogue Diviser le nom, cochez uniquement les cases Prénom et Nom de famille dans la section Type de division, puis cliquez sur OK.

Astuce : La plage sélectionnée est affichée dans la zone Plage de division, vous pouvez la modifier selon vos besoins.

3. Ensuite, une autre boîte de dialogue Diviser le nom 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 masse, comme le montre 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 y a une colonne de noms complets 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 placer dans différentes colonnes, les méthodes ci-dessous peuvent vous aider.

1) Diviser le nom complet en prénom, deuxième prénom et nom de famille avec l'outil Convertir en colonnes

La fonctionnalité intégrée – Texte en colonnes – peut vous aider à diviser facilement un nom complet en prénom, deuxième prénom et nom de famille dans Excel.

Vous pouvez suivre les étapes mentionnées ci-dessus pour appliquer la fonctionnalité Diviser les données en colonnes.

Remarque : Dans l'étape 2 du wizard en 3 étapes, cochez uniquement la case Espace.

2) Diviser 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

=GAUCHE(cellule;RECHERCHE(" "; cellule)-1)

Diviser le deuxième prénom

=STXT(cellule, RECHERCHE(" ", cellule) + 1, RECHERCHE(" ", cellule, RECHERCHE(" ", cellule)+1) - RECHERCHE(" ", cellule)-1)

Diviser le nom de famille

=DROITE(cellule;NBCAR(cellule) - RECHERCHE(" ";cellule; RECHERCHE(" ";cellule;1)+1))

Ensuite, appliquez 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, saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir le prénom des autres noms complets.

=GAUCHE(B5;RECHERCHE(" "; B5)-1)

2. Saisissez la formule ci-dessous dans la cellule E5 et appuyez sur la touche Entrée pour obtenir le premier deuxième prénom. Sélectionnez la cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les autres deuxièmes prénoms.

=STXT(B5; RECHERCHE(" "; B5) + 1; RECHERCHE(" "; B5; RECHERCHE(" "; B5)+1) - RECHERCHE(" "; B5)-1)

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

=DROITE(B5;NBCAR(B5) - RECHERCHE(" ";B5; RECHERCHE(" ";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 à la fonctionnalité Diviser le nom de Kutools pour Excel, qui vous permet de diviser un nom complet en prénom, deuxième prénom et nom de famille en une seule fois avec seulement quelques clics.

1. Sélectionnez les cellules contenant les noms complets que vous souhaitez diviser, puis cliquez sur Kutools > Fusionner et Diviser > Diviser le nom.

2. Dans la boîte de dialogue Diviser le nom, vous devez configurer comme suit.

3. Dans la boîte de dialogue Split Names qui apparaît ensuite, sélectionnez une cellule de destination pour afficher les textes séparés, puis cliquez sur OK. Consultez la démonstration ci-dessous.

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

Supposons qu'il y ait une liste de chaînes de texte contenant à la fois du texte et des nombres, comme le montre la capture d'écran ci-dessous. Pour séparer le texte et les nombres et les placer dans des colonnes différentes, nous vous proposons quatre méthodes.

Diviser le texte et les nombres avec des formules

À l'aide des formules suivantes, vous pouvez séparer le texte et les nombres d'une cellule en deux cellules distinctes.

Formules génériques

Obtenir le texte de la cellule

=GAUCHE(cellule;MIN(TROUVE({0;1;2;3;4;5;6;7;8;9};cellule&"0123456789"))-1)

Extraire des nombres d'une cellule

=DROITE(cellule;NBCAR(B5)-MIN(TROUVE({0;1;2;3;4;5;6;7;8;9};cellule&"0123456789"))+1)

Ensuite, appliquez ces deux formules dans des cellules différentes pour obtenir le texte et les nombres séparément.

1. Sélectionnez une cellule vide pour placer le texte de la première chaîne de texte dans la liste, copiez ou saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez la cellule résultat et faites glisser son Poignée de Recopie automatique vers le bas pour obtenir le texte des autres chaînes de texte dans la liste.

=GAUCHE(B5,MIN(TROUVE({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 saisissez la formule ci-dessous et appuyez sur Entrée. Sélectionnez la cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les nombres d'autres chaînes de texte dans la liste.

=DROITE(B5;NBCAR(B5)-MIN(TROUVE({0;1;2;3;4;5;6;7;8;9};B5&"0123456789"))+1)

Diviser le texte et les nombres avec le remplissage instantané (versions 2013 et ultérieures)

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

Remarque : Pour que la fonction Remplissage instantané fonctionne, vous devez placer les colonnes de résultats à côté de la colonne contenant la 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 nombres séparés doivent être placés dans les colonnes C et D. Voir capture d'écran :

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

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

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

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

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

Remarques :

Diviser le texte et les nombres avec une fonction définie par l'utilisateur

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

Ici, nous introduisons 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 sont situés dans la chaîne de texte. Voir la capture d'écran :

1. Appuyez sur les touches Alt + F11.

2. Dans la fenêtre Microsoft Visual Basic for Applications qui s'ouvre, cliquez sur Insert > Module, puis copiez le code 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. Appuyez sur les touches Alt + Q pour fermer la fenêtre Microsoft Visual Basic for Applications.

4. Sélectionnez une cellule pour afficher le texte de la première chaîne de texte, saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir le texte des autres chaînes de texte dans la même liste.

=DiviserTexte(B5,FALSE)

5. Sélectionnez une cellule à côté de la première cellule de résultat texte pour afficher les nombres, saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les nombres des autres chaînes de texte.

=DiviserTexte(B5,VRAI)

Diviser le texte et les nombres avec un outil incroyable

Ici, nous recommandons la méthode la plus simple pour diviser le texte et les nombres en une seule fois depuis une cellule en deux colonnes. L'utilisation de la fonction Diviser les cellules de Kutools pour Excel vous aide à résoudre ce problème en seulement quelques clics.

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

2. Cliquez sur Kutools > Fusionner et Diviser > Diviser les cellules.

3. Dans la boîte de dialogue Diviser les cellules, vous devez effectuer les réglages suivants.

3.1) La plage sélectionnée est répertoriée dans le Plage de division boîte, et vous pouvez cliquer sur le bouton pour sélectionner une nouvelle plage selon les besoins ;

3.2) Dans la section Type, choisissez Diviser en lignes ou Diviser les données ;

3.3) Dans la section Diviser par, sélectionnez le bouton radio Texte et nombre ;

3.4) Cliquez sur le bouton OK.

4. Dans la boîte de dialogue Diviser les cellules suivante, sélectionnez une cellule pour afficher le texte et les nombres séparés, puis cliquez sur le bouton Terminer.

Vous pouvez alors voir que le texte et les nombres dans les cellules sélectionnées sont divisés en deux colonnes immédiatement, comme le montre la capture d'écran ci-dessous.

2. Diviser les cellules de nombres

Cette partie explique comment diviser les cellules de nombres dans deux situations : la division d'un nombre comportant plusieurs chiffres en chiffres individuels et la division d'un nombre à la décimale pour séparer les parties entière et décimale.

Exemple n°1 : Diviser les cellules par chiffres

Si vous souhaitez diviser un nombre comportant 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

=STXT($A1, COLONNES($A$1:A$1), 1)

Argument

1. Sélectionnez une cellule vide pour afficher le premier chiffre, saisissez la formule ci-dessous et appuyez sur la touche Entrée.

=STXT($B3, COLONNES($B$3:B$3), 1)

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

Remarque : Dans cette formule, B3 est la cellule contenant le nombre à diviser en chiffres individuels, et vous pouvez la modifier selon vos besoins.

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. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.

2. Dans la fenêtre Microsoft Visual Basic for Applications, cliquez sur Insert > Module, puis copiez le code VBA ci-dessous dans la fenêtre Code.

Code VBA : diviser un 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. Appuyez sur la touche F5 pour exécuter le code, puis une boîte de dialogue Kutools for Excel apparaît, vous devez sélectionner la plage de cellules de nombres que vous souhaitez diviser, puis cliquer sur le bouton OK.

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

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

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

Fractionnez facilement un nombre en chiffres individuels avec un outil étonnant

La fonction Diviser les cellules de Kutools pour Excel est un outil pratique pour vous aider à facilement diviser un nombre en chiffres individuels dans Excel.

1. Après avoir installé Kutools pour Excel, sélectionnez la plage de cellules de nombres que vous souhaitez diviser, puis cliquez sur Kutools > Fusionner et Diviser > Diviser les cellules.

2. Dans la boîte de dialogue Diviser les cellules, effectuez les réglages suivants.

2.1) Dans le Plage de division section, vous pouvez voir la plage que vous avez sélectionnée affichée dans la zone de texte. Vous pouvez cliquer sur le bouton pour modifier la plage selon vos besoins ;

2.2) Dans la section Type, choisissez Diviser en lignes ou Diviser les données selon vos besoins ;

2.3) Dans la section Diviser par, sélectionnez le bouton radio Spécifier la largeur, puis saisissez le nombre 1 dans la zone de texte ;

2.4) Cliquez sur le bouton OK.

3. Dans la boîte de dialogue Diviser les cellules qui s'ouvre, sélectionnez une cellule vide pour afficher les chiffres, puis cliquez sur le bouton Terminer.

Les nombres dans les cellules sélectionnées sont alors divisés en chiffres individuels instantanément.

Exemple n°2 Diviser le nombre à la décimale

Cette section présente quelques méthodes pour diviser un nombre en parties entière et décimale dans Excel.

Diviser un nombre à la décimale avec des formules

La fonction TRUNC peut être utilisée pour diviser un nombre à la décimale 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 les nombres dans une plage de cellules spécifiée au niveau du décimal dans Excel.

1. Sélectionnez une cellule pour placer la partie entière de la première cellule de nombre, saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez la cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les entiers des autres cellules de nombres.

=TRUNC(B5)

2. Sélectionnez une cellule à côté de la première cellule de résultat pour placer la partie décimale, saisissez la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez cette cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les décimales des autres cellules de nombres.

=B5-TRONQUE(B5)

Diviser le nombre à la décimale avec l'option Diviser les données en colonnes

Vous pouvez utiliser la fonctionnalité Convertir en nombres pour diviser les nombres à décimales dans Excel.

1. Sélectionnez la plage de cellules numériques que vous souhaitez diviser à la décimale, puis cliquez sur Données > Convertir en colonnes.

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

3. Dans la boîte de dialogue Étape 2 sur 3, cochez uniquement la case Autre, saisissez un point dans la zone de texte, puis cliquez sur le bouton Suivant.

4. Dans la Étape 3 sur 3 boîte de dialogue, cliquez sur le bouton pour sélectionner une cellule de destination où afficher les entiers et décimales divisés, puis cliquez sur le Terminer bouton. Voir la capture d'écran :

Vous pouvez alors voir que les parties entière et décimale sont séparées des cellules sélectionnées, comme le montre la capture d'écran ci-dessous. Cependant, la partie décimale perd son signe négatif, ce qui pourrait être nécessaire pour l'utilisateur.

Diviser le nombre à la décimale avec le remplissage instantané (versions 2013 et ultérieures)

Dans l'exemple ci-dessus, nous avons présenté l'utilisation de la fonction Remplissage instantané pour diviser le texte et les nombres dans les cellules, et ici nous présenterons la même méthode pour diviser un nombre à la décimale.

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

Remarque : Pour les nombres négatifs, n’oubliez pas de saisir le signe moins en même temps.

3. Sélectionnez les cellules contenant quelques exemples dans la colonne que vous souhaitez remplir, puis allez cliquer sur Données > Remplissage instantané.

Ensuite, les entiers sont extraits des cellules de nombres spécifiées comme le montre la capture d'écran ci-dessous.

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

3. Diviser les cellules de date

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

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

Supposons 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 de la division des dates.

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

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

Formules génériques

=DATE(A1)

=MOIS(A1)

=ANNEE(A1)

Argument

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

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

=JOUR(B5)

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

Obtenir le mois d'une date

=MOIS(B5)

Obtenir l'année d'une date

=ANNEE(B5)

Diviser les cellules de date en jour, mois et année avec l'option Diviser les données

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

Remarque : Dans la boîte de dialogue Étape 2 sur 3, cochez uniquement la case Autre et tapez un symbole / dans la zone de texte.

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

La démonstration ci-dessous montre comment diviser une date en mois, jour et année séparés avec la fonction Diviser les cellules de Kutools pour Excel.

Cette fonctionnalité vous aide à obtenir le résultat de la division des dates en masse en seulement quelques clics.

Cliquez pour en savoir plus sur cette fonctionnalité.

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

Supposons qu'il y ait une liste de dates et d'heures dans la plage B5:B9, et que vous souhaitiez séparer les dates et les heures dans des colonnes distinctes. Cette section démontrera différentes méthodes pour vous aider à réaliser cette tâche.

Diviser la date et l'heure dans une cellule avec une 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 au format Date et les cellules de la colonne d'heure au format Heure.

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

=ENT(B5)

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

=B5-D5

Diviser la date et l'heure dans une cellule avec le remplissage instantané (versions 2013 et ultérieures)

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

1. Créez les colonnes Date et Heure et saisissez quelques exemples de ce que vous souhaitez obtenir en sortie. Dans la colonne Date, nous entrons la date de B5 dans C5, et la date de B6 dans C6. Dans la colonne Heure, nous entrons l'heure de B5 dans D5 et l'heure de B6 dans D6. Voir capture d'écran :

2. Sélectionnez la colonne Date (y compris quelques exemples) que vous souhaitez remplir, puis accédez à Données > Remplissage instantané.

3. Sélectionnez la colonne Heure (y compris les quelques exemples) que vous souhaitez remplir, puis activez également la fonction Remplissage instantané. Ensuite, les dates et heures dans B5:B9 sont divisées en colonnes séparées comme le montre la démonstration ci-dessous.

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