Formule générique
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Sommaire
Pour supprimer des caractères numériques d'une chaîne de texte, vous pouvez utiliser une formule basée sur la fonction TEXTJOIN. Dans l'exemple illustré, la formule en C5 est:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Explication
Excel ne dispose pas d'un moyen de convertir les lettres d'une chaîne de texte en tableau directement dans une formule. Pour contourner ce problème, cette formule utilise la fonction MID, avec l'aide des fonctions ROW et INDIRECT pour obtenir le même résultat. La formule en C5, copiée vers le bas, est:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Cela semble assez compliqué, mais l'essentiel est que nous créons un tableau de tous les caractères de B5 et testons chaque caractère pour voir s'il s'agit d'un nombre. Si tel est le cas, nous rejetons la valeur et la remplaçons par une chaîne vide (""). Sinon, nous ajoutons le caractère non numérique à un tableau "traité". Enfin, nous utilisons la fonction TEXTJOIN (nouvelle dans Excel 2019) pour concaténer tous les caractères ensemble, en ignorant les valeurs vides.
Travaillant de l'intérieur vers l'extérieur, la fonction MID est utilisée pour extraire le texte en B5, un caractère à la fois. La clé est l'extrait de code ROW et INDIRECT ici:
ROW(INDIRECT("1:100"))
qui fait tourner un tableau contenant 100 nombres comme celui-ci:
(1,2,3,4,5,6,7,8… .99,100)
Remarque: 100 représente le nombre maximum de caractères à traiter. Modifiez en fonction de vos données ou utilisez la fonction LEN comme expliqué ci-dessous.
Ce tableau entre dans la fonction MID comme argument start_num . Pour num_chars , nous utilisons 1.
La fonction MID renvoie un tableau comme celui-ci:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Remarque: les éléments supplémentaires du tableau ont été supprimés pour plus de lisibilité.
À ce tableau, nous ajoutons zéro. C'est une astuce simple qui oblige Excel à contraindre le texte à un nombre. Les valeurs textuelles numériques telles que "1", "2", "3", "4" etc. sont converties sans erreur, mais les valeurs non numériques échoueront et afficheront une erreur #VALUE. Nous utilisons la fonction IF avec la fonction ISERR pour détecter ces erreurs. Lorsque nous voyons une erreur, nous savons que nous avons un caractère non numérique, nous apportons donc ce caractère dans le tableau traité avec une autre fonction MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Si vous n'obtenez pas d'erreur, nous savons que nous avons un nombre, donc nous insérons une chaîne vide ("") dans le tableau à la place du nombre.
Le résultat final du tableau entre dans la fonction TEXTJOIN en tant qu'argument text1. Pour le délimiteur, nous utilisons une chaîne vide ("") et pour ignore_empty nous fournissons TRUE. TEXTJOIN concatène ensuite toutes les valeurs non vides du tableau et renvoie le résultat.
Longueur de tableau précise
Au lieu de coder en dur un nombre tel que 100 dans INDIRECT, vous pouvez utiliser la fonction LEN pour créer un tableau avec le nombre réel de caractères dans la cellule comme ceci:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN renvoie le nombre de caractères de la cellule sous forme de nombre, qui est utilisé au lieu de 100. Cela permet à la formule de se mettre à l'échelle jusqu'à n'importe quel nombre de caractères automatiquement.
Suppression d'espace supplémentaire
Lorsque vous supprimez des caractères numériques, il se peut qu'il reste des espaces supplémentaires. Pour supprimer les espaces de début et de fin et normaliser les espaces entre les mots, vous pouvez envelopper la formule affichée sur cette page dans la fonction TRIM:
=TRIM(formula)
Avec SEQUENCE
Dans Excel 365, la nouvelle fonction SEQUENCE peut remplacer le code ROW + INDIRECT ci-dessus:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Ici, nous utilisons SEQUENCE + LEN pour construire un tableau de la bonne longueur en une seule étape.
Avec LET
Nous pouvons encore rationaliser cette formule avec la fonction LET. Comme le tableau est créé deux fois ci-dessus avec SEQUENCE et LEN, nous pouvons définir un tableau comme une variable et le créer une seule fois:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Ici, la valeur du tableau est définie une seule fois, puis utilisée deux fois dans la fonction MID.
