Hvordan skille en kolonne med Excel-data i to kolonner. Hvordan analysere data i Excel.
Se på video
- Bills første metode ved å bruke Tekst til kolonner (funnet i Data-fanen).
- I trinn 1 velger du avgrenset. I trinn 2 velger du et mellomrom. Hopp over trinn 3 ved å klikke på ferdig.
- Teksten vil deles opp i hvert mellomrom, så alt med tre ord vil havne i 3 celler. Sett dem sammen igjen med
=TEXTJOIN(" ",True,B2:E2)eller - med
=B2&" "&C2&" "&D2 - Mikes første metode bruker Power Query. Power Query er Get & Transform i 2016 eller en gratis nedlasting for 2010 eller 2013.
- Konverter først dataene dine til en tabell ved hjelp av Ctrl + T. Deretter, i Power Query, fra Table. Split kolonne, avgrenseren. Velg Mellomrom og deretter til venstre avgrenseren.
- Du kan gi nytt navn til en kolonne ved å dobbeltklikke!
- Lukk og last inn … og velg et nytt sted på regnearket.
- Bills andre metode er å bruke Flash Fill. Skriv inn nye overskrifter i A, B & C. Flash Fill vil ikke fungere hvis du ikke har overskrifter! Skriv inn et mønster for de to første radene.
- Gå til den første tomme cellen i B og trykk Ctrl + E. Gjenta for kolonne C.
- Mikes andre metode er å bruke disse formlene:
- Bruk den første delen
=LEFT(A2,SEARCH(" ",A2)-1) - For den andre delen, bruk
=SUBSTITUTE(A2,B2&" ","")
Videoutskrift
(Musikk)
Bill Jelen: Hei, velkommen tilbake, det er på tide med en annen Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får selskap av Mike Girvin fra Excel Is Fun. Dette er vår
Episode 182: Del data fra en celle for å vises i to celler.
Greit, dagens spørsmål sendes inn av Tom. Er det en måte å enkelt dele dataene i en celle for å få dataene til å vises i to celler? For eksempel, 123 Main Street, han vil ha 123 i en celle og Main Street i en annen celle; eller, Howard og Howard og deretter End. Jeg har brukt utallige timer på å skille denne typen data. Jeg vil sette pris på å høre fra firmaet ditt mens det er mange, mange forskjellige måter å gjøre dette på.
Det første jeg skal gjøre er å velge alle Data, Ctrl + Skift + Pil ned og deretter Data, Tekst til kolonner. Tekst til kolonner i trinn 1, dataene er avgrenset. Den avgrenses av et mellomrom, og klikk deretter Fullfør. Her er bryet med denne metoden at hvis du har 123 Main Street, vil den havne i 3 celler i stedet for 2 celler. Åh, Power Query ville gjøre dette så mye enklere, men her er vi. Greit, så det jeg skal gjøre er at jeg kommer langt ut til høyre for dataene der jeg vet at utover der alt er bygget. Hvis jeg er i Office 365, skal jeg bruke TEXTJOIN. TEXTJOIN, den fantastiske tingen, skilletegn er et mellomrom. Ignorer tomme celler Sant og deretter cellene som jeg vil sammenføye sammen slik, og jeg kopierer bare alle disse ned, Ctrl + V. Jeg vil kopiere Ctrl + C og deretter Hjem, Lim inn,Lim inn som verdier, og på dette tidspunktet kan jeg slette disse 3 ekstra kolonnene.
Ahh, men ingen har Office 365, ikke sant? Så hvis du ikke har Office 365, må du gjøre = denne tingen & "" & det, og hvis det var mer "" og det, og hvis det var flere, fortsett. I dette tilfellet er det meningsløst fordi det ikke er noe over i D, men du får ideen. Ctrl + C, kopier den ned til den siste dataraden, Ctrl + V og deretter Ctrl + C, Alt + ESV for å lage disse B-verdiene. Og der er vi, ok. Mike, la oss se hva du har.
Mike Girvin: Takk. Hei, du lobbte meg en enkel her fordi du allerede nevnte Get & Transform Power Query, den gamle teksten til kolonnene lar deg bare si et mellomrom på hvert tegn, ikke sant? Vel, hvis vi bruker Power Query, kan vi bruke den skillelinjen og si, "Hei, bare del opp ved første forekomst."
Nå, for å få disse dataene til spørreredigereren, må vi konvertere dem til en Excel-tabell. Så jeg går opp til Sett inn, Tabell eller jeg bruker Ctrl + T. Bordet mitt har overskrifter, OK-knappen er uthevet, slik at jeg kan klikke på den med musen eller bare trykke Enter. Nå vil jeg gi navnet til denne tabellen, så jeg kommer opp hit, OriginalData og Enter. Dette er en Excel-tabell, vi kan komme opp til Data og der er den fra tabell. Det vil bringe det fra Excel til redaktøren. Kolonnen er valgt: Startside-fanen, vi kan si Del kolonne av skillelinje eller kom hit og høyreklikk, Del kolonne av skillelinje. Fra rullegardinmenyen kan vi si, hei, bruk et mellomrom og se på dette til venstre avgrenseren. Når jeg klikker OK, BOOM! Der er det. Nå skal jeg gi navn til begge disse kolonnene: dobbeltklikk på Del 1 Enter, dobbeltklikk på Del 2 og Enter. Nå,Jeg kan komme opp hit eller Close & Load, Close & Load To og jeg kan velge hvor jeg skal plassere dette. Jeg vil definitivt dumpe det som en tabell, nytt regneark, eksisterende regneark. Marker dette, klikk på skjul-knappen. Jeg skal si D1, klikk OK og klikk deretter Last inn. Og der går vi, vår Power Query Output.
Ok, kast tilbake til.
Bill Jelen: Åh, Mike, Power Query er kjempebra! Ja, det er en fin vei å gå. Her er en annen som det kan fungere hvis du har Excel 2013 eller nyere.
Og det vi skal gjøre er å komme ut her og si første del og deretter andre del. Sørg for å sette disse overskriftene. Hvis du ikke setter disse overskriftene, trenger de ikke være det, men de må ha overskrifter, ellers fungerer det ikke. Jeg legger 123 og Main Street, og så setter vi Howard og End. Nå som vi har et fint lite mønster der, kom ut her i Data-fanen og Flash Fill som er Ctrl + E, trykk Ctrl + E akkurat der og trykk deretter Ctrl + E akkurat der. Vakker ting er at vi ikke trenger å sammenkoble data sammen som i eksemplet mitt. Rett, Mike, tilbake til deg.
Mike Girvin: Ding-ding-ding. Det er vinneren uten tvil. Flash Fill er veien å gå dit. Legg merke til at vi ikke trengte å konvertere den til en tabell eller åpne noen dialogboks. bare skrev noen få eksempler og deretter Ctrl + E.
Greit, vel, vi kan gjøre det med formler, selv om Flash Fill sannsynligvis ville være raskere. Vel, se på dette, mønsteret akkurat som denne listecellen som brukes i Flash Fill er alt før første plass og deretter alt etter. Så hei, jeg skal bruke VENSTRE-funksjonen, teksten er der og hvor mange tegn fra venstre? Vel, jeg skal søke etter det rommet - 1 2 3 4 ved å bruke SØK-funksjonen, Finn tekst, mellomrom og “”, innenfor det. Legg merke til at Search vil telle på fingrene 1 2 3 4 og som vil komme til den plassen jeg vil ha, den plassen så jeg -1) Ctrl + Enter, dobbeltklikk og send den ned. Så det får alltid alt før første plass.
Legg merke til at vi allerede har teksten her, slik at jeg kan bruke SUBSTITUTE-funksjonen. Teksten som jeg skal se gjennom er Full Data, komma, den gamle teksten jeg vil se etter og deretter SUBSTITUTE. Ingenting er nesten 1 2 3. Jeg vil faktisk legge til mellomrommet som jeg nettopp tok ut i forrige formel, tilbake i. Nå vil det se etter 1 2 3, mellomrom og deretter Howard, mellomrom og så videre, komma og deretter den nye teksten jeg vil erstatte i. Vel, for å fortelle SUBSTITUTE at du vil erstatte den med ingenting, sier du "" ingen mellomrom mellom, Lukk parentes og det vil fungere. Ctrl + Enter, dobbeltklikk og send den ned. Ok? Bare kaste den tilbake til.
Bill Jelen: Hei! Greit, Mike, begge metodene dine var kjempebra. La oss gjøre en rask avslutning her. Min første metode ved å bruke Tekst til kolonner: Trinn 1, velg Avgrenset; Trinn 2, velg et mellomrom, og klikk deretter Fullfør. Problemet er at hvis du har flere mellomrom, vil det havne i flere celler. Jeg må sette dem sammen igjen. Office 365 TEXTJOIN eller den gamle B2 & “” & C2 og så videre.
Mike brukte Power Query, det er kjent som å transformere Excel 2016 eller i tidligere versjoner 10 eller 13, du laster det ned og bruker Power Query-fanen. Jeg har til og med lært noe her, men først konverterte du data ved hjelp av Ctrl + T og deretter fra Tabell, Delt kolonne, avgrenseren, velg Avgrensningsrom og deretter med en gang til venstre avgrenseren. Jeg visste ikke at du kunne gi nytt navn til en kolonne ved å dobbeltklikke. Jeg har høyreklikket og gitt nytt navn hele tiden og har irritert meg litt over det. Det vil spare meg for mye tid. Og så ikke Close & Load men Close & Load 2 og velg et nytt 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.
Ok, jeg vil takke alle for innom. Vi sees neste gang for en annen Dueling Excel Podcast fra og Excel er morsomt.
Last ned fil
Last ned eksempelfilen her: Duel182.xlsm
