Exceller

Diviser le texte et les nombres

Split Text Numbers

Formule Excel: diviser le texte et les nombresFormule générique
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))
Résumé

Pour séparer le texte et les nombres, vous pouvez utiliser une formule basée sur le Fonction FIND , le Fonction MIN , et le Fonction LEN avec le 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},B5&'0123456789'))

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



Explication

Aperçu

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



Comme pour la plupart des formules qui divisent ou extraient du texte, la clé est de localiser le positionner de la chose que vous recherchez. Une fois que vous avez le poste, 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 dans des cellules séparées, 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},A1&'0123456789'))

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

 
= LEFT (A1,position-1)

Et, pour extraire uniquement le nombre, utilisez:



 
= RIGHT (A1, LEN (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 oblige la fonction FIND à effectuer une recherche distincte pour chaque valeur de 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 original est 'apples30', le tableau résultant sera:

 
{8,10,11,7,13,14,15,16,17,18}

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 numéro qui apparaît dans le texte d'origine. 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 original.

Vous vous posez peut-être des questions sur la construction étrange de within_text dans la fonction de recherche:

supérieur ou égal à vba
 
B5&'0123456789'

Cette partie de la formule concatène chaque nombre possible 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 survenir lorsqu'un nombre n'est pas trouvé.

Dans cet exemple, puisque nous supposons que le nombre apparaîtra toujours deuxième dans le texte d'origine, cela fonctionne bien car MIN ne force que le plus petit, ou première occurrence, d'un nombre à renvoyer. Tant qu'un nombre Est-ce que apparaissent dans le texte d'origine, cette position sera renvoyée.

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

Auteur Dave Bruns


^