Comment séparer le texte et le nombre contenus dans une même cellule en deux colonnes distinctes ?
Si vous disposez d’une colonne contenant des chaînes de texte composées à la fois de lettres et de chiffres, vous souhaitez désormais séparer ces éléments en deux cellules distinctes, comme illustré ci-dessous. Dans Excel, vous pouvez réaliser cette opération à l’aide des méthodes suivantes.
Méthode 1 : séparer les Diviser par texte et nombre à l’aide de formules dans Excel
À l’aide des formules suivantes, vous pouvez extraire le texte « Diviser par » et le nombre contenus dans une cellule vers deux cellules distinctes. Voici la marche à suivre :
1. Saisissez cette formule dans une cellule vide – par exemple C3 – là où vous souhaitez afficher le résultat : =GAUCHE(A3;MIN(TROUVE())){0,1,2,3,4,5,6,7,8,9};A3&"0123456789"))-1)(où)A3 est la cellule contenant la chaîne de texte que vous souhaitez séparer), puis appuyez sur la touche Entrée pour extraire uniquement le texte situé avant les chiffres dans la cellule A3. Voir capture d’écran :
2. Ensuite, extrayez les nombres de la cellule en appliquant cette formule :=DROITE(A3;NBCAR(A3)-MIN(TROUVE())){0,1,2,3,4,5,6,7,8,9};A3&"0123456789"))+1) (A3 étant la cellule contenant la chaîne de texte que vous souhaitez traiter). Saisissez cette formule dans la cellule D3 où vous souhaitez afficher les chiffres, puis appuyez sur la touche Entrée pour obtenir immédiatement les chiffres comme suit :
3. Sélectionnez ensuite la plage C3:D3, puis faites glisser la poignée de recopie vers les cellules où vous souhaitez appliquer ces formules. Vous verrez alors que le texte « Diviser par » et le nombre ont été séparés dans des cellules différentes :
Kutools pour Excel's Diviser les cellulesest un outil puissant ; il vous permet de fractionner les valeurs des cellules en plusieurs colonnes ou lignes, et peut également scinder les chaînes alphanumériques en colonnes Diviser par texte et nombre distinctes, etc.…Cliquez pour télécharger Kutools pour Excel !
Méthode 2 : séparer les Diviser par texte et nombre à l’aide de la Remplissage instantané dans Excel 2013 et versions ultérieures
Avec les formules ci-dessus, vous ne pouvez séparer le texte et les chiffres que lorsque le texte précède les nombres. Pour extraire une chaîne dans laquelle les chiffres précèdent le texte, utilisez la fonctionnalité Remplissage instantané d’Excel 2013 et des versions ultérieures.
Si vous utilisez Excel 2013 ou une version ultérieure, la fonctionnalité Remplissage instantané peut vous aider à remplir automatiquement du texte dans une colonne et des chiffres dans une autre. Voici comment procéder :
1. Saisissez tous les chiffres de votre première chaîne de texte dans une cellule vide adjacente – B3 –, comme illustré ci-dessous :
2. Sélectionnez ensuite la plage B3:B7 où vous souhaitez remplir les chiffres, puis cliquez sur Données > Remplissage instantané. Les chiffres seront immédiatement insérés dans les cellules. Voir la capture d’écran :
3. Saisissez ensuite la chaîne de texte intégralement dans la cellule C3. Voir la capture d’écran :
4.Sélectionnez la plage C3:C7 où vous souhaitez insérer uniquement le texte, cliquez sur Données>Remplissage instantanécomme à l’étape 2, et vous verrez que le texte a été séparé comme suit :
Astuce : vous pouvez également faire glisser la poignée de recopie jusqu’à la plage souhaitée, puis cliquer sur Options de recopie automatique et cocher Remplissage instantané.
Méthode 3 : séparer les Diviser par texte et nombre mélangés de façon irrégulière à l’aide d’une fonction personnalisée
Si certaines chaînes de texte combinent de façon irrégulière du texte et des nombres, comme illustré ci-dessous, Excel ne propose pas de fonctionnalité standard pour résoudre ce problème. Toutefois, vous pouvez créer une fonction personnalisée pour y parvenir.
1. Maintenez enfoncées les touches ALT + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion > Module, puis collez le code suivant dans la fenêtre du module.
Code VBA : séparer les Diviser par texte et nombre en Cellules différentes depuis une seule cellule
Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
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. Ensuite, sauvegardez et fermez ce code, retournez à la feuille de calcul, puis saisissez la formule suivante =SplitText(A3;FAUX) dans une cellule vide pour obtenir uniquement la chaîne de texte. Enfin, faites glisser la poignée de recopie vers les cellules où vous souhaitez appliquer cette formule. Voir capture d’écran :
4. Ensuite, saisissez la formule =SplitText(A3;VRAI) dans une autre cellule, puis faites glisser la poignée de recopie vers les cellules où vous souhaitez appliquer cette formule pour obtenir les chiffres. Voir capture d’écran :
Remarque : le résultat sera incorrect s’il y a des nombres décimaux dans la chaîne de texte.
Méthode 4 : séparer les Diviser par texte et nombre en deux colonnes avec Kutools pour Excel
Si vous disposez de Kutools pour Excel, son outil puissant – Diviser les cellules – vous permet de fractionner instantanément les chaînes de texte en deux colonnes : l’une contenant les chiffres, l’autre le texte.
Après avoir installé Kutools pour Excel, procédez comme suit :
1. Sélectionnez la plage de données dont vous souhaitez séparer le texte et les nombres.
2. Cliquez ensuite sur Kutools > Fusionner et scinder > Diviser les cellules. Voir la capture d’écran :
3. Dans la boîte de dialogue Diviser les cellules, sélectionnez l’option Diviser en colonnes sous la section Type, puis cochez Diviser par texte et nombre dans la section Fractionner selon. Voir capture d’écran :
4. Cliquez ensuite sur le bouton Ok. Une boîte de dialogue s’affichera alors pour vous demander de sélectionner une cellule où afficher le résultat. Voir la capture d’écran :
5. Cliquez sur le bouton OK, et les chaînes de texte sélectionnées seront séparées en deux colonnes, comme illustré ci-dessous :
Cliquez pour télécharger Kutools pour Excel et profitez-en gratuitement dès maintenant !
Séparer les Diviser par texte et nombre avec Kutools pour Excel
Articles associés :
Comment fractionner un mot en lettres individuelles dans Excel ?
Comment diviser les cellules en plusieurs colonnes ou lignes à l’aide d’un retour chariot ?
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels