Aujourd'hui, c'est une question folle. Vous avez une colonne de numéros de pièce. Il y a de 4 à 7 tirets dans le numéro de pièce. Vous souhaitez extraire uniquement la partie du numéro de pièce après le premier tiret et jusqu'au dernier tiret, mais sans l'inclure. Ceci est un épisode d'Excel en duel.
Regarder la vidéo
- L'objectif est de trouver le premier et le dernier tiret et de tout garder entre les deux
- Le plus dur ici est de trouver le dernier tiret
- Facture Méthode 1: Remplissage Flash
- Remplissez manuellement les premiers (y compris certains avec différents nombres de tirets)
- Sélectionnez la cellule vide ci-dessous
- Ctrl + E pour Flash Fill
- Mike Méthode 2:
- Utiliser Power Query
- Dans Excel 2016, Power Query se trouve dans le groupe Obtenir et transformer dans Excel 2016
- Dans Excel 2010 et 2013, téléchargez Power Query à partir de Microsoft. Il crée un nouvel onglet Power Query dans le ruban
- Convertissez vos données en tableau en utilisant Ctrl + T
- Utilisez Diviser les données dans Power Query - d'abord pour diviser au tiret le plus à gauche, puis pour diviser au tableau le plus à droite
- Méthode de facturation 3:
- Fonction VBA qui itère de la fin de la cellule vers l'arrière pour trouver le dernier tiret
- Mike Méthode 4:
- Utilisez SUBSTITUTE pour trouver l'emplacement du Nième tiret
- SUBSTITUTE est la seule fonction de texte qui vous permet de spécifier un numéro d'instance
- Pour trouver quel numéro d'instance, utilisez
=LEN(A2)-LEN(SUBSTITUTE)
Transcription vidéo
Bill: Salut. Nous saluons le retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen de MrExcel. (Je serai accompagné par Mike Girvin d'ExcelIsFun. C'est notre - 00:03) épisode 185: extrait du premier - au dernier -.
Bien. La question d'aujourd'hui est envoyée par Anvar sur YouTube. Comment puis-je tout extraire du premier au dernier et consulter les données qu'il possède ici. Il y a un grand nombre de tirets, allant de 3, 5, 6, 7 tirets, d'accord?
Donc, ma première pensée est, eh bien, c'est vraiment facile de trouver le premier -, non? = gauche ou = MID du FIND de A2 et ensuite le -, +1 d'accord, mais pour arriver au dernier -, ça va me faire mal à la tête, à droite, parce que, eh bien, combien de tirets avons-nous? Nous pourrions prendre le SUBSTITUT de A2, en remplaçant les tirets, et comparer la longueur de cela, la longueur d'origine. Cela me dit le nombre de tirets, mais maintenant je sais lequel - trouver, le 2e, 3e, 4e, 5e, mais est-ce que j'utilise FIND?
J'étais prêt à passer à VBA, non? C'est ma réaction instinctive. J'ai dit, attendez une seconde. J'ai dit, Anvar, dans quelle version d'Excel êtes-vous? Il dit, je suis dans Excel 2016. J'ai dit, c'est magnifique. Si vous êtes dans Excel 2013 ou plus récent, nous pourrions utiliser cette nouvelle fonctionnalité appelée remplissage flash. Avec le remplissage flash, nous devons juste lui donner un motif, et je vais lui donner un motif suffisant pour que ce ne soit pas seulement que j'en prenne un avec deux tirets et que je le fasse plusieurs fois. Je veux m'assurer que j'ai quelques tirets différents de cette façon. Chad dans l'équipe Excel sait ce que je recherche. Chad est le type qui a écrit la logique du remplissage flash. Donc, j'en ai environ 3 là-dedans, puis CONTROL + E est le raccourci pour utiliser DATA, puis FLASH FILL, et, bien sûr, il semble que cela ait fait la bonne chose. Très bien, Mike.Voyons ce que vous avez.
Mike: Merci, MrExcel. Ouais. Le remplissage flash gagne. Cette fonctionnalité là, le remplissage flash, est l'un des outils Excel modernes qui est tout simplement incroyable. Si c'est un accord unique et que vous avez un modèle cohérent, c'est comme ça que je le ferais.
Hé, passons à la feuille suivante. Maintenant, au lieu d'utiliser le remplissage flash, nous pouvons en fait utiliser la requête d'alimentation. Maintenant, j'utilise Excel 2016 donc j'ai le groupe GET & TRANSFORM. C'est une question de pouvoir. Dans les versions antérieures, 2013 (de 10 à 14 h 30), vous devez en fait télécharger le complément gratuit de requête d'alimentation.
Maintenant, pour que la requête d'alimentation fonctionne, cela doit être converti en un tableau Excel. Maintenant, encore une fois, j'utiliserais le remplissage flash s'il s'agissait d'un accord unique. Quand utiliseriez-vous Power Query? Eh bien, si vous aviez de très grosses données ou si vous veniez d'une source externe, ce serait la voie à suivre, ou vous pourriez même aimer mieux que d'avoir à taper 3 ou 4 exemples pour le remplissage flash car, avec Power Query, nous pouvons spécifiquement dire trouver le premier - et trouver le dernier -.
Maintenant, je vais convertir cela en un tableau Excel. J'ai une seule cellule sélectionnée, des cellules vides tout autour. Je vais à INSERT, TABLE, ou vous utilisez le clavier, CONTROL + T. Je peux cliquer sur OK ou ENTRER. Je veux nommer cette table, je vais donc passer à TABLE TOOLS, DESIGN, jusqu'à PROPERTIES. Je vais appeler cela STARTKEYTABLE et ENTER. Maintenant, je peux revenir à DATA, le mettre en requête d'alimentation en utilisant le bouton FROM TABLE. Voilà ma chronique. Voilà le nom. Je ne veux pas garder ce nom car la sortie sera exportée vers Excel et je veux lui donner un nom différent. Donc, je l'appellerai CLEANEDKEYTABLE. Je n'ai pas besoin de ce TYPE CHANGÉ. Je regarde juste la source. Maintenant, je peux cliquer sur la colonne et, directement dans HOME, il y a le bouton SPLIT. Je peux dire SPLIT, BY DELIMITER. On dirait qu'il a déjà deviné. JE'Je vais dire GAUCHE-PLUS. Cliquez sur OK.
Maintenant, si je regarde ici, je vois CHANGÉ DE TYPE. Je n'ai pas besoin de ça alors je vais me débarrasser de cette étape. Je n'ai que COLONNE SPLIT PAR DELIMITER. Maintenant, je vais le faire à nouveau mais, au lieu d'utiliser le bouton SPLIT ici, cliquez avec le bouton droit de la souris sur SPLIT COLUMN, BY DELIMITER, et regardez cela. Nous pouvons choisir de le diviser par le DÉLIMITEUR LE PLUS DROIT. Cliquez sur OK. Maintenant, je n'ai pas besoin de ces deux colonnes donc je vais faire un clic droit sur la colonne que je veux conserver, SUPPRIMER LES AUTRES COLONNES. Je vais en fait sortir ce TYPE CHANGÉ. Ça va dire: ÊTES-VOUS SÛR DE VOULEZ SUPPRIMER CECI? Je vais dire, oui, SUPPRIMER. Il y a mes données propres.
Maintenant, je peux venir à CLOSE & LOAD. CLOSE & LOAD TO. Il s'agit de la nouvelle boîte de dialogue IMPORT. Il disait LOAD TO mais je veux le charger dans une table, sur une FEUILLE DE TRAVAIL EXISTANTE. Cliquez sur le bouton Réduire. Je vais sélectionner C1, décompresser, cliquer sur OK, et c'est parti. Power query pour nettoyer nos données et obtenir uniquement les données que nous voulons. Bien. Je vais le renvoyer.
Bill: Il y a le point juste là, le DELIMITER LE PLUS DROIT dans la COLONNE SPLIT PAR DELIMITER, l'une des fonctionnalités intéressantes de Power Query. C'est génial.
Bien. Ma réaction instinctive - VBA UDF (inintelligible - 05:34) vraiment facile à faire VBA. Basculez sur ALT + F11. INSÉREZ un MODULE. Dans ce module, tapez ce code. Je vais (créer une - 05:43) toute nouvelle fonction, je vais l'appeler MIDPART, et je vais lui passer du texte, puis ce que je vais faire, c'est aller du dernier caractère de cette cellule de la longueur de MYTEXT à 1, ÉTAPE -1 et regarder ce caractère. Ainsi, le MID de MYTEXT, cette variable i, nous indique quel caractère nous recherchons pour une longueur de 1. Est-ce un -? Dès que je trouve un -, je vais prendre la GAUCHE de MYTEXT en commençant au caractère i - 1, donc je me débarrasse de tout pour ce dernier - jusqu'au bout, et ensuite, je m'assure de ne pas y aller continuez à chercher plus de tirets, EXIT FOR me sortira de cette boucle (inintelligible - 06:17),et à partir de là, c'est la partie facile. Nous allons simplement prendre le MYTEXT, commencer au MID de MYTEXT, (où j'utilise le - 06:26) utiliser la fonction FIND pour trouver le premier -, aller 1 de plus que cela, et renvoyer cela.
Alors, revenons en arrière, ALT + Q, pour revenir à Excel. = Onglet MIDPART de cela, et il semble que cela fonctionne. Copiez cela. Mike, en avez-vous un autre? (= MIDPart (A2))
Mike: Eh bien, j'en ai une autre, mais ce sera une formule longue - pas aussi courte que cette UDF. Très bien, passons à la feuille suivante. Maintenant, si nous voulons faire une formule et que nous avons du texte et qu'il y a toujours un nombre différent de délimiteurs, d'une manière ou d'une autre, je dois connaître la position de ce dernier délimiteur.
Maintenant, cela va prendre quelques étapes mais je vais commencer par la fonction SUBSTITUTE. Je vais parcourir ce texte,, l'ancien texte que je veux trouver est dans », que -,, et qu'est-ce que je veux mettre à sa place ou remplacer? «». Cela ne mettra rien dedans. Maintenant, si I) et CONTROL + ENTER, qu'est-ce que cela va faire? (= SUBSTITUT (A2, «-», «»))
Well, now I can take the length of this and subtract it from the length of this item. That will tell me how many delimiters there are. F2, and right at the beginning, I'm going to type the length of that. That will give me the full length - the length of that dashless text, ), CONTROL+ENTER, double click, and send it down. that tells me how many delimiters there are for this text. There are 6. (=LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))
Now I'm going to use that sixth now inside of substitute to put a different character right at the sixth listing of the delimiter, F2, and if I type SUBSTITUTE, what we want to notice is this function has an instance number. If you look at other text functions like search and find, they don't have an instance number. Substitute is the only one I can think of that actually lets you specifically say which instance of a delimiter you want to deal with. Here's the text, ,. Old text is in “ a -, and I need to pick for the new text some character that will never be in this text ring. I'm going to choose, like, or something like that, , and that's where instance number comes in, ), CONTROL+ENTER, and there it is. If I double click and send it down, it's always putting that in the position of the last delimiter. (=SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))
Now I need to figure out, in each one of these, what position it is in. F2. I'm going to use the SEARCH function. SEARCH. I type S and tab. Now, search and find are the same except for search is not case-sensitive. In this case, either one would be fine because the text I'm looking for is in “, that ^, ”, , within that text. By the way, the reason that I use search instead of find is because S tab gets me search but F I tab will get me find. So, it's like one character less when typing it out. CONTROL+ENTER, double click and send it down, and now it tells me, in the 27th position is that last delimiter. (=SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”)))))
Now, I'm going to take this approach for these text items. I'm now going to use the left function and get everything from the very beginning all the way up to that position. That will get rid of that last little bit. Now, actually, search tells us 27 which is right there and we only want to go to 26. So, F2, and, at the end, I'm going to - 1, CONTROL+ENTER, double click and send it down. Now, I can use the left function. F2. LEFT. There it is, left of that, ,. That's how many characters. ), CONTROL+ENTER, double click and send it down. So, now, we have gotten rid of the last little bit after the last delimiter in every cell. (=LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1))
Now all I need to do is replace the first four characters, first four characters, first three characters. Now, I can use the search function on the original text because it can find the - which is three and I'll tell replace, please go, from the first character, three characters in and replace it with nothing, F2, and right at the beginning, I'm going to type REPLACE. There's the old text. Now watch this. I want to give myself a little bit more breathing room. I'm just going to artificially pick a space, ALT+ENTER. That's kind of like we do in DAX. Now I just have more breathing room. That's the old text, ,. The starting number, I need to always start at the first position so I simply type 1, , and I need to find that first - which represents number of characters. So, S tab, “-” , through… within that text, that search will find 4, 4, 3. That will work. ) and then , new text “”. That will put nothing in those first characters. ). I have the entire column highlighted so I can populate this edited formula with CONTROL+ENTER, and there we go. All the way down, we’re extracting everything between the first and the last -. (=REPLACE(LEFT(A2,SEARCH(“^”,SUBSTITUTE(A2,“-”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“-”,“”))))-1),1,SEARCH(“-”,A2),“”))
Now, the only reason we want to be crazy like that with formulas is if we wanted the formula result to instantly update whenever we changed anything, so if I type -00, instantly it updates. Power query and flash fill will not automatically update, alright? Send it back to.
Bill: Well, that was one heck of a formula. Like, substitute was the trick. I had used substitute in the first step but didn't see that it had the instance number. Alright, so, we have four different methods here today. My first method is flash fill. Select first few, select the blank box below that, and then CONTROL+E to flash fill. Mike's method, use power query. I love that, especially the split data letting you use the leftmost - and then the rightmost -. My live seminars always talk about this one feature. Should be a finalist for the Nobel Prize for the best excel feature. It wouldn't win but it would be in one of the top five, I'm sure. My method number three, VBA function, a UDF user-defined function, that iterates from the end of the cell, and then, Mike's method, the awesome formula method. Use substitute to find the location of the nth - and then pass that answer back into substitute that tells you which instance number to look from. Brilliant.
Eh bien, voilà. Je tiens à remercier tout le monde d'être passé. Nous vous reverrons la prochaine fois pour un autre podcast Dueling Excel de et ExcelIsFun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel185.xlsm
