Cliquer ici pour télécharger le fichier
Dans cet article je vous montre comment séparer le contenu d’une cellule au travers de 4 exemples en utilisant la fonctionnalité « Convertir » d’Excel.
1) Scinder des données séparer par un espace
Dans l’exemple on a une colonne avec le prénom et le nom qui sont séparés d’un espace et on souhaite la diviser en 2 colonnes différentes : une avec le prénom et une avec le nom correspondant.
= Il faut sélectionner la colonne pour laquelle on souhaite séparer le contenu.
= Pour séparer le contenu de la colonne sélectionnée il faut aller dans l’onglet « Données » puis cliquer sur l’outil « Convertir ».
= L’assistant conversion s’ouvre alors, il faut cocher la case « Délimité » puis cliquer sur « Suivant ».
= On peut cocher le séparateur « Espace » et cliquer sur « Terminer ». L’aperçu de données permet de visualiser le résultat obtenu par la configuration du séparateur avant de valider l’opération.
Résultat : Le prénom et le nom qui étaient contenus dans la même colonne et séparés par un espace sont désormais séparer en 2 colonnes : une colonne avec le prénom et une colonne avec le nom.
2) Scinder des données séparer par un espace
Dans l’exemple on a une colonne avec un numéro de compte et l’intitulé du compte correspondant qui sont séparés par un tiret et on souhaite la diviser en 2 colonnes différentes : une avec le numéro de compte et une avec l’intitulé du compte correspondant.
= Il faut sélectionner la colonne pour laquelle on souhaite séparer le contenu.
= Pour séparer le contenu de la colonne sélectionnée il faut aller dans l’onglet « Données » puis cliquer sur l’outil « Convertir ».
= Comme pour l’exemple précédent dans la première fenêtre qui s’ouvre dans L’assistant conversion il faut cocher la case « Délimité » puis cliquer sur « Suivant ».
= On peut cocher le séparateur « Autre » (car le tiret n’est pas proposé dans les séparateurs) et saisir le séparateur souhaité, un tiret (« -« ) dans l’exemple puis cliquer sur « Terminer ».
Résultat : Le numéro de compte et l’intitulé du compte qui étaient contenus dans la même colonne et séparés par un tiret sont désormais séparer en 2 colonnes : une colonne avec le numéro de compte et une colonne avec l’intitulé du compte.
= Dans la colonne intitulé du compte il y a des mots composés qui sont séparés par un tiret, l’outil de conversion d’Excel crée alors une séparation (et génère une nouvelle colonne) pour chaque séparateur retrouvé ; un tiret dans l’exemple.
= Pour éviter ce problème on peut remplacer les tirets par un caractère plus facilement repérable ; un signe pourcentage par exemple (« % »).
= Pour cela il faut sélectionner la colonne dans laquelle on souhaite remplacer les tirets par des % puis utiliser le raccourci clavier CTRL + H ; L’outil « Rechercher et remplacer » apparaît alors et il suffit de saisir un tiret « – » dans la case « Rechercher » et un signe pourcentage « % » dans la case « Remplacer par » puis de cliquer sur « Remplacer tout ».
Résultat : Tous les tirets sont remplacés par des signes pourcentage.
= Ensuite on remplace manuellement les « % » par des tirets aux endroits où il doit vraiment y avoir un tiret.
= On peut renouveler l’opération en saisissant cette fois-ci le séparateur « % » dans la case « Autre » puis cliquer sur « Terminer ».
Résultat : Le numéro de compte et l’intitulé du compte qui étaient contenus dans la même colonne et séparés par un signe pourcentage sont désormais séparer en 2 colonnes : une colonne avec le numéro de compte et une colonne avec l’intitulé du compte.
= Il ne reste plus qu’à supprimer l’espace situé devant l’intitulé du compte (espace qui était situé au départ à droite du tiret entre le numéro du compte et l’intitulé du compte) : Pour cela il faut utiliser la formule SUPPRESPACE en sélectionnant la cellule contenant l’intitulé du compte.
= On peut étirer la formule sur l’ensemble des lignes contenant l’intitulé du compte.
= On peut alors réaliser un collage spécial en valeurs de la colonne obtenu avec la formule SUPPRESPACE.
3) Scinder des données séparer par un espace
Dans l’exemple on a 4 données différentes dans la même colonne :
- Type de véhicule
- Immatriculation
- Numéro de contrat
- Numéro de chassis
Les 4 données ne sont séparées par aucun caractère ni espace et sont collées les unes aux autres.
= La première chose à faire est de compter le nombre de caractères pour chacune des 3 données « Immatriculation », « Numéro de contrat » et « Numéro de châssis ».
= Ici on peut utiliser cette fonctionnalité uniquement car les 3 différentes données qui comptent à chaque fois le même nombre de caractères d’une cellule à l’autre sont situés en tout début de cellule ; Le type de véhicule dont le nombre de caractères varie est situé à la fin de la chaîne de caractères et donc cela ne pose pas de problème pour la séparation.
= Pour cela il faut utiliser la formule NBCAR pour compter le nombre de caractères de chacune des 3 données.
= Cette fois-ci dans l’assistant conversion il faut cocher la case « Largeur fixe » puis cliquer sur « Suivant ».
= Il faut maintenant créer les séparateurs en cliquant aux emplacements voulus puis en cliquant sur « Suivant ».
= L’aperçu des données apparaît alors et il ne reste plus qu’à cliquer sur « Terminer ».
Résultat : Les 4 données : Numéro de série, Immatriculation, numéro de contrat et type de véhicule qui étaient contenus dans la même colonne et collées les unes aux autres sont désormais séparer en 4 colonnes : une colonne avec le numéro de série, une avec l’immatriculation, une avec le numéro de contrat et une avec le type de véhicule.
4) Scinder des données séparer par un espace
Dans l’exemple on a 5 données différentes dans la même colonne :
- Immatriculation
- Date de mise en service
- Couleur du véhicule
- Marque de la voiture
- Chevaux fiscaux
Dans cet exemple on a cette fois-ci plusieurs données différentes dont certaines à convertir en date, d’autres à supprimer. Il faut donc combiner à la fois la fonctionnalité convertir d’Excel avec l’outil « Largeur fixe » et avec l’outil « Délimité » puis utiliser des formules de traitement de données pour extraire certaines données impossibles à séparer avec la fonctionnalité convertir d’Excel.
= On va commencer par séparer la chaîne de caractères en utilisant des caractères comme séparateur.
= Il faut utiliser l’option « Délimité » de l’assistant de conversion puis on peut cocher le séparateur « Autre » (car le dièse n’est pas proposé dans les séparateurs) et saisir le séparateur souhaité, un dièse (« # ») dans l’exemple puis cliquer sur « Terminer ».
= Comme il y a 2 séparateurs « # » côte à côte l’outil de conversion génère alors une colonne vide ; Pour la supprimer il faut cocher la case « Interpréter des séparateurs identiques consécutifs comme uniques ».
= On peut ajouter le second séparateur « Espace » qui sépare la couleur du véhicule et le modèle puis cliquer sur « Terminer ».
= Pour la 2ème colonne crée avec la date en codification chiffres et la couleur du véhicule il faut utiliser un séparateur fixe.
= La date comprend 5 caractères ; il faut donc créer un séparateur au 5ème caractère puis cliquer sur « Terminer ».
= Cette manipulation sépare donc une colonne et 2 et Excel nous prévient que la colonne de droite contient déjà des données ; Soit on valide et cela supprime alors la colonne existante ; Soit on insère une colonne supplémentaire à droite avant d’effectuer la manipulation ; Soit on supprime une des colonnes.
= Dans l’exemple on ne souhaite pas conserver la couleur du véhicule on peut donc cliquer sur la colonne qui contient la couleur puis cocher la case « Colonne non distribuée » puis sur « Terminer ».
= La date est désormais isolée dans une seule colonne ; on peut donc la convertir dans le bon format.
= Il faut désormais isoler la marque du véhicule et le nombre de chevaux fiscaux ; Pour cela il faut combiner les formules CNUM et DROITE en récupérant les 2 derniers chiffres à droite.
Résultat : La formule renvoie la puissance fiscale lorsqu’elle est composée de 2 chiffres sinon elle renvoie un message d’erreur.
= Pour remplacer le message d’erreur par « rien » il faut utiliser la formule SIERREUR.
Résultat : Lorsque la puissance fiscale est composée de 2 chiffres la formule renvoie le résultat sinon elle ne renvoie rien.
= Pour retrouver la puissance fiscale lorsqu’elle n’est composée que d’un seul chiffre il faut utiliser la formule DROITE et ne prendre que le dernier caractère.
Résultat : La formule renvoie le dernier chiffre de la chaîne de caractères.
= On peut combiner le résultat des 2 colonnes précédentes dans une 3ème colonne pour obtenir la puissance fiscale sur un ou 2 chiffres en fonction du cas.
Résultat : La puissance fiscale est isolée de la marque du véhicule.
= Pour isoler la marque du véhicule il faut utiliser la formule GAUCHE en ne prenant que le nombre de caractères de la marque du véhicule ; Pour retrouver ce nombre il faut prendre le nombre total de caractère de la chaîne de caractères (marque du véhicule + puissance fiscale) et soustraire le nombre de caractères de la puissance fiscale.
Résultat : La formule permet d’isoler la marque du véhicule.
Résultat : Les 4 données : Immatriculation, date de mise en service, marque du véhicule et puissance fiscale qui étaient contenus dans la même colonne sont désormais séparer en 4 colonnes.
=> Voir aussi comment transposer des colonnes en lignes et inversement
