Sådan opdeles celler i Excel (adskilt i flere kolonner)

Der kan være situationer, hvor du skal opdele celler i Excel. Disse kan være, når du får dataene fra en database, eller du kopierer dem fra internettet eller får dem fra en kollega.

Et enkelt eksempel, hvor du skal opdele celler i Excel, er når du har fulde navne, og du vil opdele disse i fornavn og efternavn.

Eller du får adresse ’, og du vil opdele adressen, så du kan analysere byerne eller pinkoden separat.

Sådan opdeles celler i Excel

I denne vejledning lærer du, hvordan du deler celler i Excel ved hjælp af følgende teknikker:

• Brug af funktionen Tekst til kolonne.
• Brug af Excel -tekstfunktioner.
• Brug af Flash Fill (tilgængelig i 2013 og 2016).

Lad os begynde!

Opdel celler i Excel ved hjælp af tekst til kolonne

Nedenfor har jeg en liste med navne på nogle af mine foretrukne fiktive karakterer, og jeg vil dele disse navne i separate celler .:

Her er trinene til at opdele disse navne i for- og efternavn:

• Vælg de celler, hvor du har den tekst, du vil dele (i dette tilfælde A2: A7).
• Klik på fanen Data
• Klik på "Tekst til kolonner" i gruppen "Dataværktøjer".
• I guiden Konverter tekst til kolonner:
  • Trin 1 af 3 i guiden Tekst til kolonner: Sørg for, at Afgrænset er valgt (det er standardvalget). Dette ville give dig mulighed for at adskille fornavn og efternavn baseret på en specificeret separator (mellemrum i dette tilfælde).
  • Klik på Næste.
  • Trin 2 af 3 Guiden Tekst til kolonner: Vælg mellemrum som afgrænsning, og fravælg alt andet. Du kan se, hvordan dit resultat ville se ud i sektionen Dataforhåndsvisning i dialogboksen.
  • Klik på Næste.
  • Trin 3 af 3 Guiden Tekst til kolonner: I dette trin kan du angive dataformatet, og hvor du vil have resultatet. Jeg vil beholde dataformatet som Generelt, da jeg har tekstdata til at dele. Standarddestinationen er A2, og hvis du fortsætter med dette, erstatter det det originale datasæt. Hvis du vil beholde de originale data intakte, skal du vælge en anden celle som destination. I dette tilfælde vælges B2.
  • Klik på Afslut.

Dette vil øjeblikkeligt opdele cellens tekst i to forskellige kolonner.

Bemærk:

• Tekst til kolonne -funktion opdeler cellernes indhold baseret på afgrænsningen. Selvom dette fungerer godt, hvis du vil adskille fornavn og efternavn, vil det i tilfælde af for-, mellem- og efternavn opdele det i tre dele.
• Det resultat, du får ved at bruge funktionen Tekst til kolonne, er statisk. Det betyder, at hvis der er ændringer i de originale data, skal du gentage processen for at få opdaterede resultater.

Opdel celler i Excel ved hjælp af tekstfunktioner

Excel -tekstfunktioner er gode, når du vil skære og snitte tekststrenge.

Mens funktionen Tekst til kolonne giver et statisk resultat, er det resultat, du får ved at bruge funktioner, dynamisk og opdateres automatisk, når du ændrer de originale data.

Opdel navne, der har et fornavn og efternavn

Antag, at du har de samme data som vist herunder:

Udtrækning af fornavnet

For at få fornavnet fra denne liste skal du bruge følgende formel:

= VENSTRE (A2, SØG ("", A2) -1)

Denne formel ville få øje på det første mellemrumstegn og derefter returnere al teksten før dette mellemrumstegn:

Denne formel bruger SEARCH -funktionen til at få placeringen af ​​mellemrumstegnet. I tilfælde af Bruce Wayne er rumkarakteren i 6. position. Det udtrækker derefter alle tegnene til venstre for det ved hjælp af funktionen VENSTRE.

Udtrækning af efternavnet

Tilsvarende, for at få efternavnet, skal du bruge følgende formel:

= HØJRE (A2, LEN (A2) -SØG ("", A2))

Denne formel bruger søgefunktionen til at finde mellemrumstastens position ved hjælp af funktionen SØG. Det trækker derefter dette tal fra den samlede længde af navnet (der er givet af LEN -funktionen). Dette angiver antallet af tegn i efternavnet.

Dette efternavn ekstraheres derefter ved hjælp af funktionen HØJRE.

Bemærk: Disse funktioner fungerer muligvis ikke godt, hvis du har indledende, efterfølgende eller dobbelte mellemrum i navnene. Klik her for at lære, hvordan du fjerner førende/efterfølgende/dobbelt mellemrum i Excel.

Opdel navne, der har et fornavn, mellemnavn og efternavn

