Een kolom met Excel-gegevens scheiden in twee kolommen. Gegevens in Excel parseren.
Bekijk video
- Bill's eerste methode met behulp van Tekst naar kolommen (te vinden op het tabblad Gegevens).
- Kies in stap 1 gescheiden. Kies in stap 2 een spatie. Sla stap 3 over door op Voltooien te klikken.
- De tekst wordt op elke spatie gesplitst, dus alles met drie woorden komt in drie cellen terecht. Zet die weer in elkaar met
=TEXTJOIN(" ",True,B2:E2)of - met
=B2&" "&C2&" "&D2 - Mike's eerste methode maakt gebruik van Power Query. Power Query is Ophalen en transformeren in 2016 of een gratis download voor 2010 of 2013.
- Converteer eerst uw gegevens naar een tabel met Ctrl + T.Vervolgens, in Power Query, vanuit Table. Kolom splitsen, door scheidingsteken. Selecteer Spatie en vervolgens op het meest linkse scheidingsteken.
- U kunt een kolom een andere naam geven door te dubbelklikken!
- Sluiten & laden naar … en kies een nieuwe plek op het werkblad.
- Bill's tweede methode is om Flash Fill te gebruiken. Typ nieuwe koppen in A, B en C. Snel invullen werkt niet als u geen koppen heeft! Typ een patroon voor de eerste twee rijen.
- Ga naar de eerste lege cel in B en druk op Ctrl + E. Herhaal voor kolom C.
- Mike's tweede methode is om deze formules te gebruiken:
- Gebruik voor het eerste deel
=LEFT(A2,SEARCH(" ",A2)-1) - Gebruik voor het tweede deel
=SUBSTITUTE(A2,B2&" ","")
Videotranscriptie
(Muziek)
Bill Jelen: Hey, welkom terug, het is tijd voor weer een Dueling Excel Podcast. Ik ben Bill Jelen van. Ik krijg gezelschap van Mike Girvin van Excel Is Fun. Dit is onze
Aflevering 182: Gegevens splitsen van één cel om in twee cellen te verschijnen.
Oké, de vraag van vandaag is ingezonden door Tom. Is er een manier om de gegevens eenvoudig in één cel te splitsen om de gegevens in twee cellen te laten verschijnen? Bijvoorbeeld, 123 Main Street, hij wil 123 in de ene cel en Main Street in een andere cel; of, Howard en Howard en dan End. Ik heb talloze uren besteed aan het scheiden van dit soort gegevens. Ik zou het op prijs stellen om van uw bedrijf te horen, aangezien er veel, veel verschillende manieren zijn om dit te doen.
Het eerste dat ik ga doen, is alle gegevens selecteren, Ctrl + Shift + Pijl-omlaag en vervolgens Gegevens, Tekst naar kolommen. Tekst naar kolommen in stap 1, de gegevens worden gescheiden. Het wordt afgebakend door een spatie en klik vervolgens op Voltooien. Het probleem met deze methode is dat als je 123 Main Street hebt, het in 3 cellen terechtkomt in plaats van in 2. Oh, Power Query zou dit zoveel gemakkelijker maken, maar hier zijn we dan. Oké, dus wat ik ga doen, is dat ik ver naar de rechterkant van de gegevens uitkom, waar ik weet dat verder dan waar alles is gebouwd. Als ik in Office 365 ben, ga ik TEXTJOIN gebruiken. TEXTJOIN, dat geweldige ding, scheidingsteken is een spatie. Negeer lege cellen Waar en dan de cellen die ik zo aan elkaar wil samenvoegen, en ik kopieer ze allemaal naar beneden, Ctrl + V. Ik kopieer Ctrl + C en dan Home, Plakken,Plak als waarden en op dit punt kan ik deze 3 extra kolommen verwijderen.
Ahh, maar niemand heeft Office 365, toch? Dus als je geen Office 365 hebt, moet je doen = dit ding & "" & dat, en als er meer "" & dat waren, en als er meer waren, ga dan door. In dit geval is het zinloos, want er staat niets over in D, maar je snapt het wel. Ctrl + C, kopieer het naar de laatste rij met gegevens, Ctrl + V en vervolgens Ctrl + C, Alt + ESV om die B-waarden te maken. En daar zijn we dan, oké. Mike laat eens zien wat je hebt.
Mike Girvin: Bedankt ,. Hé, je hebt me hier een gemakkelijke gegeven omdat je het al had gehad over Get & Transform Power Query, met de oude Tekst naar kolommen kun je alleen een spatie bij elk teken zeggen, toch? Als we Power Query gebruiken, kunnen we dat scheidingsteken gebruiken en zeggen: "Hé, splits gewoon bij de eerste keer dat het voorkomt."
Om deze gegevens nu in de Query-editor te krijgen, moeten we deze naar een Excel-tabel converteren. Dus ik ga naar Invoegen, Tabel of ik gebruik Ctrl + T. Mijn tabel heeft kopteksten, de OK-knop is gemarkeerd, dus ik kan erop klikken met mijn muis of gewoon op Enter drukken. Nu wil ik deze tabel een naam geven, dus ik ga hier naar boven komen, OriginalData en Enter. Dit is nu een Excel-tabel, we kunnen naar Data komen en daar is het From Table. Dat brengt het van Excel naar de editor. De kolom is geselecteerd: Home Ribbon Tab, we kunnen zeggen Split Column by Delimiter of kom hierheen en klik met de rechtermuisknop, Split Column by Delimiter. Vanuit de vervolgkeuzelijst kunnen we zeggen, hé, gebruik een spatie en bekijk dit bij het meest linkse scheidingsteken. Als ik op OK klik, BOOM! Daar is het. Nu ga ik beide kolommen een naam geven: dubbelklik op Part 1 Enter, dubbelklik op Part 2 en Enter. Nu,Ik kan hier komen of Close & Load, Close & Load To en ik kan kiezen waar ik dit wil plaatsen. Ik wil het zeker dumpen als een tabel, nieuw werkblad, bestaand werkblad. Markeer dit, klik op de knop voor samenvouwen. Ik zeg D1, klik op OK en vervolgens op Laden. En daar gaan we, onze Power Query Output.
Oké, gooi terug naar.
Bill Jelen: Oh, Mike, Power Query is geweldig! Ja, dat is een geweldige manier om te gaan. Hier is er nog een die het zou kunnen werken als u Excel 2013 of nieuwer heeft.
En wat we gaan doen is hier naar buiten komen en het eerste deel en dan het tweede deel zeggen. Zorg ervoor dat u deze koppen plaatst, zodat als u die koppen niet invoert, ze dat niet hoeven te zijn, maar ze moeten koppen hebben, anders gaat het niet werken. Ik zet 123 en Main Street en dan Howard en End, zo. Nu we daar een leuk klein patroon hebben, kom hier naar voren in de Data Tab en Flash Fill, dat is Ctrl + E, druk op Ctrl + E daar en druk dan op Ctrl + E daar. Het mooie is dat we gegevens niet hoeven samen te voegen zoals in mijn voorbeeld. Goed, Mike, terug naar jou.
Mike Girvin: Ding-ding-ding. Dat is zonder twijfel de winnaar. Flash Fill is de manier om daar naartoe te gaan. Merk op, we hoefden het niet naar een tabel te converteren of een dialoogvenster te openen; typte gewoon een paar voorbeelden en vervolgens Ctrl + E.
Oké, we zouden het kunnen doen met formules, ook al zou Flash Fill waarschijnlijk sneller zijn. Kijk hier eens naar, het patroon dat net als deze lijstcel wordt gebruikt in Flash Fill is alles voor de eerste spatie en dan alles erna. Dus hey, ik ga de LEFT-functie gebruiken, de tekst staat daar en hoeveel tekens vanaf de linkerkant? Nou, ik ga naar die spatie zoeken - 1 2 3 4 met behulp van de SEARCH-functie, Find Text, spatie en "", daarbinnen. Merk nu op dat Zoeken op zijn vingers zou tellen 1 2 3 4 en dat dat de ruimte zou krijgen die ik wil, die ruimte dus ik -1) Ctrl + Enter, dubbelklik en stuur het naar beneden. Dus dat krijgt altijd alles voor de eerste spatie.
Merk nu op dat we de tekst hier al hebben, zodat ik de functie VERVANGEN kan gebruiken. De tekst die ik ga doornemen is de volledige gegevens, komma, de oude tekst waarnaar ik wil zoeken en dan VERVANGEN. Niets is bijna 1 2 3. Ik wil eigenlijk de spatie toevoegen die ik zojuist in de vorige formule heb gehaald, terug. Nu zoekt het naar 1 2 3, spatie en dan Howard, spatie enzovoort, komma en dan de nieuwe tekst die ik wil vervangen. Wel, om SUBSTITUTE te vertellen dat je het door niets wilt vervangen, zeg je "" geen spatie ertussen, Haakje sluiten en dat zal werken. Ctrl + Enter, dubbelklik en verzend het naar beneden. Oké? Gooi het gewoon terug naar.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking. I've been right-clicking and renaming all this time and being a little annoyed of that. That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
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.
Oké, ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor nog een Dueling Excel Podcast van en Excel is leuk.
Download bestand
Download het voorbeeldbestand hier: Duel182.xlsm
