Adskil for- og efternavn i Excel (opdel navne ved hjælp af formler)

Se video - Opdel navne i Excel (i for-, mellem- og efternavn)

Excel er et fantastisk værktøj, når det kommer til at skære og skære tekstdata.

Der er så mange nyttige formler samt funktioner, du kan bruge til at arbejde med tekstdata i Excel.

Et af de meget almindelige spørgsmål, jeg får om manipulation af tekstdata, er - “Hvordan adskiller man for- og efternavne (eller for-, mellem- og efternavne) i Excel?“.

Der er et par nemme måder at opdele navne på i Excel. Den metode, du vælger, afhænger af, hvordan dine data er struktureret, og om du vil have, at resultatet skal være statisk eller dynamisk.

Så lad os komme i gang og se forskellige måder at dele navne på i Excel.

Opdel navne ved hjælp af tekst til kolonner

Tekst til kolonner -funktionalitet i Excel giver dig mulighed for hurtigt at opdele tekstværdier i separate celler i træk.

Antag for eksempel, at du har datasættet som vist nedenfor, og du vil adskille for- og efternavn og få disse i separate celler.

Nedenfor er trinene til at adskille for- og efternavn ved hjælp af tekst til kolonner:

1. Vælg alle navnene i kolonnen (A2: A10 i dette eksempel)
2. Klik på fanen 'Data'
3. Klik på "Tekst til kolonner" i gruppen "Dataværktøjer".
4. Foretag følgende ændringer i guiden Konverter tekst til kolonne:
   1. Trin 1 af 3: Vælg Afgrænset (dette giver dig mulighed for at bruge mellemrum som separator) og klik på Næste
   2. Trin 2 af 3: Vælg mellemrumsmuligheden, og klik på Næste
   3. Trin 3 af 3: Indstil B2 som destinationscelle (ellers overskriver det de eksisterende data)
5. Klik på Afslut

Ovenstående trin ville øjeblikkeligt opdele navnene i for- og efternavn (med fornavne i kolonne B og efternavn i kolonne C).

Bemærk: Hvis der allerede er data i cellerne (dem, hvor der forventes output til tekst til kolonner), viser Excel dig en advarsel, der fortæller dig, at der allerede er nogle data i cellerne. Du kan vælge at overskrive dataene eller annullere tekst til kolonner og manuelt fjerne dem først.

Når det er gjort, kan du slette de fulde navnedata, hvis du vil.

Et par ting at vide, når du bruger tekst til kolonner til at adskille for- og efternavne i Excel:

1. Resultatet af dette er statisk. Det betyder, at hvis du har nye data eller de originale data har nogle ændringer, skal du gøre dette igen for at dele navnene.
2. Hvis du kun vil have fornavnet eller kun efternavnet, kan du springe de kolonner over, som du ikke vil have i trin 3 i dialogboksen 'Guiden Tekst til kolonne'. For at gøre dette skal du vælge den kolonne i den forhåndsvisning, du vil springe over, og derefter vælge indstillingen 'Importer ikke kolonne (spring over)'.
3. Hvis du ikke angiver destinationscellen, overskriver tekst til kolonne den aktuelle kolonne

Indstillingen Tekst til kolonner er bedst egnet, når du har konsekvente data (f.eks. Har alle navne kun for- og efternavn, eller alle navne har et for-, mellem- og efternavn).

I dette eksempel har jeg vist dig, hvordan du adskiller navne, der har mellemrum som afgrænsning. Hvis afgrænsningen er et komma eller en kombination af komma og mellemrum, kan du stadig bruge de samme trin. I dette tilfælde kan du angive afgrænseren i trin 2 af 3 i guiden. Der er allerede en mulighed for at bruge kommaet som afgrænsning, eller du kan også vælge ‘Andet’ og angive en brugerdefineret afgrænser.

Selvom tekst til kolonner er en hurtig og effektiv måde at opdele navne på, er det kun velegnet, når du vil have outputtet til at være et statisk resultat. Hvis du har et datasæt, der kan udvides eller ændres, er det bedre at bruge formler til at adskille navnene.

Adskil for-, mellem- og efternavne ved hjælp af formler

Formler giver dig mulighed for at skære og skære tekstdataene og udtrække det, du ønsker.

I dette afsnit vil jeg dele forskellige formler, du kan bruge til at adskille navnedata (baseret på hvordan dine data er struktureret).

Nedenfor er de tre formler, du kan bruge til at adskille for-, mellem- og efternavn (forklaret detaljeret senere i de følgende afsnit).

