Exceller

Fractionner le texte et les nombres

Split Text Numbers

Formule Excel : fractionner le texte et les nombresFormule générique |_+_| Sommaire

Pour séparer le texte et les nombres, vous pouvez utiliser une formule basée sur le Fonction TROUVER , les Fonction MIN , et le Fonction LEN avec le LA GAUCHE ou DROIT fonction, selon que vous souhaitez extraire le texte ou le nombre. Dans l'exemple illustré, la formule en C5 est :



= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

qui renvoie 7, la position du chiffre 3 dans la chaîne 'apples30'.

Explication

Aperçu

La formule semble complexe, mais la mécanique est en fait assez simple.





Comme avec la plupart des formules qui divisent ou extraient du texte, la clé est de localiser le position de la chose que vous recherchez. Une fois que vous avez la position, vous pouvez utiliser d'autres fonctions pour extraire ce dont vous avez besoin.

Dans ce cas, nous supposons que les nombres et le texte sont combinés et que le nombre apparaît après le texte. À partir du texte d'origine, qui apparaît dans une cellule, vous souhaitez diviser le texte et les nombres en cellules distinctes, comme ceci :



Original Texte Nombre
Pommes30 Pommes 30
pêches24 les pêches 24
oranges12 des oranges 12
pêches0 les pêches 0

Comme indiqué ci-dessus, la clé dans ce cas est de localiser la position de départ du nombre, ce que vous pouvez faire avec une formule comme celle-ci :

comment créer un index dans Excel
 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},B5&'0123456789'))

Une fois que vous avez la position, pour extraire uniquement le texte, utilisez :

 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

Et, pour extraire uniquement le nombre, utilisez :

 
= LEFT (A1,position-1)

Dans la première formule ci-dessus, nous utilisons la fonction FIND pour localiser la position de départ du nombre. Pour le find_text, nous utilisons la constante de tableau {0,1,2,3,4,5,6,7,8,9}, ce qui amène la fonction FIND à effectuer une recherche distincte pour chaque valeur dans la constante de tableau. Étant donné que la constante de tableau contient 10 nombres, le résultat sera un tableau avec 10 valeurs. Par exemple, si le texte d'origine est « apples30 », le tableau résultant sera :

 
= RIGHT (A1, LEN (A1)-position+1)

Chaque nombre de ce tableau représente la position d'un élément dans la constante du tableau à l'intérieur du texte d'origine.

Ensuite, la fonction MIN renvoie la plus petite valeur de la liste, qui correspond à la position dans le premier nombre qui apparaît dans le texte original. Essentiellement, la fonction FIND obtient toutes les positions numériques et MIN nous donne la première position numérique : notez que 7 est la plus petite valeur du tableau, ce qui correspond à la position du nombre 3 dans le texte d'origine.

Vous vous posez peut-être des questions sur la construction étrange pour dans_texte dans la fonction find :

supérieur ou égal à vba
 
{8,10,11,7,13,14,15,16,17,18}

Cette partie de la formule concatène tous les nombres possibles de 0 à 9 avec le texte original en B5. Malheureusement, FIND ne renvoie pas zéro lorsqu'une valeur n'est pas trouvée, c'est donc juste un moyen intelligent d'éviter les erreurs qui pourraient se produire lorsqu'un nombre n'est pas trouvé.

Dans cet exemple, puisque nous supposons que le nombre apparaîtra toujours seconde dans le texte original, ça marche bien car MIN ne force que le plus petit, ou premier occurrence, d'un numéro à renvoyer. Tant qu'un nombre Est-ce que apparaissent dans le texte original, cette position sera retournée.

Si le texte d'origine ne contient aucun nombre, une position 'fausse' égale à la longueur du texte d'origine + 1 sera renvoyée. Avec cette fausse position, la formule GAUCHE ci-dessus renverra toujours le texte et la formule DROITE renverra un chaîne vide ('').

Auteur Dave Bruns


^