Comment diviser un texte en colonnes séparées par des lettres majuscules dans Excel ?
Lorsque vous travaillez avec des données dans Excel, vous pourriez occasionnellement rencontrer des cellules contenant plusieurs mots fusionnés ensemble, chaque mot commençant par une lettre majuscule, par exemple "PrénomNom" ou "VentesMarsAvril". Pour le nettoyage ou l'analyse des données, il peut être nécessaire de diviser le contenu en colonnes distinctes à chaque lettre majuscule, comme illustré dans la capture d'écran ci-dessous. Cependant, Excel ne propose pas de fonction intégrée directe pour réaliser cette division par lettres majuscules. Quelles approches pratiques peuvent vous aider à séparer rapidement et précisément les mots en colonnes individuelles basées sur les lettres majuscules ?
Vous trouverez ci-dessous trois solutions efficaces à ce problème, chacune ayant son propre scénario d'application et ses forces :
Diviser un texte en colonnes séparées par des lettres majuscules avec des formules
Si vos données consistent en des valeurs de cellules contenant seulement deux mots joints par des lettres majuscules, les formules Excel peuvent aider à diviser le contenu en colonnes distinctes. Cette solution est simple, ne nécessite aucun code ni module complémentaire, et convient aux petits ensembles de données ou aux besoins de division moins complexes.
1. Dans une cellule vide adjacente à vos données (par exemple, la cellule C2), entrez la formule matricielle suivante pour extraire le premier mot de la cellule A2 (où A2 contient le texte à diviser) :
=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)Après avoir tapé la formule, assurez-vous de presser simultanément Ctrl + Maj + Entrée. Ceci est requis pour les formules matricielles dans certaines versions d'Excel. Si c'est correctement fait, des accolades {} apparaîtront autour de votre formule dans la barre de formule. Cette formule fonctionne en recherchant la deuxième lettre majuscule dans votre cellule, puis en extrayant tous les caractères avant cette position, donnant le premier mot.
2. Pour appliquer la formule aux autres entrées, sélectionnez la cellule C2, puis faites glisser la poignée de recopie vers le bas pour remplir la formule dans des cellules supplémentaires de la colonne. Tous les premiers mots correspondants pour chaque ligne seront automatiquement extraits.
3. Ensuite, dans la cellule à droite (par exemple, D2), entrez la formule suivante pour extraire le reste du texte (le deuxième mot) :
=REPLACE(A2,1,LEN(C2),"")Cette formule supprime le premier mot qui a été extrait à l'étape précédente, ne laissant que le deuxième mot. Appuyez simplement sur Entrée pour confirmer.
4. De même, sélectionnez la cellule D2 et utilisez la poignée de recopie pour faire glisser cette formule vers le bas sur d'autres lignes. Le texte de chaque cellule dans votre plage d'origine sera désormais divisé en deux colonnes, séparées par la position de la lettre majuscule.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Diviser un texte en colonnes séparées par des lettres majuscules avec du code VBA
La méthode basée sur les formules ci-dessus convient bien aux cas où seuls deux mots doivent être divisés, mais elle ne peut pas gérer les cellules contenant plus de deux mots concaténés (par ex., 'PrénomDeuxièmeNomNom'). Dans de tels cas, vous pouvez utiliser du code VBA pour insérer automatiquement un espace avant chaque lettre majuscule, segmentant ainsi les mots. Après avoir ajouté ces espaces, vous pouvez utiliser la fonctionnalité Texte en colonnes intégrée d'Excel pour diviser le texte en colonnes individuelles.
Tout d'abord, appliquez le code VBA pour ajouter des espaces avant les lettres majuscules afin de séparer les mots.
1. Appuyez sur ALT + F11 pour ouvrir la fenêtre de l'éditeur Microsoft Visual Basic pour Applications.
2. Dans l'éditeur VBA, cliquez sur Insérer > Module, et collez le code suivant dans la nouvelle fenêtre de module.
Code VBA : Insérer des espaces avant chaque lettre majuscule dans une cellule
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
Dim I As Integer
SplitWords = Left(Str, 1)
For I = 2 To Len(Trim(Str))
If (Asc(Mid(Str, I, 1)) > 64) And _
(Asc(Mid(Str, I, 1)) < 91) And _
(Mid(Str, I - 1, 1) <> " ") Then _
SplitWords = SplitWords & " "
SplitWords = SplitWords & Mid(Str, I, 1)
Next
End Function
Après avoir tapé ou collé le code, fermez l'éditeur VBA pour revenir à la feuille de calcul. Ce code définit une nouvelle fonction appelée splitwords que vous pouvez utiliser comme une formule de feuille de calcul normale. Notez qu'il peut être nécessaire d'activer les macros pour utiliser cette fonction, et vous devriez enregistrer votre classeur sous forme de fichier .xlsm pour conserver le code VBA.
3. Dans une cellule vide à côté de vos données, entrez la formule suivante pour insérer des espaces avant chaque lettre majuscule dans la cellule A2 :
=splitwords(A2)Cette formule appelle la fonction définie par l'utilisateur pour traiter chaque cellule. Après avoir appuyé sur Entrée, vous devriez voir votre texte avec des espaces insérés avant chaque lettre majuscule, rendant les mots individuels distincts.
4. Copiez cette formule vers le bas pour toutes les cellules pertinentes en utilisant la poignée de recopie, de manière à ce que chaque ligne soit traitée en conséquence.
5. À ce stade, les cellules contiennent des formules. Vous pourriez souhaiter copier et coller les valeurs ailleurs pour ne retenir que le texte traité, sans le lien de formule. Pour cela, sélectionnez les cellules traitées, copiez-les, puis faites un clic droit et choisissez 'Collage spécial' > 'Valeurs' à un emplacement souhaité.
Ensuite, appliquez la fonctionnalité Texte en colonnes pour diviser le contenu des cellules en colonnes séparées par l'espace.
6. Maintenant, sélectionnez les valeurs des cellules contenant des espaces (les résultats de l'étape précédente). Sur le ruban, accédez à Données > Texte en colonnes. Lorsque l'assistant Convertir en colonnes apparaît (Étape 1), choisissez le bouton radio Délimité.
7. Cliquez sur Suivant, et dans l'Étape 2 sélectionnez l'option Espace sous Délimiteurs. Cela indiquera à Excel d'utiliser les espaces comme point de séparation pour diviser les données en colonnes.
8. Dans l'Étape 3, assurez-vous que Général est sélectionné pour le format des données de la colonne (ou choisissez un autre format si nécessaire), et sélectionnez la cellule où vous souhaitez que les données divisées apparaissent en cliquant 
. Assurez-vous que la plage de destination ne remplace pas les données existantes.
9. Cliquez sur Terminer, et Excel séparera le texte en plusieurs colonnes, chacune représentant un mot qui était initialement séparé par une lettre majuscule.
Si vous rencontrez une erreur où certains mots ne sont pas divisés comme prévu, vérifiez les situations exceptionnelles telles que les acronymes (plusieurs majuscules en séquence) ou les espaces de fin. Envisagez d'affiner votre code VBA ou de revérifier vos données pour détecter les anomalies. N'oubliez pas également de sauvegarder régulièrement votre travail lors de l'utilisation de VBA ou de l'exécution d'opérations de données en lot.
Cette méthode est particulièrement utile pour traiter des longueurs de texte incohérentes ou un nombre de mots par cellule variable. Toutefois, certains utilisateurs peuvent être moins à l'aise avec l'exécution de code VBA, auquel cas vous pouvez envisager les méthodes alternatives ci-dessous.
Diviser un texte en colonnes séparées par des lettres majuscules avec Kutools pour Excel
Kutools pour Excel fournit une solution conviviale et efficace pour les utilisateurs de tous niveaux pour diviser un texte par des lettres majuscules, particulièrement utile pour les grands ensembles de données ou les tâches répétitives. En combinant ses fonctionnalités Ajouter du texte et Diviser les cellules, vous pouvez effectuer l'opération avec un minimum d'effort et de risque.
1. Sélectionnez les cellules contenant les données d'origine que vous souhaitez diviser.
2. Cliquez sur Kutools > Texte > Ajouter du texte. Cet outil vous aide à insérer un caractère ou un séparateur—tel qu'un espace—avant chaque lettre majuscule.
3. Dans la boîte de dialogue Ajouter du texte, tapez un espace (ou un autre séparateur si nécessaire) dans la zone de saisie Texte. Puis, sous la section Ne rajouter que si, sélectionnez La première lettre est en majuscule. Cela indiquera à Kutools d'ajouter votre séparateur choisi avant chaque lettre majuscule dans les cellules sélectionnées.
4. Cliquez sur OK. Kutools ajoutera automatiquement des espaces avant chaque lettre majuscule, préparant instantanément vos données pour la division.
5. Ensuite, avec la plage traitée toujours sélectionnée, rendez-vous sur Kutools > Fusionner & Diviser > Diviser les cellules pour commencer à séparer le texte en colonnes par des délimiteurs.
6. Dans la boîte de dialogue Diviser les cellules, choisissez Diviser en colonnes sous la section Type, et cochez la case Espace (ou le délimiteur ajouté précédemment) pour indiquer où les divisions doivent avoir lieu.
7. Cliquez sur OK. Une invite apparaîtra vous permettant de choisir la cellule de départ pour les résultats de la division. Spécifiez soigneusement la destination, en veillant à avoir suffisamment d'espace pour les colonnes divisées.
8. Enfin, cliquez sur OK pour terminer l'opération de division. Vos données seront maintenant distribuées en colonnes séparées à chaque lettre majuscule, de manière efficace et précise.
Cette méthode est idéale pour des données complexes, volumineuses ou fréquemment mises à jour, et aide à éviter les ajustements manuels des formules ou la programmation. Rappelez-vous que Kutools pour Excel étend son utilité avec de nombreuses autres fonctionnalités, facilitant une plus grande efficacité dans les tâches courantes et avancées.
Téléchargez et essayez gratuitement Kutools pour Excel dès maintenant !
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels