Några av de frågor som kommer in är ganska svåra. Idag har vi en kolumn med celler. Varje cell har några ord, sedan ett datum, sedan några fler ord. Målet är att dra datumdelen av texten till en ny kolumn. Detta är en duelleringsepisod med idéer från Bill och Mike.
Kolla på video
- Bills superbrett synsätt:
- Lägg alla 12 månader i separata kolumner
- Använd HITTA-funktionen för att se om den här månaden finns i originaltexten
- För att hitta minsta startposition, använd = AGGREGATE (5,6,…
- Några extra formler för att leta efter nummer 2 eller 3 positioner före månaden
- Mike's strategi:
- Använd SÖK i stället för HITTA. Sök är skiftlägeskänsligt, sökning är inte.
- Skapa en funktionsargument array-operation genom att ange B13: B24 som Find_Text.
- Formeln returnerar #VÄRDE! Fel, men om du trycker på F2, F9 ser du att den returnerar en matris.
- De första 13 funktionerna i AGGREGATE kan inte hantera en array, men funktionerna 14-19 kan hantera en array.
- 5 = MIN och 15 = SMALL (, 1) är lika, men SMALL (, 1) fungerar med en array.
- LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX och AGGREGATE kan hantera funktionsarrayargument utan Ctrl + Shift + Enter
- Mike var smartare genom att se om två tecken före Start är ett nummer och sedan ta 3 tecken innan. Det extra utrymmet elimineras av TRIM ()
- För att få titeln, använd SUBSTITUTE-funktionen för att bli av med datumtexten i kolumn C
Videoutskrift
Bill Jelen: Hej, välkommen tillbaka. Det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från. Jag kommer med av Mike Girvin från Excel är kul.
Detta är vår duell nr 170: Hitta datum
Hej, välkommen tillbaka alla. Jag hade en så bra fråga här och jag kunde inte lösa det. Jag kunde åtminstone inte lösa det lätt så jag gick ut till Mike Girvin och sa: "Mike, hej, har du ett sätt att göra det här?" Han sa, ”Ja, jag har ett sätt att göra det. Låt oss göra en duell. ”
Så någon på YouTube skickade in dessa uppgifter och varje cell i allmänhet har något som en dokumenttitel följt av ett datum. De ville dela upp dessa uppgifter i dokumentets titel: vad det är, vad saken är och sedan vad datumet är. Men datumen är helt onda. Som här är det 20 januari; men här nere finns det saker där datumet kan vara efter cellen, 9 april. Okej, och oavsett vilket sätt det är vill vi hitta det. Och ibland finns det två datum och det här är helt hemskt och att det är en sådan blandad situation med datum och som möjligt, inte ens ha ett datum dyker upp, okej. Så här är mitt försök. På höger sida ska jag lägga de saker jag letar efter. Vad jag verkligen gillar här är att de aldrig förkortade månadens namn. Jag verkligen,verkligen uppskattar det. Så skriv i januari så drar jag hit till december så, och för varje cell jag vill veta kan vi hitta = HITTA den januari. Så jag ska trycka på F4 en, två gånger för att låsa ner den till bara en rad, i texten där borta i kolumn A, så. Jag trycker på F4 en, två, tre gånger för att låsa ner den i kolumnen, okej. Och här berättar det för oss att januari finns i Position 32 och för de andra 11 månaderna kommer det att berätta att det inte finns alls. Med andra ord får vi värdefelet nu. Vad jag behöver göra där är att jag måste hitta, jag måste hitta minimivärdet och ignorera alla värdefel. Så, ta bort den här lilla formeln här = AGGREGATE och låt oss bygga den här helt från grunden, = AGGREGATE, vad vi vill ha är MIN så det är siffran 5,och sedan ignorera felvärdena 6 kommatecken och sedan alla dessa celler från januari till december. Och vad som kommer att berätta för oss är att det kommer att berätta var månaden händer. Och i det här fallet kommer vi att få 0, säg att månaden inte händer alls.
Okej nu, låt oss ta bort resten av detta. Så för att hantera situationen där vi har den 20 januari eller 1 november sa jag att det första jag ska göra är att jag ska titta på var den månaden börjar och gå tillbaka två celler, två celler, två tecken , två tecken. Och se om det är ett nummer, inte så. Det är min kolumn här, Adjust2. Justera2. Och här är vad vi ska göra. Jag ska säga, ta MID för A2 och starta den där i G2-2 för en längd på 1, lägg till 0 till den och fråga, är det ett nummer eller inte? Okej, så är det ett nummer. Och sedan ska vi också leta efter situationen där det är ett tvåsiffrigt datum, så den 20 januari. Så det kallas Adjust3, gå tillbaka 3 tecken från var. Så det finns Where, gå tillbaka tre tecken för längden 1, lägg till 0 till det och se om det 'ett nummer, okej? Då ska vi justera och justerat där säger IF. OM det är detta konstiga fall var 0, kommer vi bara att sätta ett riktigt stort värde 999; annars kommer vi att gå från G2 och antingen gå tillbaka 3, om Adjust3 är sant eller gå tillbaka 2 om Adjust2 är sant, eller om ingen av dem är sant, kommer Where att vara där månaden börjar. Okej, nu när vi vet att det justerade var, dubbelklickar vi för att kopiera det. Tja, hej nu, det är väldigt enkelt. Vi ska bara - för titeln kommer vi att säga ta vänster om A2, hur många tecken vi vill ha. Vi vill ha D2-1 eftersom det är -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.OM det är detta konstiga fall var 0, kommer vi bara att sätta ett riktigt stort värde 999; annars kommer vi att gå från G2 och antingen gå tillbaka 3, om Adjust3 är sant eller gå tillbaka 2 om Adjust2 är sant, eller om ingen av dem är sant, kommer Where att vara där månaden börjar. Okej, nu när vi vet att det justerade var, dubbelklickar vi för att kopiera det. Tja, hej nu, det är väldigt enkelt. Vi ska bara - för titeln kommer vi att säga ta vänster om A2, hur många tecken vi vill ha. Vi vill ha D2-1 eftersom det är -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.OM det är detta konstiga fall var 0, kommer vi bara att sätta ett riktigt stort värde 999; annars kommer vi att gå från G2 och antingen gå tillbaka 3, om Adjust3 är sant eller gå tillbaka 2 om Adjust2 är sant, eller om ingen av dem är sant, kommer Where att vara där månaden börjar. Okej, nu när vi vet att det justerade var, dubbelklickar vi för att kopiera det. Tja, hej nu, det är väldigt enkelt. Vi ska bara - för titeln kommer vi att säga ta vänster om A2, hur många tecken vi vill ha. Vi vill ha D2-1 eftersom det är -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.eller om inget av dessa är sant, kommer var att vara där månaden börjar. Okej, nu när vi vet att det justerade var, dubbelklickar vi för att kopiera det. Tja, hej nu, det är väldigt enkelt. Vi ska bara - för titeln kommer vi att säga ta vänster om A2, hur många tecken vi vill ha. Vi vill ha D2-1 eftersom det är -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.eller om inget av dessa är sant, kommer var att vara där månaden börjar. Okej, nu när vi vet att det justerade var, dubbelklickar vi för att kopiera det. Tja, hej nu, det är väldigt enkelt. Vi ska bara - för titeln kommer vi att säga ta vänster om A2, hur många tecken vi vill ha. Vi vill ha D2-1 eftersom det är -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.s -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.s -1 är att bli av med utrymmet i slutet. Även om jag antar att TRIM också blir av med utrymmet i slutet.
Och sedan för datumet ska vi använda MID. MID för - MID av A2 som börjar vid Justerat Var i D2 och går ut 50 eller vad som helst som du tror det skulle kunna vara, och sedan TRIM-funktionen, och vi dubbelklickar för att kopiera ner det.
Okej nu, anledningen till att jag kontaktade Mike är att jag sa, jag undrar om det finns ett sätt att jag kunde ersätta dessa 12 kolumner med en enda form, faktiskt dessa 13 kolumner med en enda form. Finns det något sätt att göra detta med en matrisformel? Och Mike skrev naturligtvis den stora boken, Ctrl + Shift + Enter, om Array-formler. Och jag försökte några olika saker och i mina tankar fanns det inget sätt att det kunde göras. Okej, men du vet, låt oss fråga experten. Så Mike, låt oss se vad du har.
Mike Girvin: Tack. Hej, och talar om expert, detta var ganska expertist gjort. Du använde HITTA, AGGREGATE, ISNUMBER (MID. Nu när du skickade den här frågan till mig gick jag vidare och löste den och det är fantastiskt hur min lösning liknar din.
Okej, jag ska gå över till det här bladet här. Jag ska börja med att ta reda på var startpositionen i denna textsträng är för varje månad. Nu hur jag ska göra det ska jag, hej, använda den här SÖK-funktionen. Nu använde du HITTA, jag använder SÖK. Faktiskt är FIND bättre i den här situationen eftersom FIND är skiftlägeskänslig, SÖK inte. Nu normalt vad vi gör med antingen HITTA eller SÖKA, säger jag, hej, gå HITTA, januari, komma inom denna större textsträng, det är så vi normalt använder SÖK Ctrl + Enter, och det räknas på fingret: en, två, tre , fyra fem. Det står att den 32: e karaktären är där den hittade januari.
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å jag vill tacka alla för att de kom in. Vi ses nästa gång för en annan Duelling Excel Podcast från och Excel är kul.
Nedladdning fil
Ladda ner exempelfilen här: Duel180.xlsm
