Nogle af de spørgsmål, der kommer ind, er ret vanskelige. I dag har vi en søjle af celler. Hver celle har nogle ord, så en dato, så nogle flere ord. Målet er at trække datodelen af teksten til en ny kolonne. Dette er en duelleringsepisode med ideer fra Bill og Mike.
Se video
- Bills superbrede tilgang:
- Sæt alle 12 måneder i separate kolonner
- Brug FIND-funktionen til at se, om denne måned er i originalteksten
- For at finde den minimale startposition skal du bruge = AGGREGATE (5,6,…
- Et par ekstra formler til at lede efter et nummer 2 eller 3 positioner inden måneden
- Mike's tilgang:
- Brug SØG i stedet for FIND. Find er store og små bogstaver, søgning er ikke.
- Opret en funktionsargument array-operation ved at angive B13: B24 som Find_Text.
- Formlen returnerer #VÆRDI! Fejl, men hvis du trykker på F2, F9, vil du se, at den returnerer en matrix.
- De første 13 funktioner i AGGREGATE kan ikke håndtere et array, men funktionerne 14-19 kan håndtere et array.
- 5 = MIN og 15 = SMALL (, 1) er ens, men SMALL (, 1) fungerer med en matrix.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX og AGGREGATE kan håndtere funktionsarrayargumenter uden Ctrl + Shift + Enter
- Mike var klogere ved at se efter, om 2 tegn før Start er et tal, og derefter gribe 3 tegn før. Den ekstra plads elimineres af TRIM ()
- For at få titlen skal du bruge SUBSTITUTE-funktionen til at slippe af med datoteksten i kolonne C
Videoudskrift
Bill Jelen: Hej, velkommen tilbage. Det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg bliver med af Mike Girvin fra Excel Is Fun.
Dette er vores duel nr. 170: Find datoer
Hej, velkommen tilbage alle sammen. Jeg havde et så stort spørgsmål her, og jeg kunne ikke løse det. I det mindste kunne jeg ikke løse det let, så jeg gik ud til Mike Girvin, og jeg sagde: "Mike, hej, har du en måde at gøre dette på?" Han sagde, ”Ja, jeg har en måde at gøre det på. Lad os lave en duel. ”
Så nogen på YouTube sendte disse data ind, og hver eneste celle generelt har noget som en dokumenttitel efterfulgt af en dato. De ønskede at opdele disse data i dokumentets titel: hvad det er, hvad der er, og derefter hvad datoen er. Men datoerne er helt onde. Som her er det 20. januar; men hernede er der ting, hvor datoen kan være efter cellen, 9. april. Okay, og uanset hvilken vej det er, vil vi finde det. Og nogle gange er der to datoer, og dette er bare helt forfærdeligt, og at det er sådan en bare en blandet situation med datoer og som muligt ikke engang have en dato, ok. Så her er mit forsøg. Ude på højre side vil jeg lægge de ting, jeg leder efter. Hvad jeg virkelig kan lide her er, at de aldrig forkortede månedsnavnet. Jeg virkeligvirkelig sætter pris på det. Så skriv i januar, så trækker jeg her ud til december sådan, og for hver celle, jeg vil vide, kan vi finde = FIND den januar. Så jeg skal trykke på F4 en, to gange for at låse den ned til bare en række i teksten derovre i kolonne A sådan. Jeg trykker på F4 en, to, tre gange for at låse den ned til søjlen, okay. Og her fortæller det os, at januar findes i position 32, og at de andre 11 måneder vil det fortælle os, at det slet ikke findes. Med andre ord får vi værdi-fejlen nu. Hvad jeg skal gøre der er, at jeg skal finde, jeg skal finde den mindste værdi, der ignorerer alle værdifejlene. Så skjul denne lille formel her = AGGREGATE, og lad os bygge dette lige fra bunden, = AGGREGATE, hvad vi ønsker er MIN, så det er tallet 5,og derefter ignorere fejlværdierne nummer 6 komma og derefter alle disse celler fra januar til december. Og hvad det vil fortælle os, er det, der fortæller os, hvor måneden sker. Og i dette tilfælde får vi 0, siger, at måneden slet ikke sker.
Okay nu, lad os fjerne resten af dette. Så for at håndtere den situation, hvor vi har 20. januar eller 1. november, sagde jeg, at det første jeg skal gøre, er at jeg vil se på, hvor den måned starter og gå tilbage to celler, to celler, to tegn , to tegn. Og se om det er et tal, ikke så. Det er min kolonne, der hedder Adjust2. Juster2. Og her er hvad vi skal gøre. Jeg vil sige, tag MID af A2, start det hvor i G2-2 i en længde på 1, tilføj 0 til det og spørg, er det et tal eller ej? Okay, så er det et tal. Og så ser vi også efter situationen, hvor det er en tocifret dato, så den 20. januar. Så det kaldes Adjust3, gå 3 tegn tilbage fra hvor. Så der er Hvor, gå tre tegn tilbage i længden på 1, tilføj 0 til det, og se om det 'et nummer, okay? Så skal vi justere, og det Justerede Hvor siger HVIS. HVIS det er denne underlige sag, der var 0, lægger vi bare en meget stor værdi på 999; Ellers vil vi gå fra G2 og enten gå tilbage 3, hvis Adjust3 er sandt eller gå tilbage 2, hvis Adjust2 er sandt, eller hvis ingen af dem er sande, hvor vil der være, hvor måneden starter. Okay, nu hvor vi ved, at det justerede hvor, dobbeltklikker vi for at kopiere det ned. Nå, hej nu, det er virkelig let. Vi skal bare - for titlen vil vi sige tage til venstre for A2, hvor mange tegn vi vil have. Vi vil have D2-1, fordi det er -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.HVIS det er denne underlige sag, der var 0, lægger vi bare en meget stor værdi på 999; Ellers vil vi gå fra G2 og enten gå tilbage 3, hvis Adjust3 er sandt eller gå tilbage 2, hvis Adjust2 er sandt, eller hvis ingen af dem er sande, hvor vil der være, hvor måneden starter. Okay, nu hvor vi ved, at det justerede hvor, dobbeltklikker vi for at kopiere det ned. Nå, hej nu, det er virkelig let. Vi skal bare - for titlen vil vi sige tage til venstre for A2, hvor mange tegn vi vil have. Vi vil have D2-1, fordi det er -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.HVIS det er denne underlige sag var 0, vil vi bare lægge en virkelig stor værdi på 999; Ellers vil vi gå fra G2 og enten gå tilbage 3, hvis Adjust3 er sandt eller gå tilbage 2, hvis Adjust2 er sandt, eller hvis ingen af dem er sande, hvor vil der være, hvor måneden starter. Okay, nu hvor vi ved, at det justerede hvor, dobbeltklikker vi for at kopiere det ned. Nå, hej nu, det er virkelig let. Vi skal bare - for titlen vil vi sige tage til venstre for A2, hvor mange tegn vi vil have. Vi vil have D2-1, fordi det er -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.eller hvis ingen af disse er sande, hvor vil der være hvor måneden starter. Okay, nu hvor vi ved, at det justerede hvor, dobbeltklikker vi for at kopiere det ned. Nå, hej nu, det er virkelig let. Vi skal bare - for titlen vil vi sige tage til venstre for A2, hvor mange tegn vi vil have. Vi vil have D2-1, fordi det er -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.eller hvis ingen af disse er sande, hvor vil der være hvor måneden starter. Okay, nu hvor vi ved, at det justerede hvor, dobbeltklikker vi for at kopiere det ned. Nå, hej nu, det er virkelig let. Vi skal bare - for titlen vil vi sige tage venstre til A2, hvor mange tegn vi vil have. Vi vil have D2-1, fordi det er -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.s -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.s -1 er at slippe af med pladsen i slutningen. Selvom jeg gætter på, at TRIM også slipper af pladsen i slutningen.
Og så til datoen skal vi bruge MID. MID for- MID af A2 startende ved Justeret hvor i D2 og gå ud 50 eller hvad som helst langs, du tror det kunne være, og derefter TRIM-funktionen, og vi dobbeltklikker for at kopiere det ned.
Okay, grunden til, at jeg nåede ud til Mike, sagde jeg, jeg spekulerer på, om der er en måde, hvorpå jeg kunne erstatte disse 12 kolonner med en enkelt form, faktisk disse 13 kolonner med en enkelt form. Er der en måde, hvorpå jeg kunne gøre dette ved hjælp af en Array-formel? Og Mike skrev selvfølgelig den store bog, Ctrl + Shift + Enter, om Array-formler. Og jeg prøvede et par forskellige ting, og efter min mening var der ingen måde, det kunne gøres på. Okay, men ved du, lad os spørge eksperten. Så Mike, lad os se hvad du har.
Mike Girvin: Thanks,. Hey, and speaking of expert, this was pretty expertly done. You used FIND, AGGREGATE, ISNUMBER(MID. Now, when you sent this question over to me, I went ahead and solved it and it is amazing how similar my solution is to yours.
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.
Så jeg vil gerne takke alle for at komme forbi. Vi ses næste gang til en anden Dueling Excel Podcast fra og Excel er sjovt.
Download fil
Download eksempelfilen her: Duel180.xlsm
