Sådan adskilles en kolonne med Excel-data i to kolonner. Sådan analyseres data i Excel.
Se video
- Bills første metode ved hjælp af tekst til kolonner (findes på fanen Data).
- I trin 1 skal du vælge afgrænset. I trin 2 skal du vælge et mellemrum. Spring trin 3 over ved at klikke på Afslut.
- Teksten opdeles i hvert mellemrum, så alt med tre ord ender i 3 celler. Sæt dem sammen igen med
=TEXTJOIN(" ",True,B2:E2)eller - med
=B2&" "&C2&" "&D2 - Mike's første metode bruger Power Query. Power Query er Get & Transform i 2016 eller en gratis download til 2010 eller 2013.
- Konverter først dine data til en tabel ved hjælp af Ctrl + T. Derefter i Power Query fra Table. Opdel kolonne af delimiter. Vælg Mellemrum og derefter til venstre afgrænsning.
- Du kan omdøbe en kolonne ved at dobbeltklikke!
- Luk & indlæs til … og vælg et nyt sted på regnearket.
- Bills anden metode er at bruge Flash Fill. Indtast nye overskrifter i A, B & C. Flashfyld fungerer ikke, hvis du ikke har overskrifter! Indtast et mønster for de to første rækker.
- Gå til den første tomme celle i B og tryk på Ctrl + E. Gentag for kolonne C.
- Mike's anden metode er at bruge disse formler:
- Brug den første del
=LEFT(A2,SEARCH(" ",A2)-1) - Brug den anden del
=SUBSTITUTE(A2,B2&" ","")
Videoudskrift
(Musik)
Bill Jelen: Hej, velkommen tilbage, det er tid til en anden Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følgeskab af Mike Girvin fra Excel Is Fun. Dette er vores
Afsnit 182: Opdel data fra en celle til at vises i to celler.
Okay, dagens spørgsmål sendes af Tom. Er der en måde til let at opdele dataene i en celle for at få dataene til at vises i to celler? For eksempel, 123 Main Street, han vil have 123 i en celle og Main Street i en anden celle; eller Howard og Howard og derefter End. Jeg har brugt utallige timer på at adskille denne form for data. Jeg ville sætte pris på at høre fra din virksomhed, mens der er mange, mange forskellige måder at gøre dette på.
Den første ting, jeg skal gøre er at vælge alle data, Ctrl + Skift + Pil ned og derefter Data, Tekst til kolonner. Tekst til kolonner i trin 1, dataene er afgrænset. Det afgrænses af et mellemrum, og klik derefter bare på Udfør. Her er besværet med denne metode, at hvis du har 123 Main Street, vil det ende i 3 celler i stedet for 2 celler. Åh, Power Query ville gøre dette så meget lettere, men her er vi. Okay, så hvad jeg skal gøre er, at jeg kommer langt ud til højre for dataene, hvor jeg ved det ud over, hvor alt er bygget. Hvis jeg er i Office 365, skal jeg bruge TEXTJOIN. TEXTJOIN, den fantastiske ting, afgrænser er et mellemrum. Ignorer tomme celler Sandt og derefter cellerne, som jeg vil sammenkæde sådan, og jeg kopierer bare alle disse ned, Ctrl + V. Jeg kopierer Ctrl + C og derefter Hjem, Sæt ind,Indsæt som værdier, og på dette tidspunkt kan jeg slette disse 3 ekstra kolonner.
Ahh, men ingen har Office 365, ikke? Så hvis du ikke har Office 365, skal du gøre = denne ting & "" & det, og hvis der var mere "" & det, og hvis der var flere, skal du fortsætte. I dette tilfælde er det meningsløst, fordi der ikke er noget over i D, men du får ideen. Ctrl + C, kopier det ned til den sidste række data, Ctrl + V og derefter Ctrl + C, Alt + ESV for at lave disse B-værdier. Og der er vi, okay. Mike lad os se, hvad du har.
Mike Girvin: Tak. Hej, du lobbede mig let her, fordi du allerede har nævnt Get & Transform Power Query, den gamle tekst til kolonner giver dig kun mulighed for at sige et mellemrum ved hvert tegn, ikke? Nå, hvis vi bruger Power Query, kan vi bruge den afgrænser og sige, "Hej, bare splitt ved den første begivenhed."
For at få disse data til forespørgselseditoren skal vi konvertere dem til en Excel-tabel. Så jeg går op til Indsæt, Tabel, eller jeg bruger Ctrl + T. Min tabel har overskrifter, OK-knappen er fremhævet, så jeg kan klikke på den med min mus eller bare trykke på Enter. Nu vil jeg navngive denne tabel, så jeg kommer op her, OriginalData og Enter. Nu er dette en Excel-tabel, vi kan komme op til data, og der er den fra tabel. Det bringer det fra Excel til redaktøren. Kolonnen er valgt: Fanen Startbånd, vi kan sige Opdel kolonne efter afgrænsning eller kom herover og højreklik, Opdel kolonne efter afgrænser. Fra rullemenuen kan vi sige, hej, brug et mellemrum og se på dette Til venstre afgrænsning. Når jeg klikker på OK, BOOM! Der er det. Nu skal jeg navngive begge disse kolonner: dobbeltklik på Del 1 Enter, dobbeltklik på Del 2 og Enter. Nu,Jeg kan komme herop eller Luk & indlæs, Luk og indlæs til, og jeg kan vælge, hvor jeg skal placere dette. Jeg vil bestemt dumpe det som en tabel, nyt regneark, eksisterende regneark. Fremhæv dette, klik på knappen Skjul. Jeg siger D1, skal du klikke på OK og derefter klikke på Indlæs. Og der går vi, vores Power Query Output.
Okay, kast tilbage til.
Bill Jelen: Åh, Mike, Power Query er fantastisk! Ja, det er en god vej at gå. Her er en anden, som det muligvis fungerer, hvis du har Excel 2013 eller nyere.
Og hvad vi skal gøre er at komme ud her og sige Første del og derefter Anden del. Sørg for at sætte disse overskrifter, at hvis du ikke sætter disse overskrifter, behøver de ikke være det, men de skal have overskrifter, ellers fungerer det ikke. Jeg lægger 123 og Main Street, og så sætter vi Howard og End sådan. Nu hvor vi har et dejligt lille mønster der, skal du komme ud her i fanen Data og Flash Fill, som er Ctrl + E, tryk Ctrl + E lige der og tryk derefter Ctrl + E lige der. Den smukke ting er, at vi ikke behøver at sammenkæde data sammen som i mit eksempel. Ret, Mike, tilbage til dig.
Mike Girvin: Ding-ding-ding. Det er vinderen uden tvivl. Flash Fill er vejen derhen. Bemærk, at vi ikke behøvede at konvertere det til en tabel eller åbne nogen dialogboks; bare skrev et par eksempler og derefter Ctrl + E.
Okay, godt, vi kunne gøre det med formler, selvom Flash Fill sandsynligvis ville være hurtigere. Nå se på dette, mønsteret ligesom denne listecelle brugt over i Flash Fill er alt før det første mellemrum og derefter alt efter. Så hej, jeg skal bruge VENSTRE-funktionen, teksten er lige der, og hvor mange tegn fra venstre? Nå, jeg vil søge efter det rum - 1 2 3 4 ved hjælp af SØG-funktionen, Find tekst, mellemrum og “” inden for det. Læg nu mærke til, at Søgning vil tælle på fingrene 1 2 3 4, og det ville komme til det rum, jeg vil have, det mellemrum, så jeg -1) Ctrl + Enter, dobbeltklik og send det ned. Så det får altid alt inden det første rum.
Bemærk nu, at vi allerede har teksten her, så jeg kan bruge SUBSTITUTE-funktionen. Den tekst, som jeg vil se igennem, er Fuld data, komma, den gamle tekst, jeg vil se efter, og derefter UDSKIFT. Intet er næsten 1 2 3. Jeg vil faktisk tilføje det mellemrum, som jeg lige tog ud i den foregående formel, tilbage i. Nu vil det se efter 1 2 3, mellemrum og derefter Howard, mellemrum og så videre, komma og derefter den nye tekst, jeg vil erstatte i. Nå, for at fortælle SUBSTITUTE, at du vil erstatte den med ingenting, siger du "" intet mellemrum imellem, Luk parentes, og det fungerer. Ctrl + Enter, dobbeltklik og send det ned. I orden? Kast det bare tilbage til.
Bill Jelen: Hej! Okay, Mike, begge dine metoder var fantastiske. Lad os lave en hurtig afslutning her. Min første metode ved hjælp af tekst til kolonner: Trin 1, vælg Afgrænset; Trin 2, vælg et mellemrum, og klik derefter på Udfør. Problemet er, at hvis du har flere mellemrum, vil det ende i flere celler. Jeg er nødt til at sætte dem sammen igen. Office 365 TEXTJOIN eller den gamle B2 & “” & C2 og så videre.
Mike brugte Power Query, det er kendt som at transformere Excel 2016 eller i tidligere versioner 10 eller 13, du downloader det og bruger fanen Power Query. Jeg lærte endda noget her, men først konverterede du data ved hjælp af Ctrl + T og derefter fra tabel, opdelt søjle, ved afgrænsning, vælg afgrænsningsrum og derefter straks til venstre afgrænser. Jeg vidste ikke, at du kunne omdøbe en kolonne ved at dobbeltklikke. Jeg har højreklikket og omdøbt hele denne tid og er lidt irriteret over det. Det sparer mig meget tid. Og så ikke Luk & indlæs, men Luk & indlæs 2, og vælg et nyt sted på regnearket.
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.
Okay, 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: Duel182.xlsm