Formel for at få fornavnet:

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

Formel for at få det mellemste navn:

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

Formel for at få efternavnet:

= HØJRE (A15, LEN (A15) -SØG ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

Få fornavnet

Antag, at du har datasættet som vist nedenfor, og du vil hurtigt adskille fornavnet i en celle og efternavn i en celle.

Nedenstående formel giver dig fornavnet:

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

Ovenstående formel bruger SEARCH -funktionen til at få placeringen af ​​mellemrumstegnet mellem for- og efternavnet. LEFT -funktionen bruger derefter dette mellemrumspositionsnummer til at udtrække al teksten før den.

Dette er en ret ligetil brug af at udtrække en del af tekstværdien. Da alt, hvad vi skal gøre, er at identificere den første mellemrumstegnposition, er det ligegyldigt, om navnet har et mellemnavn eller ej. Ovenstående formel fungerer fint.

Lad os nu blive lidt mere avancerede med hvert eksempel.

Få efternavnet

Lad os sige, at du har det samme datasæt, og denne gang skal du få efternavnet.

Nedenstående formel vil udtrække efternavnet fra ovenstående datasæt:

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

Igen, ganske ligetil.

Denne gang finder vi først mellemrumstegnpositionen, som derefter bruges til at finde ud af antallet af tegn, der er tilbage efter mellemrum (hvilket ville være efternavnet).

Dette opnås ved at trække positionsværdien af ​​mellemrumstegnet med det samlede antal tegn i navnet.

Dette nummer bruges derefter i HØJRE -funktionen til at hente alle disse tegn fra højre for navnet.

Selvom denne formel fungerer godt, når der kun er for- og efternavn, fungerer den ikke, hvis du også har et mellemnavn. Dette skyldes, at vi kun tegnede os for et mellemrumstegn (mellem fornavn og efternavn). At have et mellemnavn tilføjer flere mellemrumstegn til navnet.

For at hente efternavnet, når du også har et mellemnavn, skal du bruge nedenstående formel:

= HØJRE (A15, LEN (A15) -SØG ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

Nu er dette begyndt at blive lidt komplekst … ikke sandt?

Lad mig forklare, hvordan dette fungerer.

Ovenstående formel finder først det samlede antal mellemrumstegn i navnet. Dette gøres ved at få længden af ​​navnet med og uden mellemrumstegnet og derefter trække den uden mellemrum fra den med mellemrum. Dette giver det samlede antal mellemrumstegn.

SUBSTITUTE -funktionen bruges derefter til at erstatte det sidste mellemrumstegn med et '@' -symbol (du kan bruge et hvilket som helst symbol - noget der usandsynligt vil forekomme som en del af navnet).

Når @ -symbolet er blevet erstattet i stedet for det sidste mellemrumstegn, kan du let finde placeringen af ​​dette @ -symbol. Dette gøres ved hjælp af SEARCH -funktionen.

Det eneste du skal gøre er at udtrække alle tegnene til højre for dette @ -symbol. Dette gøres ved at bruge funktionen HØJRE.

Få mellemnavnet

Antag, at du har datasættet som vist nedenfor, og du vil udtrække mellemnavnet.

Følgende formel gør dette:

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

Ovenstående formel bruger MID -funktionen, som giver dig mulighed for at angive en startposition og antallet af tegn, der skal udtrækkes fra den position.

Startpositionen er let at finde ved hjælp af SEARCH -funktionen.

Den svære del er at finde ud af, hvor mange tegn der skal udtrækkes efter denne startposition. For at få dette skal du identificere, hvor mange tegn der er fra startpositionen til det sidste mellemrumstegn.

Dette kan gøres ved at bruge SUBSTITUTE -funktionen og erstatte det sidste mellemrumstegn med et '@' symbol. Når dette er gjort, kan du let bruge funktionen SØG til at finde placeringen af ​​dette sidste mellemrumstegn.

Nu hvor du har startpositionen og positionen for det sidste mellemrum, kan du nemt hente mellemnavnet ved hjælp af MID -funktionen.

En af fordelene ved at bruge en formel til at adskille navnene er, at resultatet er dynamisk. Så hvis dit datasæt udvides og flere navne tilføjes til det, eller hvis nogle navne ændres i det originale datasæt, behøver du ikke bekymre dig om de resulterende data.

Adskil navne ved hjælp af Find og erstat

Jeg elsker fleksibiliteten, der følger med 'Find og erstat' - fordi du kan bruge jokertegn i den.

Lad mig først forklare, hvad der er et wild card -tegn.

Et jokertegn er noget, du kan bruge i stedet for enhver tekst. For eksempel kan du bruge et stjernesymbol (*), og det vil repræsentere et vilkårligt antal tegn i Excel. For at give dig et eksempel, hvis jeg vil finde alle de navne, der starter med alfabetet A, kan jeg bruge A* til at finde og erstatte. Dette finder og markerer alle cellerne, hvor navnet starter med A.

Hvis du stadig ikke er klar, skal du ikke bekymre dig. Fortsæt med at læse, og de næste par eksempler vil gøre det klart, hvad jokertegn er, og hvordan du bruger disse til hurtigt at adskille navne (eller eventuelle tekstværdier i Excel).

I alle eksemplerne nedenfor skal du sørge for at oprette en sikkerhedskopi af datasættet. Find og erstat ændrer de data, som de bruges på. Det er bedst at kopiere og indsætte dataene først og derefter bruge Find og erstat på det kopierede datasæt.

Få fornavnet

Antag, at du har et datasæt som vist nedenfor, og du ønsker kun at få fornavnet.

Nedenfor er trinene for at gøre dette:

1. Kopier navnedataene i kolonne A, og indsæt dem i kolonne B.
2. Når dataene i kolonne B er valgt, skal du klikke på fanen Startside
3. I gruppen Redigering skal du klikke på Find og vælg.
4. Klik på Erstat. Dette åbner dialogboksen 'Find og erstat'.
5. Indtast følgende i dialogboksen 'Find og erstat'
   1. Find hvad: * (mellemrumstegn efterfulgt af stjernetegnet)
   2. Erstat med: lad dette stå tomt
6. Klik på Erstat alle.

Ovenstående trin ville give dig fornavnet og fjerne alt efter fornavnet.

Dette fungerer, selvom du har navne, der har et mellemnavn.

Pro tip: Tastaturgenvejen til at åbne dialogboksen Find og erstat er Kontrol + H (hold styretasten nede, og tryk derefter på tasten H).

Få efternavnet

Antag, at du har et datasæt som vist nedenfor, og du ønsker kun at få efternavnet.

Nedenfor er trinene for at gøre dette:

1. Kopier navnedataene i kolonne A, og indsæt dem i kolonne B.
2. Når dataene i kolonne B er valgt, skal du klikke på fanen Startside
3. I gruppen Redigering skal du klikke på Find og vælg.
4. Klik på Erstat. Dette åbner dialogboksen 'Find og erstat'.
5. Indtast følgende i dialogboksen 'Find og erstat'
   1. Find hvad: * (stjerne -symbol efterfulgt af et mellemrumstegn)
   2. Erstat med: lad dette stå tomt
6. Klik på Erstat alle.

Ovenstående trin ville give dig efternavnet og fjerne alt før fornavnet.

Dette fungerer, selvom du har navne, der har et mellemnavn.

Fjern det mellemste navn

Hvis du kun vil slippe af med det mellemste navn og kun har for- og efternavnet, kan du gøre det ved hjælp af Find og erstat.

Antag, at du har et datasæt som vist nedenfor, og du vil fjerne det mellemste navn fra disse.

Nedenfor er trinene for at gøre dette:

1. Kopier navnedataene i kolonne A, og indsæt dem i kolonne B.
2. Når dataene i kolonne B er valgt, skal du klikke på fanen Startside
3. I gruppen Redigering skal du klikke på Find og vælg.
4. Klik på Erstat. Dette åbner dialogboksen 'Find og erstat'.
5. I dialogboksen 'Find og erstat' skal du indtaste følgende
   1. Find hvad: * (mellemrumstegn efterfulgt af stjernetegn efterfulgt af mellemrumstegn)
   2. Erstat med: (sæt bare et mellemrumstegn her)
6. Klik på Erstat alle.

Ovenstående trin fjerner det mellemste navn fra et fuldt navn. Hvis nogle navne ikke har noget mellemnavn, ville de ikke blive ændret.

Separate navne ved hjælp af Flash Fill

Flash fill blev introduceret i Excel 2013 og gør det virkelig let at ændre eller rense et tekstdatasæt.

Og når det kommer til at adskille navnedata, er det lige oppe i Flash Fill's gyde.

Det vigtigste at vide, når du bruger Flash Fill, er, at der skal et mønster, som flash fill kan identificere. Når det har identificeret mønsteret, vil det let hjælpe dig med at opdele navne i Excel (du får mere klarhed om dette, når du går gennem et par eksempler herunder).

Få for- eller efternavnet fra det fulde navn

Antag, at du har et datasæt som vist nedenfor, og du ønsker kun at få fornavnet.

1. I den tilstødende celle skal du indtaste fornavnet manuelt fra det fulde navn. I dette eksempel ville jeg skrive Rick.
2. I den anden celle skal du manuelt skrive fornavnet fra den tilstødende celles navn. Mens du skriver, vil du se Flash Fill vise dig en liste med fornavnet automatisk (i gråt).
3. Når du ser navnene i gråt, skal du hurtigt kigge igennem dem for at sikre, at det viser de rigtige navne. Hvis disse er rigtige, skal du trykke på enter -tasten, og Flash Fill vil automatisk fylde resten af ​​cellerne med fornavnet.

Flash Fill har brug for, at du giver det et mønster, som det kan følge, når du giver dig de ændrede data. I vores eksempel, når du skriver fornavnet i den første celle, kan Flash Fill ikke finde ud af mønsteret.

Men så snart du begynder at indtaste fornavnet i den anden celle, forstår Flash Fill mønsteret og viser dig et forslag. Hvis forslaget er korrekt, skal du bare trykke på enter -tasten.

Og hvis det ikke er korrekt, kan du prøve at indtaste manuelt i et par celler mere og kontrollere, om Flash Fill er i stand til at skelne mønsteret eller ej.

Nogle gange ser du muligvis ikke mønsteret i gråt (som vist i trin 2 ovenfor). Hvis det er tilfældet, skal du følge nedenstående trin for at få Flash Fill -resultatet:

1. Indtast teksten manuelt i to celler.
2. Vælg begge disse celler
3. Hold markøren i nederste højre del af markeringen. Du vil bemærke, at markøren ændres til et plusikon
4. Dobbeltklik på det (venstre musetast). Dette vil fylde alle cellerne. På dette tidspunkt er resultaterne sandsynligvis forkerte og ikke, hvad du havde forventet.
5. Nederst til højre i de resulterende data vil du se et lille ikon for automatisk udfyldning. Klik på dette ikon for automatisk udfyldning
6. Klik på Flash Fill

Ovenstående trin ville give dig resultatet fra Flash Fill (baseret på det mønster, det har udledt).

Du kan også bruge Flash Fill til at få efternavnet eller mellemnavnet. I de to første celler skal du indtaste efternavnet (eller det mellemste navn), og flashfyld vil være i stand til at forstå mønsteret

Omarranger navn ved hjælp af Flash Fill

Flash Fill er et smart værktøj, og det kan også tyde lidt komplekse mønstre

Antag f.eks., At du har et datasæt som vist nedenfor, og du vil omarrangere navnet fra Rick Novak til Novak, Rick (hvor efternavnet først kommer efterfulgt af et komma og derefter fornavnet).

Nedenfor er trinene for at gøre dette:

1. Skriv manuelt i den tilstødende celle Novak, Rick
2. I den anden celle skal du indtaste manuelt Connor, Susan. Mens du skriver, vil du se Flash Fill vise dig en liste over navnene i samme format (i gråt).
3. Når du ser navnene i gråt, skal du hurtigt kigge igennem det for at sikre, at det viser de rigtige navne. Hvis disse er rigtige, skal du trykke på enter -tasten, og Flash Fill vil automatisk fylde resten af ​​cellerne med navnene i det samme format.

Fjern det mellemste navn (eller få det mellemste navn)

Du kan også bruge Flash Fill til at slippe af med mellemnavnet eller kun få mellemnavnet.

Antag f.eks., At du har et datasæt som vist nedenfor, og du ønsker kun at få for- og efternavnet og ikke mellemnavnet.

Nedenfor er trinene for at gøre dette:

1. Skriv manuelt i den tilstødende celle Rick Novak
2. I den anden celle skal du indtaste manuelt Susan Connor. Mens du skriver, vil du se Flash Fill vise dig en liste over navnene i samme format (i gråt).
3. Når du ser navnene i gråt, skal du hurtigt kigge igennem det for at sikre, at det viser de rigtige navne. Hvis disse er rigtige, skal du trykke på enter -tasten, og Flash Fill vil automatisk fylde resten af ​​cellerne med navnene uden det mellemste navn.

På samme måde, hvis du kun vil have de mellemste navne, skal du skrive det mellemste navn i de to første celler og bruge Flash Fill til at få det mellemste navn fra alle de resterende navne.

Eksemplerne vist i denne vejledning bruger navne under manipulation af tekstdata. Du kan bruge de samme begreber til også at arbejde med andre dataformater (f.eks. Adresser, produktnavne osv.)