Exceller

Supprimer les caractères numériques de la cellule

Strip Numeric Characters From Cell

Formule Excel: supprimer les caractères numériques de la celluleFormule générique
{= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (A1, ROW ( INDIRECT ('1:100')),1)+0), MID (A1, ROW ( INDIRECT ('1:100')),1),''))}
Résumé

Pour supprimer des caractères numériques d'une chaîne de texte, vous pouvez utiliser une formule basée sur le 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'un formule matricielle et doit être entré 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 un déployer 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 déployer de tous les caractères de B5, et testez 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 un 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.

exceller les jours entre la date et aujourd'hui

Travaillant de l'intérieur vers l'extérieur, le Fonction MID est utilisé pour extraire le texte en B5, un caractère à la fois.La clé est la RANGÉE et INDIRECT extrait 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 en tant que start_num argument. Pour num_chars , nous utilisons 1.

comment changer les limites dans excel

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 de texte 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 le Fonction IF avec le Fonction ISERR pour attraper ces erreurs. Lorsque nous voyons une erreur, nous savons que nous avons un caractère non numérique, donc nous apportons ce caractère dans le traitement déployer avec une autre fonction MID:

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

Si ne obtenir une erreur, nous savons que nous avons un nombre, nous insérons donc une chaîne vide ('') dans le tableau à la place du nombre.

Le résultat final du tableau entre dans la fonction TEXTJOIN en tant que texte 1 argument. Pour délimiteur , nous utilisons une chaîne vide ('') et pour ignore_empty nous fournissons TRUE. TEXTJOIN puis concatène 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 comme 100 dans INDIRECT, vous pouvez utiliser le 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 dans 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 placer la formule affichée sur cette page dans le Fonction TRIM :

comment supprimer plusieurs lignes dans Excel
 
= TRIM (formula)

Avec SEQUENCE

Dans Excel 365 , le nouveau 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 le 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 valeur de déployer est défini une seule fois, puis utilisé deux fois dans la fonction MID.

Auteur Dave Bruns


^