Il peut arriver que vous deviez diviser des cellules dans Excel. Cela peut être lorsque vous obtenez les données d'une base de données ou que vous les copiez à partir d'Internet ou que vous les obtenez d'un collègue.
Un exemple simple où vous devez diviser des cellules dans Excel est lorsque vous avez des noms complets et que vous souhaitez les diviser en prénom et nom de famille.
Ou vous obtenez l'adresse' et vous souhaitez diviser l'adresse afin que vous puissiez analyser les villes ou le code PIN séparément.
Comment diviser des cellules dans Excel
Dans ce didacticiel, vous apprendrez à diviser des cellules dans Excel à l'aide des techniques suivantes :
- Utilisation de la fonction Texte en colonne.
- Utilisation des fonctions de texte Excel.
- Utilisation de Flash Fill (disponible en 2013 et 2016).
Commençons!
Fractionner les cellules dans Excel à l'aide de texte en colonne
Ci-dessous, j'ai une liste de noms de certains de mes personnages de fiction préférés et je souhaite diviser ces noms en cellules séparées.:
Voici les étapes pour diviser ces noms en prénom et nom de famille :
- Sélectionnez les cellules dans lesquelles vous avez le texte que vous souhaitez diviser (dans ce cas A2:A7).
- Cliquez sur l'onglet Données
- Dans le groupe « Outils de données », cliquez sur « Texte dans les colonnes ».
- Dans l'assistant Convertir du texte en colonnes :
- Étape 1 sur 3 de l'Assistant Texte en colonnes: Assurez-vous que Délimité est sélectionné (c'est la sélection par défaut). Cela vous permettrait de séparer le prénom et le nom en fonction d'un séparateur spécifié (barre d'espace dans ce cas).
- Cliquez sur Suivant.
- Étape 2 sur 3 Assistant de conversion de texte en colonnes: sélectionnez Espace comme délimiteur et désélectionnez tout le reste. Vous pouvez voir à quoi ressemblerait votre résultat dans la section Aperçu des données de la boîte de dialogue.
- Cliquez sur Suivant.
- Étape 3 sur 3 Assistant de conversion de texte en colonnes: Dans cette étape, vous pouvez spécifier le format des données et où vous voulez le résultat. Je garderai le format de données comme général car j'ai des données de texte à diviser. La destination par défaut est A2 et si vous continuez, elle remplacera l'ensemble de données d'origine. Si vous souhaitez conserver les données d'origine intactes, sélectionnez une autre cellule comme destination. Dans ce cas, B2 est sélectionné.
- Cliquez sur Terminer.
- Étape 1 sur 3 de l'Assistant Texte en colonnes: Assurez-vous que Délimité est sélectionné (c'est la sélection par défaut). Cela vous permettrait de séparer le prénom et le nom en fonction d'un séparateur spécifié (barre d'espace dans ce cas).
Cela divisera instantanément le texte de la cellule en deux colonnes différentes.
Noter:
- La fonction Texte en colonne divise le contenu des cellules en fonction du délimiteur. Bien que cela fonctionne bien si vous souhaitez séparer le prénom et le nom, dans le cas du prénom, du deuxième prénom et du nom de famille, il le divisera en trois parties.
- Le résultat que vous obtenez en utilisant la fonction Text to Column est statique. Cela signifie que s'il y a des changements dans les données d'origine, vous devrez répéter le processus pour obtenir des résultats mis à jour.
Fractionner des cellules dans Excel à l'aide de fonctions de texte
Les fonctions de texte Excel sont idéales lorsque vous souhaitez découper et découper des chaînes de texte.
Alors que la fonctionnalité Texte en colonne donne un résultat statique, le résultat que vous obtenez en utilisant des fonctions est dynamique et se mettrait automatiquement à jour lorsque vous modifiez les données d'origine.
Fractionner les noms qui ont un prénom et un nom de famille
Supposons que vous ayez les mêmes données que celles indiquées ci-dessous :
Extraire le prénom
Pour obtenir le prénom de cette liste, utilisez la formule suivante :
=GAUCHE(A2,RECHERCHE(" ",A2)-1)
Cette formule repérerait le premier caractère espace, puis renverrait tout le texte avant ce caractère espace :
Cette formule utilise la fonction SEARCH pour obtenir la position du caractère espace. Dans le cas de Bruce Wayne, le caractère espace est en 6ème position. Il extrait ensuite tous les caractères à sa gauche en utilisant la fonction GAUCHE.
Extraction du nom de famille
De même, pour obtenir le nom de famille, utilisez la formule suivante :
=DROITE(A2,LEN(A2)-RECHERCHE(" ",A2))
Cette formule utilise la fonction de recherche pour trouver la position de la barre d'espace à l'aide de la fonction RECHERCHE. Il soustrait ensuite ce nombre de la longueur totale du nom (qui est donné par la fonction LEN). Cela donne le nombre de caractères du nom de famille.
Ce dernier nom est ensuite extrait en utilisant la fonction RIGHT.
Noter: Ces fonctions peuvent ne pas fonctionner correctement si vous avez des espaces de début, de fin ou doubles dans les noms. Cliquez ici pour savoir comment supprimer les espaces de début/de fin/double dans Excel.
Fractionner les noms qui ont un prénom, un deuxième prénom et un nom de famille
Il peut arriver que vous obteniez une combinaison de noms où certains noms ont également un deuxième prénom.
La formule dans de tels cas est un peu complexe.
Extraire le prénom
Pour obtenir le prénom :
=GAUCHE(A2,RECHERCHE(" ",A2)-1)
C'est la même formule que nous avons utilisée lorsqu'il n'y avait pas de deuxième prénom. Il recherche simplement le premier caractère espace et renvoie tous les caractères avant l'espace.
Extraire le deuxième prénom
Pour obtenir le deuxième prénom :
=SIERREUR(MID(A2,RECHERCHE(" ",A2)+1,RECHERCHE(" ",A2,RECHERCHE(" ",A2)+1)-RECHERCHE(" ",A2)),"")
La fonction MID commence à partir du premier caractère d'espacement et extrait le deuxième prénom en utilisant la différence de position du premier et du deuxième caractère d'espacement.
Dans les cas où il n'y a pas de deuxième prénom, la fonction MID renvoie une erreur. Pour éviter l'erreur, il est enveloppé dans la fonction SIERREUR.
Extraction du nom de famille
Pour obtenir le nom de famille, utilisez la formule ci-dessous :
=SI(LEN(A2)-LEN(REMPLACER(A2," ",""))=1,DROITE(A2,LEN(A2)-RECHERCHE(" ",A2)),DROITE(A2,LEN(A2) -RECHERCHE(" ",A2,RECHERCHE(" ",A2)+1)))
Cette formule vérifie s'il y a un deuxième prénom ou non (en comptant le nombre de caractères espace). S'il n'y a qu'un caractère espace, il renvoie simplement tout le texte à droite du caractère espace.
Mais s'il y en a 2, il repère le deuxième caractère d'espacement et renvoie le nombre de caractères après le deuxième espace.
Remarque : ces formules fonctionnent bien lorsque vous avez des noms qui ont soit le prénom et le nom de famille uniquement, soit le prénom, le deuxième prénom et le nom de famille. Cependant, si vous avez un mélange où vous avez des suffixes ou des salutations, vous devrez alors modifier davantage les formules.
Fractionner des cellules dans Excel à l'aide de Flash Fill
Flash Fill est une nouvelle fonctionnalité introduite dans Excel 2013.
Cela peut être très pratique lorsque vous avez un motif et que vous souhaitez en extraire rapidement une partie.
Par exemple, prenons les données du prénom et du nom de famille :
Le remplissage Flash fonctionne en identifiant des modèles et en les répliquant pour toutes les autres cellules.
Voici comment extraire le prénom de la liste à l'aide de Flash Fill :
- Dans la cellule B2, entrez le prénom de Bruce Wayne (c'est-à-dire Bruce).
- Avec la cellule sélectionnée, vous remarquerez un petit carré à l'extrémité droite de la sélection de cellule. Double-cliquez dessus. Cela remplira le même nom dans toutes les cellules.
- Lorsque les cellules sont remplies, en bas à droite, vous verrez l'icône Options de remplissage automatique. Clique dessus.
- Sélectionnez Flash Fill dans la liste.
- Dès que vous sélectionnez Flash Fill, vous remarquerez que toutes les cellules se mettent à jour et affichent maintenant le prénom pour chaque nom.
Comment fonctionne le remplissage flash ?
Flash Fill recherche les modèles dans l'ensemble de données et réplique le modèle.
Flash Fill est une fonctionnalité étonnamment intelligente et fonctionne comme prévu dans la plupart des cas. Mais il échoue également dans certains cas aussi.
Par exemple, si j'ai une liste de noms qui a une combinaison de noms avec certains ayant un deuxième prénom et d'autres pas.
Si j'extrait le deuxième prénom dans un tel cas, Flash Fill renverra par erreur le nom de famille au cas où il n'y aurait pas de prénom.
Pour être honnête, cela reste une bonne approximation de la tendance. Cependant, ce n'est pas ce que je voulais.
Mais c'est toujours un outil assez bon à conserver dans votre arsenal et à utiliser chaque fois que le besoin s'en fait sentir.
Voici un autre exemple où Flash Fill fonctionne à merveille.
J'ai un ensemble d'adresses dont je veux extraire rapidement la ville.
Pour obtenir rapidement la ville, entrez le nom de la ville pour la première adresse (entrez Londres dans la cellule B2 dans cet exemple) et utilisez le remplissage automatique pour remplir toutes les cellules. Utilisez maintenant Flash Fill et vous donnera instantanément le nom de la ville de chaque adresse.
De même, vous pouvez diviser l'adresse et extraire n'importe quelle partie de l'adresse.
Notez que cela nécessiterait que l'adresse soit un ensemble de données homogène avec le même délimiteur (virgule dans ce cas).
Si vous essayez d'utiliser Flash Fill alors qu'il n'y a pas de motif, une erreur s'affichera comme indiqué ci-dessous :
Dans ce didacticiel, j'ai couvert trois manières différentes de diviser des cellules dans Excel en plusieurs colonnes (à l'aide de Text to Columns, de formules et de Flash Fill)
J'espère que vous avez trouvé ce tutoriel Excel utile.
