Téléchargez l'exemple de classeur
Ce didacticiel montrera comment supprimer des nombres du texte d'une cellule dans Excel et Google Sheets.
Nous allons discuter de deux formules différentes pour supprimer les nombres du texte dans Excel.
SUBSTITUT Fonction Formule
Nous pouvons utiliser une formule basée sur la fonction SUBSTITUTE. C'est une longue formule, mais c'est l'un des moyens les plus simples de supprimer des nombres d'une chaîne alphanumérique.
Dans cette formule, nous avons imbriqué les fonctions SUBSTITUTE 10 fois, comme ceci :
| 1 | =REMPLACER(REMPLACER(REMPLACER(REMPLACER(REMPLACER(REMPLACER(REMPLACER(REMPLACER(REMPLACER(B3,1,""),2,""),3,""),4,""),5," "),6,""),7,""),8,""),9,""),0,"") |
Formule tableau TEXTJOIN
Pour supprimer des nombres de chaînes alphanumériques, nous pouvons également utiliser une formule matricielle complexe composée des fonctions TEXTJOIN, MID, ROW et INDIRECT.
| 1 | {=TEXTJOIN("",TRUE,SI(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Remarque : TEXTJOIN est une nouvelle fonction Excel disponible dans Excel 2022+ et Office 365.
Il s'agit d'une formule complexe, nous allons donc la diviser en étapes pour mieux la comprendre.
Étape 1
La fonction MID est utilisée pour extraire la chaîne alphanumérique en fonction des arguments start_num et num_chars.
Pour l'argument start_num dans la fonction MID, nous utiliserons la liste de tableaux résultante des fonctions ROW et INDIRECT.
| 1 | =LIGNE(INDIRECT("1:"&LEN(B3))) |
Et pour l'argument num-chars, nous mettrons 1. Après avoir mis les arguments dans la fonction MID, elle renverra un tableau.
| 1 | {=MID(B3,LIGNE(INDIRECT("1 :"&LEN(B3))),1)} |
Étape 2
Nous ajouterons zéro à chaque valeur du tableau résultant (que nous obtenons de la fonction MID ci-dessus). Dans Excel, si vous ajoutez des nombres à des caractères non numériques, nous obtiendrons une #VALUE ! Erreur. Ainsi, après avoir ajouté 0 dans le tableau ci-dessus, nous obtiendrons un tableau de nombres et #Value ! Les erreurs.
| 1 | {=MID(B3,LIGNE(INDIRECT("1 :"&LEN(B3))),1)+0} |
Étape 3
Après avoir ajouté 0, le tableau résultant est placé dans la fonction ISERR. Comme nous le savons, la fonction ISERR renvoie TRUE pour les erreurs et FALSE pour les valeurs sans erreur.
Ainsi, cela donnera un tableau de VRAI et FAUX, VRAI pour les caractères non numériques et FAUX pour les nombres.
| 1 | =ESTERR(MID(B3,LIGNE(INDIRECT("1:"&LEN(B3))),1)+0) |
Étape 4
Maintenant, nous allons ajouter la fonction SI.
La fonction SI vérifiera le résultat de la fonction ISERR (étape 3). Si sa valeur est VRAI, il renverra un tableau de tous les caractères d'une chaîne alphanumérique. Pour cela, nous avons ajouté une autre fonction MID sans ajouter de zéro à la fin. Si la valeur de la fonction SI est FALSE, elle renverra un blanc (“”).
De cette façon, nous aurons un tableau qui ne contient que les caractères non numériques de la chaîne.
| 1 | =SI(ESTERR(MID(B3,LIGNE(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,LIGNE(INDIRECT("1:"&LEN(B3))),1 ),"") |
Étape 5
Enfin, le tableau ci-dessus est placé dans la fonction TEXTJOIN. La fonction TEXTJOIN joindra tous les caractères du tableau ci-dessus et ignorera la chaîne vide.
Le délimiteur de cette fonction est une chaîne vide ("") et la valeur de l'argument ignore_empty est entrée TRUE.
Cela nous donnera le résultat souhaité, c'est-à-dire uniquement les caractères non numériques de la chaîne alphanumérique.
| 1 | {=TEXTJOIN("",TRUE,SI(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Remarque : il s'agit d'une formule matricielle. Lors de la saisie de formules matricielles dans Excel 2022 ou version antérieure, vous devez utiliser CTRL + MAJ + ENTRÉE pour entrer la formule au lieu de la normale ENTRER.
Vous saurez que vous avez entré la formule correctement grâce aux accolades qui apparaissent. NE PAS taper manuellement les accolades, la formule ne fonctionnera pas.
Avec Office 365 (et probablement les versions d'Excel après 2022), vous pouvez simplement entrer la formule comme d'habitude.
Fonction TRIM
Lorsque les nombres sont supprimés de la chaîne, il peut rester des espaces supplémentaires. Pour supprimer tous les espaces de fin et de début, et les espaces supplémentaires entre les mots, nous pouvons utiliser la fonction TRIM avant la formule principale, comme ceci :
| 1 | =AJUSTER(C3) |
Supprimer des nombres du texte dans Google Sheets
La formule pour supprimer des nombres du texte fonctionne exactement de la même manière dans Google Sheets que dans Excel :
