Excel - comment ne conserver que les chiffres d'une cellule, pas les lettres.
Regarder la vidéo
- Vérifiez une colonne pour voir s'il y a des chiffres dans le code
- Facture Méthode 1:
- Remplissage flash
- Méthode Mike:
- Utilisez la fonction SUBSTITUTE avec une constante de tableau.
- Vous n'aurez pas à utiliser Ctrl + Maj + Entrée car il s'agit d'une constante de tableau
- Cela supprimera un chiffre à la fois
- Utilisez la fonction ET pour voir si chaque élément du tableau résultant est égal à l'élément d'origine
- Méthode de facturation 3:
- Utilisez une fonction VBA pour vérifier les chiffres
Transcription vidéo
Bill: Salut. Nous saluons le retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen. Je serai accompagné de Mike Girvin d'ExcelIsFun. Ceci est notre épisode 186: oui s'il y a des chiffres dans la cellule. La question d'aujourd'hui est envoyée par Jen, contient 13 000 lignes de données, doit parcourir une cellule, si un caractère est un chiffre, marquez-le comme Oui, sinon Non. Très bien. Eh bien, Mike, j'espère que vous avez un moyen génial de faire cela parce que ce n'est pas le cas.
Je vais utiliser le remplissage flash et, en remplissage flash, je vais juste lui donner un motif ici avec des lettres et des chiffres. Je veux m'assurer d'inclure tous les nombres possibles juste pour qu'il comprenne ce que je fais, et 0 comme ça. Donc, il y a les données d'origine, puis je vais demander au remplissage flash de le réparer pour moi, et, dans la version fixe, nous allons nous débarrasser de tous les chiffres. Alors, je vais regarder et voir s'il y a un chiffre. Si c'est le cas, débarrassez-vous-en comme ça, puis appuyez sur CONTROL + E pour flasher le remplissage, et ce que nous devrions avoir, ce sont maintenant toutes les lettres, juste les lettres.
Et puis la question est: a-t-il changé? Donc = SI c'est = à ce que nous recherchons, alors cela signifie qu'il n'y a pas eu de changements, pas de chiffres, alors nous disons Non, sinon Oui, comme ça, et double-cliquez, copiez cela, d'accord, et alors tout ce qui a un Non n'a pas de chiffres. Nous nous débarrassons de notre ligne d'origine, et une fois que nous copions ceci, CONTROL + C, ALT + E, S, V, et nous pouvons nous débarrasser du remplissage flash. Très bien, Mike. Voyons ce que vous avez. (= SI (A2 = B2, «Non», «Oui»))
Mike: Wow ,. Cela doit être l'utilisation unique et créative la plus étonnante du remplissage flash que je pense que j'ai jamais vue. Vous avez inventé un texte, sorti les nombres réels, CONTROL + E, et il a instantanément extrait tous les nombres et livré une nouvelle chaîne de texte sans les nombres, puis vous avez fait votre IF. Absolument magnifique.
Bien. Je vais revenir à cette feuille ici et je vais utiliser la fonction SUBSTITUTE. Maintenant, SUBSTITUTE, je vais lui dire de regarder ce texte juste là, et le OLD_TEXT que je veux trouver et supprimer, eh bien, ce sont tous les chiffres. Donc, je vais créer une constante de tableau (1, 2, 3, 4, 5 tous les chiffres, et). Maintenant, c'est une constante de tableau et elle se trouve dans OLD_TEXT parce que je n'y mets pas un seul élément, mais à la place, je mets un tas d'éléments. Il s'agit d'une opération de tableau d'arguments de fonction. Il y a 10 articles différents ici qui indiqueront à SUBSTITUTE de livrer 10 articles séparés, correctement, et s'il trouve l'un de ces articles, que veux-je? «». Cela dira à la fonction de ne rien y mettre,). (= REMPLACEMENT (A2, (1,2,3,4,5,6,7,8,9,0), «»))
Maintenant, mon curseur est à la fin. Lorsque j'appuie sur la touche F9, bien sûr, car il n'y a qu'un 0, tous sont exactement égaux à l'exception du dernier. Pour le dernier, le SUBSTITUT a trouvé le 0 et n'a rien mis à sa place. Maintenant, CONTROL-Z, CONTROL-ENTER et je vais le copier ici, F2 et F9. Donc, si nous allons au 6, il y a la version 5, il a supprimé le 5 là-bas, il a supprimé le 6 là-bas, et il a supprimé le 8 là-bas, donc il y aura 1, 2, 3 éléments différents qui sont différents. Ce n'est que lorsque tous les articles sont exactement égaux à l'article d'origine que cela nous indiquera qu'il n'y a pas de chiffres. ÉCHAPPER.
Je vais remonter au sommet. Cela ressemble à, F2, c'est un test logique ET. ET fonction. Je veux vérifier si chacun de ces éléments dans ce tableau résultant est = à l'élément d'origine. Quand ils sont tous vrais, cela me dira qu'il n'y a pas de chiffres dans cet anneau de texte. ), CONTROL + ENTER, j'obtiens un FALSE car l'un d'eux manque en interne ce 0. Je vais copier ceci ici. Celui-ci obtiendra bien sûr un VRAI - même chose avec ceux-ci - car tous les éléments générés en interne, si je F2 ici, tous ceux-ci, F9, sont exactement égaux à l'original. ÉCHAPPER. Maintenant, je viens au sommet. À propos, je n'ai pas eu besoin d'utiliser CONTROL + SHIFT + ENTER car lorsque vous utilisez cette constante de tableau dans votre formule de tableau, vous n'avez pas besoin d'utiliser CONTROL + SHIFT + ENTER. (= ET (SUBSTITUT (A2, (1,2,3,4,5,6,7,8,9,0), «») = A2))
Bien. Je vais venir au début. C'est mon test logique. Si tout cela s'avère vrai,, valeur si vrai, dans «NON», sinon veuillez mettre un OUI, »). CONTORL + ENTER. Double-cliquez dessus. Je vais devoir double-cliquer sur celui-ci et l'envoyer. Bien. C'était un peu amusant avec le SUBSTITUTE, une constante de tableau, un test logique ET et le IF, mais, je vous dis quoi, je ne peux toujours pas croire que le remplissage flash, comment vous l'avez utilisé pour, en substance, extraire tous les chiffres de cela. Très bien, je vais vous le renvoyer, MrExcel. (= SI (ET (SUBSTITUT (A2, (1,2,3,4,5,6,7,8,9,0), «») = A2), «Non», «Oui»))
Bill: Well, that formula with SUBSTITUTE and the array constant and AND, that is freaking amazing. I had to use flash fill because I couldn't have figured this one out. That is brilliant. Now, I do have a third way. Let's take a look at that.
Now, here's the way that I would really solve this, just a little bit of VBA. So, I do ALT+F11 to switch over to VBA, INSERT, MODULE, and then type this code. We're going to create a new function called HASNUMBERS and we're going to pass it to cell value, and we're going to start out with the word ALPHA. We look at every single character, and if that code, if the (ASC code - 06:35) of that character is between 48 to 57, then we say that it's NUMBERS, EXIT FUNCTION, and just keep going. Right, so, it looks until it finds a digit. When it does, it returns HASNUMBERS. So, here, we’re going to say =HASNUMBERS, point to that cell, and double-click to copy that down. Anytime it sees a digit over there, will get the NUMBERS, ALPHA, easy to sort them out. (=HasNumbers(A2))
Très bien, récapitulation rapide de l'épisode. Objectif: vérifiez la colonne pour voir s'il y a des chiffres dans le code du caractère, le code dans la cellule. J'ai utilisé le remplissage flash pour supprimer les chiffres, puis la fonction de longueur pour voir si cela avait changé ou non. Mike avait une formule brillante, la fonction SUBSTITUTE avec une constante de tableau. Vous n'avez pas besoin de CONTROL + SHIFT + ENTER. Vous devez supprimer un chiffre à la fois, puis utiliser la fonction ET pour examiner les 10 résultats pour voir si chacun est égal à l'élément d'origine. Brillante façon de procéder, puis, mon secours, utilisez une fonction VBA pour vérifier les chiffres.
Et bien Salut. Je tiens à remercier tout le monde d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de et ExcelIsFun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel186.xlsm
