Comment faire pour supprimer des nombres de chaîne dans Excel Alphanumeric
Nous avons appris comment lier: / excel-text-formules-split-numbers-and-text-from-string-in-excel [supprimer les valeurs numériques d’une cellule dans Excel 2016 et plus ancien]. Les formules que nous avons utilisées étaient un peu complexes mais maintenant Excel 2019 et 365 sont dans le jeu avec de nouveaux jouets, je veux dire des fonctions. Excel 2019 et 365 introduisent de nouvelles fonctions (TEXTJOIN et SEQUENCE)
qui peut faciliter la tâche de suppression des caractères numériques et de récupérer uniquement les valeurs non numériques dans une nouvelle cellule. Nous utiliserons des formules qui peuvent nous aider à le faire, plus facilement. Formule générique
|
= |
Jumbled_text: il s’agit du texte source à partir duquel vous souhaitez extraire toutes les valeurs numériques.
NumChars: il s’agit du nombre total de caractères que vous souhaitez traiter.
Le jumbled_text ne doit pas avoir plus de caractères que ce nombre (caractères et numériques combinés).
Voyons un exemple pour clarifier les choses.
Exemple: Supprimer les caractères numériques et extraire tous les alphabets et les caractères non numériques.
Nous avons donc ici un texte alphanumérique. Ce texte contient des chiffres et des caractères non numériques. Je dois me débarrasser des caractères numériques et obtenir uniquement les alphabets et autres valeurs de caractères, dans la colonne D.
Je ne m’attends pas à ce que le nombre total de caractères dans le texte brouillé soit supérieur à 100. La valeur de NumChars est donc de 100 ici. Vous pouvez augmenter ou diminuer ce nombre si nécessaire.
Appliquez ici la formule générique ci-dessus pour supprimer les caractères numériques.
|
= |
Lorsque vous appuyez sur le bouton Entrée, vous supprimez tous les caractères numériques et seules les valeurs alphabétiques restent. Faites glisser cette formule vers le bas pour supprimer les nombres de la chaîne de toutes les cellules de la colonne B.
Comment ça marche?
Voyons d’abord comment cette formule est résolue étape par étape.
|
1→
|
|
2→
is…site »,\{1,2,3,…100},1)+0), |
|
3→ |
|
4→
|
|
5→
|
|
6→
|
|
7→ « This is number one website » |
Pour la facilité de lecture, je n’ai pas écrit tout le tableau. J’ai utilisé des points (…) pour indiquer de longs tableaux.
Comme vous pouvez le voir, la formule commence à se résoudre de l’intérieur. Dans un premier temps, le lien: / excel-365-functions-the-sequence-function-in-excel [SEQUENCE function] est résolu. Depuis que nous avons dépassé 100 comme nombre de caractères. Il renvoie un tableau de nombres allant de 1 à 100.
Ce tableau est servi à la fonction MID comme numéro de départ. La fonction mid accède à chaque index sous forme de chaîne et divise chaque caractère dans un tableau. Vous pouvez le voir à l’étape 3. Je n’ai pas montré l’ensemble du tableau car il prendra trop de place. \ {« T »; « h »; « i »; « s »; « », « 1 » … « »; « »} * Ensuite, nous ajoutons 0 à chaque caractère. Dans Excel, si vous essayez d’ajouter un nombre à des caractères non numériques, il en résulte `link: / tips-value-error-and-how-to-fix-it-in-excel [#VALUE!
Erreur] `. Nous obtenons donc un tableau de nombres et #VALUE! Les erreurs.
\ {# VALEUR!; # VALEUR!; # VALEUR!; # VALEUR!; 1…; # VALEUR!}
Ce tableau est servi au lien: / excel-formule-and-function-iserror-function [ISERROR] `
une fonction. Comme vous le savez, la fonction ISERROR renvoie TRUE pour les erreurs et FALSE pour les valeurs sans erreur. Par conséquent, nous obtenons un tableau de TRUE et FALSE.
TRUE pour les caractères non numériques et FALSE pour les nombres.
\ {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
La même chose se produit pour les instructions de section TRUE de IF (link: / excel-text-formulas-the-mid-function-in-excel [MID] (B3, `link: / excel-365-functions-the -séquence-fonction-dans-excel [SEQUENCE] ` (100), 1) ).
Il renvoie un tableau de tous les caractères de la chaîne alphanumérique dans B3.
Désormais, pour chaque instruction TRUE, la fonction IF renvoie le caractère correspondant du tableau de la section true. Pour FALSE, IF renvoie le blanc (« »). Vous aurez maintenant un tableau qui ne contient aucun caractère numérique. \{« Cette »; » »….; » »}. Enfin, ce tableau est servi par la fonction link: / excel-365-functions-the-excel-textjoin-function [TEXTJOIN *]
une fonction. Cette fonction joint des textes donnés les uns aux autres, en ignorant les valeurs vides, avec un délimiteur donné. Par conséquent, nous obtenons une chaîne qui ne contient aucun caractère numérique. C’est le site Web numéro un *.
Amélioration de la formule
La formule ci-dessus utilise un nombre codé en dur pour traiter le nombre de caractères (nous en avons pris 100). Mais vous voudrez peut-être que ce soit dynamique. Dans ce cas, vous l’avez bien deviné, vous pouvez utiliser la fonction LEN. Il faudra le nombre exact de caractères pour le traitement. La formule sera donc.
|
= |
Ici, la fonction LEN détectera automatiquement les caractères numériques exacts dans la chaîne alphanumérique. Cela soulagera le fardeau de déterminer le nombre maximum de caractères.
Alternative de la fonction SEQUENCE
Si vous ne souhaitez pas utiliser la fonction SEQUENCE, vous pouvez utiliser une combinaison de fonction ROW et INDIRECT pour `link: / information-formulas-how-to-get-sequential-row-number-in-excel [générer des nombres séquentiels] ».
|
= |
L’INDIRECT convertira le texte (« 1: 100 ») en plage réelle, puis la fonction ROW listera tous les numéros de ligne de 1 à 100. (100 n’est qu’un exemple. Cela peut être n’importe quel nombre).
Alors oui les gars, voici comment vous pouvez extraire les caractères non numériques d’une chaîne alphanumérique dans Excel. J’espère avoir été suffisamment explicatif et cet article vous a aidé. Si vous avez des questions concernant ce sujet ou tout autre sujet Excel / VBA. Jusque-là, continuez à exceller.
Articles liés:
link: / excel-text-formulas-how-to-remove-non-numeric-characters-from-cells-in-excel [Comment supprimer les caractères non numériques des cellules dans Excel 2019:] Pour supprimer les caractères non numériques de une chaîne alphanumérique dans Excel, nous utilisons la nouvelle fonction TEXTJOIN. La bande de caractères non numériques d’une chaîne peut nous aider à nettoyer nos données pour une meilleure analyse des données. Alors, voici comment faire link: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [Split Numbers and Text from String in Excel 2016 and Older]: When we didn ‘ Pour avoir la fonction TEXTJOIN, nous avons utilisé les fonctions GAUCHE et DROITE avec d’autres fonctions pour séparer les caractères numériques et non numériques d’une chaîne. Extraire le texte d’une chaîne dans Excel à l’aide de la fonction GAUCHE et DROITE d’Excel: Pour supprimer le texte dans Excel de la chaîne, nous pouvons utiliser les fonctions GAUCHE et DROITE d’Excel. Ces fonctions nous aident à découper les chaînes de manière dynamique. \ Link: / excel-text-formulas-remove-lead-and-trailing-spaces-from-text-in-excel [Supprimer les espaces de début et de fin du texte dans Excel]: * Les espaces de début et de fin sont difficiles à reconnaître visuellement et peuvent gâcher vos données. La suppression de ces caractères de la chaîne est une tâche de base et la plus importante dans le nettoyage des données. Voici comment vous pouvez le faire facilement dans Excel.
link: / excel-text-formulas-remove-characters-from-right [Supprimer les caractères de droite]: * Pour supprimer des caractères à droite d’une chaîne dans Excel, nous utilisons la fonction GAUCHE. Oui, la fonction GAUCHE. La fonction GAUCHE conserve le nombre donné de caractères de GAUCHE et supprime tout ce qui se trouve à sa droite.
link: / excel-text-formulas-remove-unwanted-characters-in-excel [Supprimer les caractères indésirables dans Excel]: * Pour supprimer les caractères indésirables d’une chaîne dans Excel, nous utilisons la fonction SUBSTITUTE. La fonction SUBSTITUTE remplace les caractères donnés par un autre caractère donné et produit une nouvelle chaîne modifiée.
link: / excel-text-formulas-how-to-remove-text-in-excel-starting-from-a-position [Comment supprimer du texte dans Excel à partir d’une position dans Excel]: * Pour supprimer du texte de une position de départ dans une chaîne, nous utilisons la fonction REPLACE d’Excel. Cette fonction nous aide à déterminer la position de départ et le nombre de caractères à supprimer.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
lien: / formules-et-fonctions-introduction-de-vlookup-function [Comment utiliser la fonction RECHERCHEV Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. link: / tips-countif-in-microsoft-excel [Comment utiliser le]
lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]
lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante.
Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.