Sommige van de vragen die binnenkomen, zijn behoorlijk moeilijk. Tegenwoordig hebben we een kolom met cellen. Elke cel heeft enkele woorden, dan een datum en dan nog wat woorden. Het doel is om het datumgedeelte van die tekst naar een nieuwe kolom te slepen. Dit is een duellerende aflevering met ideeën van Bill en Mike.
Bekijk video
- Bill's superbrede aanpak:
- Zet alle 12 maanden in aparte kolommen
- Gebruik de FIND-functie om te zien of deze maand in de originele tekst staat
- Gebruik = AGGREGATE (5,6,…
- Een paar extra formules om voor de maand een nummer 2 of 3 posities te zoeken
- Mike's aanpak:
- Gebruik SEARCH in plaats van FIND. Zoeken is hoofdlettergevoelig, Zoeken is dat niet.
- Maak een array-bewerking van een functieargument door B13: B24 op te geven als Find_Text.
- De formule retourneert #WAARDE! Fout, maar als u op F2, F9 drukt, ziet u dat het een array retourneert.
- De eerste 13 functies in AGGREGATE kunnen geen array aan, maar de functies 14-19 kunnen wel een array aan.
- 5 = MIN en 15 = SMALL (, 1) zijn vergelijkbaar, maar SMALL (, 1) werkt met een array.
- LOOKUP, SUMPRODUCT, CHIKW.TEST, INDEX en AGGREGATE kunnen functiematrixargumenten verwerken zonder Ctrl + Shift + Enter
- Mike was slimmer door te kijken of 2 karakters voor de Start een cijfer is, en dan 3 karakters daarvoor te pakken. De extra ruimte wordt geëlimineerd door de TRIM ()
- Gebruik de functie SUBSTITUTE om de titel te krijgen om de datumtekst in kolom C te verwijderen
Videotranscriptie
Bill Jelen: Hey, welkom terug. Het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van. Ik word bijgestaan door Mike Girvin van Excel Is Fun.
Dit is ons Duel # 170: Dates vinden
Hey, welkom terug allemaal. Ik had hier zo'n geweldige vraag en ik kon hem niet oplossen. Ik kon het tenminste niet gemakkelijk oplossen, dus ging ik naar Mike Girvin en ik zei: "Mike, hé, heb je een manier om dit te doen?" Hij zei: "Ja, ik heb een manier om het te doen. Laten we een duel doen. "
Dus iemand op YouTube heeft deze gegevens ingezonden en elke cel in het algemeen heeft zoiets als een documenttitel gevolgd door een datum. Ze wilden deze gegevens opsplitsen in de titel van het document: wat het is, wat het ding is en wat de datum is. Maar de datums zijn volkomen slecht. Zoals hier is het 20 januari; maar hier beneden zijn er dingen waarvan de datum kan zijn na de cel, 9 april. Oké, en op welke manier het ook is, we willen het vinden. En soms zijn er twee dates en dit is gewoon helemaal vreselijk en dat het zo'n verwisselde situatie van dates is en dat er mogelijk niet eens een date komt opdagen, oké. Dus hier is mijn poging. Aan de rechterkant zet ik de dingen die ik zoek. Wat ik hier echt leuk vind, is dat ze de naam van de maand nooit hebben afgekort. Ik echt,waardeer dat echt. Dus typ in januari en ik sleep hier zo naar december, en voor elke cel die ik wil weten, kunnen we vinden = VIND die januari. Dus ik ga een, twee keer op F4 drukken om het vast te zetten tot slechts een rij, in de tekst daar in kolom A, zoals dat. Ik druk een, twee, drie keer op F4 om het vast te zetten op de kolom, oké. En hier vertelt het ons dat januari wordt gevonden op positie 32 en voor de andere 11 maanden gaat het ons vertellen dat het helemaal niet wordt gevonden. Met andere woorden, we krijgen nu de Value-fout. Wat ik daar moet doen, is dat ik moet vinden, ik moet de minimumwaarde vinden en alle waardefouten negeren. Dus maak deze kleine formule hier zichtbaar = AGGREGEREN en laten we dit helemaal opnieuw bouwen, = AGGREGEREN, wat we willen is de MIN, dus dat is het getal 5,en negeer vervolgens de foutwaarden nummer 6 komma en vervolgens al deze cellen van januari tot en met december. En wat dat ons gaat vertellen, is dat het ons gaat vertellen waar de maand plaatsvindt. En in dit geval krijgen we 0, stel dat de maand helemaal niet gebeurt.
Oké, laten we de rest hiervan zichtbaar maken. Dus om de situatie aan te pakken waarin we hier 20 januari of 1 november hebben, zei ik dat het eerste wat ik ga doen is dat ik ga kijken naar waar die maand begint en twee cellen, twee cellen, twee tekens terug , twee karakters. En kijk of dat een nummer is, niet zo. Dat is mijn column hier genaamd Adjust2. Aanpassen 2. En hier is wat we gaan doen. Ik ga zeggen, neem het MIDDEN van A2 en begin het waar in G2-2 voor een lengte van 1, tel er 0 bij op en vraag, is dat een getal of niet? Oké, dat is dus een nummer. En dan kijken we ook naar de situatie waarin het een 2-cijferige datum is, dus 20 januari. Dus dat heet Adjust3, ga 3 karakters terug vanaf waar. Dus daar is de Waar, ga drie tekens terug voor een lengte van 1, tel er 0 bij op en kijk of dat 'is een nummer, oké? Dan gaan we aanpassen en de Aangepaste Waar zegt IF. ALS het rare geval 0 was, gaan we gewoon een heel grote waarde 999 plaatsen; anders gaan we van G2 en ofwel terug 3, als Adjust3 waar is, of terug 2 als Adjust2 waar is, of als geen van deze waar is, de Waar zal zijn waar de maand begint. Oké, nu we weten dat die Aangepaste Waar, zullen we dubbelklikken om dat naar beneden te kopiëren. Nou, hey nu, het is heel gemakkelijk. We gaan gewoon - voor de titel gaan we zeggen: neem links van A2, hoeveel tekens we willen. We willen D2-1 omdat dat de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.ALS dit rare geval 0 was, gaan we gewoon een heel grote waarde 999 plaatsen; anders gaan we van G2 en gaan we terug naar 3, als Adjust3 waar is, of gaan we terug naar 2 als Adjust2 waar is, of als geen van deze waar is, wordt de Waar zal zijn waar de maand begint. Oké, nu we weten dat die Aangepaste Waar, zullen we dubbelklikken om dat naar beneden te kopiëren. Nou, hey nu, het is heel gemakkelijk. We gaan gewoon - voor de titel gaan we zeggen: neem links van A2, hoeveel tekens we willen. We willen D2-1 omdat dat de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.ALS het rare geval 0 was, gaan we gewoon een heel grote waarde 999 plaatsen; anders gaan we van G2 en gaan we terug naar 3, als Adjust3 waar is, of gaan we terug naar 2 als Adjust2 waar is, of als geen van deze waar is, wordt de Waar zal zijn waar de maand begint. Oké, nu we weten dat die Aangepaste Waar, zullen we dubbelklikken om dat naar beneden te kopiëren. Nou, hey nu, het is heel gemakkelijk. We gaan gewoon - voor de titel gaan we zeggen: neem links van A2, hoeveel tekens we willen. We willen D2-1 omdat dat de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.of als geen van deze waar is, de Waar zal zijn waar de maand begint. Oké, nu we weten dat die Aangepaste Waar, zullen we dubbelklikken om dat naar beneden te kopiëren. Nou, hey nu, het is heel gemakkelijk. We gaan gewoon - voor de titel gaan we zeggen: neem links van A2, hoeveel tekens we willen. We willen D2-1 omdat dat de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.of als geen van deze waar is, de Waar zal zijn waar de maand begint. Oké, nu we weten dat die Aangepaste Waar, zullen we dubbelklikken om dat naar beneden te kopiëren. Nou, hey nu, het is heel gemakkelijk. We gaan gewoon - voor de titel gaan we zeggen: neem links van A2, hoeveel tekens we willen. We willen D2-1 omdat dat de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.s de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.s de -1 is om de spatie aan het einde te verwijderen. Hoewel ik denk dat de TRIM aan het einde ook de ruimte aan het wegwerken is.
En dan gaan we voor de datum de MID gebruiken. MIDDEN voor MID van A2 beginnend bij de Aangepaste Waar in D2 en ga uit 50 of wat je ook denkt dat het zou kunnen zijn, en dan de TRIM-functie, en we zullen dubbelklikken om dat naar beneden te kopiëren.
Oké, de reden dat ik contact opnam met Mike is dat ik zei: ik vraag me af of er een manier is om deze 12 kolommen te vervangen door een enkel formulier, eigenlijk deze 13 kolommen door een enkel formulier. Is er een manier waarop ik dit kan doen met behulp van een matrixformule? En Mike schreef natuurlijk dat geweldige boek, Ctrl + Shift + Enter, over matrixformules. En ik probeerde een paar verschillende dingen en in mijn gedachten was er geen manier om het te doen. Oké, maar we gaan het aan de expert vragen. Mike, laten we eens kijken wat je hebt.
Mike Girvin: Bedankt ,. Hé, en over expert gesproken, dit was behoorlijk vakkundig gedaan. Je gebruikte FIND, AGGREGATE, ISNUMBER (MID. Nu, toen je deze vraag naar mij stuurde, ging ik verder en loste het op en het is verbazingwekkend hoe vergelijkbaar mijn oplossing is met de jouwe.
Alright, I'm going to go over to this sheet here. I'm going to start with figuring out where the start position in this text string is for each particular month. Now the way I'm going to do it is I'm going to, hey, use this SEARCH function. Now, you used FIND, I use SEARCH. Actually probably FIND is better in this situation because FIND is case-sensitive, SEARCH is not. Now normally what we do with either FIND or SEARCH, I say, hey, go FIND, January, comma within this larger text string, that's how we normally use SEARCH Ctrl+Enter, and it counts on its finger: one, two, three, four, five. It says the 32nd character is where it found January.
Now, instead of doing it in many cells across the columns, I'm going to hit F2, come up here and the FIND_TEXT. Notice we gave it 1 item, SEARCH gave us 1 answer. But if I highlight the entire column of month names, now instead of a single item I put many items in there. This is a Function Argument. We're putting an array of items in and so that means we're doing a Function Argument Array operation. Any time you do that, you tell the function, hey, give me 12 answers, 1 for each month. Now this will deliver an array so if I try to Enter this and copy down it's not going to work.
Well, let's go down to any particular cell, F2 and then F9 to look that yes, in fact, it is delivering an array, and look at that. It looks like I F2 up here, forgot to lock it. So I'm going to click on that and F4, Ctrl+Enter, double click and send it down, F2, F9. There we go, that's that array. There's exactly 12 answers and there's the 34 and the 55. Now, from this array, since we always want the actual first month, not the second month, we want whatever the MIN is because those are number of characters in from the left. So I'm going to click Escape, come up to the top F2. I'm going to use the AGGREGATE function. Hey, we would like to use AGGREGATE 5 but no matter how hard you try if you have an array operation and we do here, if you try to put any function 1 to 13, it just doesn't work. But no problem, we have SMALL, so number 15 comma. Any one of those functions 14 to 19, they understand array operations. And once you select 14 or above, this is the screen tip you're working off, not this bottom one with the references. Alright, comma.
The second options here we want to Ignore errors, comma. That number 6 will then tell AGGREGATE to look through here and ignore the errors. It will only see the numbers. And this is one of five functions in Excel: LOOKUP some product, CHI SQUARE TEST, AGGREGATE, and INDEX that actually have a special argument that can handle Array operations without doing any special key stroke. So there is the Array, comma, and then for K we simply put A1. That's our way of getting them in. Close parentheses, Ctrl+Enter, double click and send it down. And so that tells us the position where it found the first month name from this list.
Now, we'll deal with the NUM error at the very end in our final formula. Now, we are going to have to take these and notice that sometimes there's a number before the month and sometimes, like down here in December, there is not. So I'm going to do the same thing did. I'm going to go back two characters and check whether it is a letter or in this case a number =MID, there’s the text, comma, the starting position. Well, I want to start at 32 in this case and -2 to go back to and comma. I get exactly one character. Now, if I close parenthesis MID LEFT RIGHT they all deliver text, double click and send it down and we want to check if it's a number. So watch this, the whole column is highlighted. Active cell at the top, I'm going to hit F2. We could do any Math operation to convert text numbers back to number so I'm going to add 0, Ctrl+Enter to populate this edited formula down through the column. Ctrl+Enter. Now, we can ask the question: Is the returned item a number? F2. So now I say ISNUMBER, close parenthesis, Ctrl+Enter. So now we have a pattern of Trues and Falses. Now, remember we need to get the starting position and for 32 we're definitely going to have to subtract 3 and start at that 20 but notice down here, we don't want to subtract any. So our logical test if I hit F2, that will simply be put into the IF Logical Test Argument. If that comes out True comma then I want to jump back 3 comma. Otherwise I want to jump back 0, close parenthesis, Ctrl+Enter to populate that all the way down. Now we can take this number and subtract the number over here to give us our starting position. Active cell at the top F2, I'm putting this inside of MID. There's the text, comma. And can you believe it? All of this to get the start number. So I'm going to click on that B2 and subtract our IF, come to the end comma and I'm just going to put a big number in here, 100, some big number big enough to get all the way to the end, close parenthesis and Ctrl+Enter to populate that all the way down. It looks like we have some extra spaces and that makes sense because right here we went back three, so no problem. Active cell at the top, F2, I'm going to use the haircut function, the diet function. No, the TRIM function to remove extra spaces except for single spaces between words. Come to the end, close parenthesis, Ctrl+Enter to populate that all the way down.
Now, I have the date, oh, except for the NUM. Now, I could come to the top and use IF error but then it would run all of these plus that cell right there and for a small data set, it doesn't matter at all; but, with the goal of efficiency, I'm going to say IF(ISNUMBER and I'm going to click on that cell, that way close parenthesis, comma. The trigger for whether we run the formula is only based on that instead of the entire formula. If that comes out True, we want to run the formula, comma. Otherwise, double quote double quote. That zero link text string will show nothing. Ctrl+Enter, double click and send it down. And now, all we need to do is get the Title. Well, I already have the text that I don't want in here so I'm going to use the SUBSTITUTE function. SUBSTITUTE, there's the text, comma. The old text, it's that right there, comma, the new text. Hey, I want to take that and SUBSTITUTE in nothing. There's our zero link text string, Ctrl+Enter, double click and send it down.
Now, I'm going to come over here to column B, right click, Hide and there we go. Alright, throw it back to.
Bill Jelen: Hey, Mike, that is brilliant and I know exactly, exactly where I went wrong. Right here in row 12 when the formula returned the #VALUE error, you pressed F2, F9 to see that it's returning an array. When I got the #VALUE error, I just swore a little bit and said, why isn't this working? Never thought of pressing F2, F9, alright. Also, like that, of course, MIN and SMALL(,1) are the same but the difference is SMALL(,1) will work with an array in the AGGREGATE function. That was a beautiful, beautiful trick. And then, I went through that whole hassle to look at 2 characters before and 3 characters before. You were smart enough to say, “Hey, we're going to go 2 characters before and if so, go back 3 characters.” Worst case you get a space for that extra space and eliminated by the TRIM. And then the cherry on top, using SUBSTITUTE function to get rid of the Date text in column C. What a brilliant, brilliant way to go, alright.
Dus ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor nog een Dueling Excel Podcast van en Excel Is Fun.
Download bestand
Download het voorbeeldbestand hier: Duel180.xlsm