Der kan være tilfælde, hvor du får en kombination af navne, hvor nogle navne også har et mellemnavn.

Formlen i sådanne tilfælde er lidt kompleks.

Udtrækning af fornavnet

For at få fornavnet:

= VENSTRE (A2, SØG ("", A2) -1)

Dette er den samme formel, vi brugte, da der ikke var noget mellemnavn. Det leder simpelthen efter det første mellemrumstegn og returnerer alle tegnene før rummet.

Udtrækning af mellemnavnet

For at få det mellemste navn:

= HVISFEJL (MIDD (A2, SØG ("", A2)+1, SØG ("", A2, SØG ("", A2) +1) -SØG ("", A2)), "")

MID -funktionen starter fra det første mellemrumstegn og udtrækker mellemnavnet ved at bruge forskellen i placeringen af ​​det første og det andet mellemrumstegn.

Hvis der ikke er noget mellemnavn, returnerer MID -funktionen en fejl. For at undgå fejlen er den pakket ind i IFERROR -funktionen.

Udtrækning af efternavnet

For at få efternavnet skal du bruge nedenstående formel:

= HVIS (LEN (A2) -LEN (SUBSTITUTE (A2, "", "")) = 1, HØJRE (A2, LEN (A2) -SØG ("", A2)), HØJRE (A2, LEN (A2) -SØG ("", A2, SØG ("", A2) +1)))

Denne formel kontrollerer, om der er et mellemnavn eller ej (ved at tælle antallet af mellemrumstegn). Hvis der kun er 1 mellemrumstegn, returnerer det ganske enkelt al teksten til højre for mellemrumstegnet.

Men hvis der er 2, så opdager det det andet mellemrumstegn og returnerer antallet af tegn efter det andet mellemrum.

Bemærk: Denne formel fungerer godt, når du har navne, der enten kun har nævenavnet og efternavnet, eller for-, mellem- og efternavnet. Men hvis du har en blanding, hvor du har suffikser eller hilsner, skal du ændre formlerne yderligere.

Opdel celler i Excel ved hjælp af Flash Fill

Flash Fill er en ny funktion, der blev introduceret i Excel 2013.

Det kan være virkelig praktisk, når du har et mønster, og du hurtigt vil udtrække en del af det.

Lad os f.eks. Tage fornavn og efternavn:

Flash fill fungerer ved at identificere mønstre og replikere det for alle de andre celler.

Sådan kan du udtrække fornavnet fra listen ved hjælp af Flash Fill:

• I celle B2 skal du indtaste fornavnet for Bruce Wayne (dvs. Bruce).
• Når cellen er markeret, vil du bemærke en lille firkant i højre ende af cellevalget. Dobbeltklik på den. Dette vil fylde det samme navn i alle cellerne.
• Når cellerne er fyldt, ser du nederst til højre ikonet Autofyld -muligheder. Klik på den.
• Vælg Flash Fill fra listen.
• Så snart du vælger Flash Fill, vil du bemærke, at alle cellerne opdaterer sig selv og nu viser fornavnet for hvert navn.

Hvordan fungerer Flash Fill?

Flash Fill søger efter mønstrene i datasættet og replikerer mønsteret.

Flash Fill er en overraskende smart funktion og fungerer som forventet i de fleste tilfælde. Men det mislykkes også i nogle tilfælde.

For eksempel, hvis jeg har en liste med navne, der har en kombination af navne, hvor nogle har et mellemnavn og nogle ikke.

Hvis jeg udtrækker mellemnavnet i et sådant tilfælde, returnerer Flash Fill fejlagtigt efternavnet, hvis der ikke er noget fornavn.

For at være ærlig er det stadig en god tilnærmelse til tendensen. Det er dog ikke det, jeg ønskede.

Men det er stadig et godt nok værktøj til at opbevare i dit arsenal og bruge, når behovet opstår.

Her er et andet eksempel, hvor Flash Fill fungerer glimrende.

Jeg har et sæt adresser, som jeg hurtigt vil udtrække byen fra.

For hurtigt at få byen skal du indtaste bynavnet for den første adresse (indtast London i celle B2 i dette eksempel) og bruge autofyld til at udfylde alle cellerne. Brug nu Flash Fill og giver dig øjeblikkeligt navnet på byen fra hver adresse.

På samme måde kan du opdele adressen og udtrække enhver del af adressen.

Bemærk, at dette ville have brug for adressen for at være et homogent datasæt med den samme afgrænsning (komma i dette tilfælde).

Hvis du prøver at bruge Flash Fill, når der ikke er noget mønster, viser det dig en fejl som vist herunder:

I denne vejledning har jeg dækket tre forskellige måder at opdele celler i Excel i flere kolonner (ved hjælp af tekst til kolonner, formler og Flash Fill)

Håber du fandt denne Excel -tutorial nyttig.