Hur man separerar en kolumn med Excel-data i två kolumner. Hur man analyserar data i Excel.
Kolla på video
- Bills första metod med text till kolumner (finns på fliken Data).
- Välj avgränsad i steg 1. Välj ett mellanslag i steg 2. Hoppa över steg 3 genom att klicka på Slutför.
- Texten delas upp i varje mellanslag, så allt med tre ord hamnar i tre celler. Sätt tillbaka dem tillsammans med
=TEXTJOIN(" ",True,B2:E2)eller - med
=B2&" "&C2&" "&D2 - Mike första metod använder Power Query. Power Query är Get & Transform 2016 eller en gratis nedladdning för 2010 eller 2013.
- Konvertera först dina data till en tabell med Ctrl + T. Sedan, i Power Query, från Table. Dela kolumn, avgränsare. Välj Mellanslag och sedan längst till vänster.
- Du kan byta namn på en kolumn genom att dubbelklicka!
- Stäng & ladda till … och välj en ny plats i kalkylbladet.
- Bills andra metod är att använda Flash Fill. Skriv nya rubriker i A, B & C. Flash Fill fungerar inte om du inte har rubriker! Skriv ett mönster för de två första raderna.
- Gå till den första tomma cellen i B och tryck på Ctrl + E. Upprepa för kolumn C.
- Mikes andra metod är att använda dessa formler:
- För den första delen, använd
=LEFT(A2,SEARCH(" ",A2)-1) - För den andra delen, använd
=SUBSTITUTE(A2,B2&" ","")
Videoutskrift
(Musik)
Bill Jelen: Hej, välkommen tillbaka, det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från. Jag får sällskap av Mike Girvin från Excel Is Fun. Detta är vår
Avsnitt 182: Dela data från en cell för att visas i två celler.
Okej, dagens fråga skickas in av Tom. Finns det ett sätt att enkelt dela upp data i en cell så att uppgifterna visas i två celler? Till exempel, 123 Main Street, han vill ha 123 i en cell och Main Street i en annan cell; eller, Howard och Howard och sedan End. Jag har tillbringat otaliga timmar på att separera den här typen av data. Jag skulle uppskatta att höra från ditt företag medan det finns många, många olika sätt att göra detta.
Det första jag ska göra är att välja alla data, Ctrl + Skift + nedåtpil och sedan Data, text till kolumner. Text till kolumner i steg 1, data avgränsas. Det avgränsas av ett mellanslag och klicka sedan bara på Slutför. Nu är det besväret med den här metoden att om du har 123 Main Street kommer det att hamna i 3 celler istället för 2 celler. Åh, Power Query skulle göra det så mycket lättare men här är vi. Okej, så vad jag ska göra är att jag kommer ut långt till höger om Data där jag vet att bortom där allt är byggt. Om jag är i Office 365 ska jag använda TEXTJOIN. TEXTJOIN, den fantastiska saken, avgränsare är ett mellanslag. Ignorera tomma celler Sanna och sedan cellerna som jag vill sammanfoga på så sätt, och jag kopierar bara alla dessa ner, Ctrl + V. Jag kopierar Ctrl + C och sedan Hem, Klistra in,Klistra in som värden och nu kan jag ta bort dessa tre extra kolumner.
Ahh, men ingen har Office 365, eller hur? Så om du inte har Office 365 måste du göra = den här saken & "" & det, och om det fanns mer "" och det, och om det var fler, fortsätt. I det här fallet är det meningslöst eftersom det inte finns något över i D men du får idén. Ctrl + C, kopiera den till den sista raden med data, Ctrl + V och sedan Ctrl + C, Alt + ESV för att göra dessa B-värden. Och där är vi, okej. Mike, låt oss se vad du har.
Mike Girvin: Tack. Hej, du lobbade mig lätt här eftersom du redan nämnde Get & Transform Power Query, den gamla texten till kolumner låter dig bara säga ett mellanslag vid varje tecken, eller hur? Tja, om vi använder Power Query kan vi använda den avgränsaren och säga, "Hej, dela bara vid första tillfället."
För att få dessa data till frågeditoren måste vi konvertera dem till en Excel-tabell. Så jag går upp till Infoga, Tabell eller använder Ctrl + T. Mitt bord har rubriker, OK-knappen är markerad så att jag kan klicka på den med min mus eller bara trycka på Enter. Nu vill jag namnge den här tabellen så jag ska komma hit, OriginalData och Enter. Nu är detta en Excel-tabell, vi kan komma till Data och där är det från tabell. Det tar det från Excel till redigeraren. Kolumnen är vald: Startsidans flik, vi kan säga Dela kolumn avgränsare eller komma hit och högerklicka, Dela kolumn efter avgränsare. Från rullgardinsmenyn kan vi säga, hej, använd ett mellanslag och titta på det här längst till vänster. När jag klickar på OK, BOOM! Där är det. Nu ska jag namnge båda dessa kolumner: dubbelklicka på del 1 Enter, dubbelklicka på del 2 och Enter. Nu,Jag kan komma hit eller stäng & ladda, stäng & ladda till och jag kan välja var jag vill lägga det här. Jag vill definitivt dumpa det som en tabell, nytt kalkylblad, befintligt kalkylblad. Markera detta, klicka på kollaps-knappen. Jag ska säga D1, klicka på OK och klicka sedan på Ladda. Och där går vi, vår Power Query Output.
Okej, kasta tillbaka till.
Bill Jelen: Åh, Mike, Power Query är fantastisk! Ja, det är ett bra sätt att gå. Här är en annan som det kan fungera om du har Excel 2013 eller nyare.
Och vad vi ska göra är att komma ut här och säga första delen och sedan andra delen. Se till att ange dessa rubriker att om du inte sätter in dessa rubriker behöver de inte vara det, men de måste ha rubriker, annars kommer det inte att fungera. Jag sätter 123 och Main Street och sedan sätter vi Howard och End, så. Nu när vi har ett fint litet mönster där, kom ut här i fliken Data och Flash Fill som är Ctrl + E, tryck Ctrl + E där och tryck sedan på Ctrl + E där. Det vackra är att vi inte behöver sammanfoga data som i mitt exempel. Rätt, Mike, tillbaka till dig.
Mike Girvin: Ding-ding-ding. Det är vinnaren utan tvekan. Flash Fill är vägen att gå dit. Observera att vi inte behövde konvertera den till en tabell eller öppna någon dialogruta. skrev bara några exempel och sedan Ctrl + E.
Okej, ja, vi skulle kunna göra det med formler även om Flash Fill förmodligen skulle bli snabbare. Titta på det här, mönstret precis som den här listcellen som används i Flash Fill är allt före det första mellanslaget och sedan allt efter. Så hej, jag ska använda VÄNSTER-funktionen, texten finns där och hur många tecken till vänster? Tja, jag ska söka efter det utrymmet - 1 2 3 4 med SÖK-funktionen, Hitta text, mellanslag och “”, inom det. Lägg märke till att Sök skulle räkna på fingrarna 1 2 3 4 och att det skulle komma till det utrymmet som jag vill ha, det utrymmet så jag -1) Ctrl + Enter, dubbelklicka och skicka det ner. Så det får alltid allt innan det första utrymmet.
Lägg märke till att vi redan har texten här så att jag kan använda SUBSTITUTE-funktionen. Texten som jag ska titta igenom är Full Data, komma, den gamla texten jag vill leta efter och sedan SUBSTITUTE. Ingenting är nästan 1 2 3. Jag vill faktiskt lägga till det mellanslag som jag just tog ut i föregående formel, tillbaka i. Nu kommer det att leta efter 1 2 3, mellanslag och sedan Howard, mellanslag och så vidare, kommatecken och sedan den nya texten jag vill ersätta i. Tja, för att säga SUBSTITUTE att du vill ersätta den med ingenting, säger du "" inget mellanrum däremellan, Stäng parentes och det kommer att fungera. Ctrl + Enter, dubbelklicka och skicka ner den. OK? Kasta bara tillbaka det till.
Bill Jelen: Hej! Okej, Mike, båda dina metoder var fantastiska. Låt oss göra en snabb avslutning här. Min första metod med text till kolumner: Steg 1, välj Avgränsad; Steg 2, välj ett mellanslag och klicka sedan på Slutför. Problemet är att om du har flera mellanslag kommer det att hamna i flera celler. Jag måste sätta ihop dem igen. Office 365 TEXTJOIN eller den gamla B2 & “” & C2 och så vidare.
Mike använde Power Query, det är känt som att transformera Excel 2016 eller i tidigare versioner 10 eller 13, du laddar ner det och använder Power Query-fliken. Jag lärde mig till och med något här, men först konverterade du data med Ctrl + T och sedan från tabell, delad kolumn, avgränsare, välj avgränsningsutrymme och sedan omedelbart längst till vänster. Jag visste inte att du kunde byta namn på en kolumn genom att dubbelklicka. Jag har högerklickat och döpt om hela tiden och varit lite irriterad över det. Det kommer att spara mycket tid på mig. Och sedan inte Stäng & Ladda men Stäng & Ladda 2 och välj en ny plats i kalkylbladet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer. Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing. That was awesome.
Okej, jag vill tacka alla för att de kom in. Vi ses nästa gång för en annan Dueling Excel Podcast från och Excel är kul.
Nedladdning fil
Ladda ner exempelfilen här: Duel182.xlsm
