Formule générique
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Sommaire
Pour séparer le texte et les nombres, vous pouvez utiliser une formule basée sur la fonction FIND, la fonction MIN et la fonction LEN avec la fonction GAUCHE ou DROITE, 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 chiffre 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 la position de l'élément 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:
=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. Puisque 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:
(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 nombre 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 interrogez peut-être sur la construction étrange de within_text dans la fonction find:
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 en deuxième position dans le texte d'origine, cela fonctionne bien car MIN ne force que la plus petite, ou première occurrence, d'un nombre à être renvoyée. Tant qu'un nombre apparaît dans le texte d'origine, cette position sera renvoyé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 position fausse, la formule GAUCHE ci-dessus renverra toujours le texte et la formule DROITE renverra une chaîne vide ("").
