Skill for- og etternavn i Excel (splitt navn ved hjelp av formler)

Se video - Del navn i Excel (i fornavn, mellomnavn og etternavn)

Excel er et fantastisk verktøy når det gjelder skjæring og terning av tekstdata.

Det er så mange nyttige formler og funksjoner du kan bruke til å jobbe med tekstdata i Excel.

Et av de veldig vanlige spørsmålene jeg får om manipulering av tekstdata er - “Hvordan skiller jeg fornavn og etternavn (eller fornavn, mellomnavn og etternavn) i Excel?“.

Det er et par enkle måter å dele navn på i Excel. Metoden du velger vil avhenge av hvordan dataene dine er strukturert og om du vil at resultatet skal være statisk eller dynamisk.

Så la oss komme i gang og se forskjellige måter å dele navn på i Excel.

Del navn ved å bruke tekst til kolonner

Tekst til kolonner -funksjonalitet i Excel lar deg raskt dele tekstverdier i separate celler på rad.

Anta for eksempel at du har datasettet som vist nedenfor, og du vil skille fornavn og etternavn og få disse i separate celler.

Nedenfor er trinnene for å skille fornavn og etternavn ved hjelp av tekst til kolonner:

1. Velg alle navnene i kolonnen (A2: A10 i dette eksemplet)
2. Klikk på "Data" -fanen
3. I gruppen "Dataverktøy" klikker du på "Tekst til kolonner" -alternativet.
4. Gjør følgende endringer i veiviseren Konverter tekst til kolonne:
   1. Trinn 1 av 3: Velg Avgrenset (dette lar deg bruke mellomrom som skilletegn) og klikk på Neste
   2. Trinn 2 av 3: Velg mellomrom -alternativet og klikk på Neste
   3. Trinn 3 av 3: Angi B2 som målcelle (ellers vil den overskrive eksisterende data)
5. Klikk på Fullfør

De ovennevnte trinnene ville umiddelbart dele navnene i for- og etternavn (med fornavn i kolonne B og etternavn i kolonne C).

Merk: Hvis det allerede er data i cellene (de der tekst til kolonner -utgang forventes), vil Excel vise deg en advarsel som gir deg beskjed om at det allerede er noen data i cellene. Du kan velge å overskrive dataene eller avbryte tekst til kolonner og fjerne dem manuelt først.

Når du er ferdig, kan du slette hele navnedataene hvis du vil.

Noen ting å vite når du bruker tekst til kolonner for å skille fornavn og etternavn i Excel:

1. Resultatet av dette er statisk. Dette betyr at hvis du har nye data eller de originale dataene har noen endringer, må du gjøre dette på nytt for å dele navnene.
2. Hvis du bare vil ha fornavnet eller bare etternavnet, kan du hoppe over kolonnene du ikke vil ha i trinn 3 i dialogboksen "Veiviser for tekst til kolonne". For å gjøre dette, velg kolonnen i forhåndsvisningen du vil hoppe over, og velg deretter alternativet "Ikke importer kolonne (hopp over)".
3. Hvis du ikke angir målcellen, vil tekst til kolonne overskrive den nåværende kolonnen

Alternativet Tekst til kolonner er best egnet når du har konsistente data (for eksempel har alle navn kun for- og etternavn, eller alle navn har for-, mellom- og etternavn).

I dette eksemplet har jeg vist deg hvordan du skiller navn som har mellomrom som skilletegn. Hvis skilletegnet er et komma eller en kombinasjon av komma og mellomrom, kan du fortsatt bruke de samme trinnene. I dette tilfellet kan du angi skilletegn i trinn 2 av 3 i veiviseren. Det er allerede et alternativ for å bruke kommaet som skilletegn, eller du kan velge alternativet "Annet" og angi en tilpasset skilletegn også.

Selv om tekst til kolonner er en rask og effektiv måte å dele navn på, er den bare egnet når du vil at utgangen skal være et statisk resultat. Hvis du har et datasett som kan utvides eller endres, er det bedre å bruke formler for å skille navnene.

Skill fornavn, mellomnavn og etternavn ved å bruke formler

Formler lar deg skjære og skjære tekstdataene og trekke ut det du vil ha.

I denne delen vil jeg dele forskjellige formler du kan bruke til å skille navnedata (basert på hvordan dataene dine er strukturert).

Nedenfor er de tre formlene du kan bruke til å skille fornavn, mellomnavn og etternavn (forklart i detalj senere i de følgende avsnittene).

Formel for å få fornavnet:

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

Formel for å få mellomnavnet:

= MIDT (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

Formel for å få etternavnet:

= HØYRE (A15, LEN (A15) -SØK ("@", ERSTATNING (A15, "", "@", LEN (A15) -LEN (ERSTATNING (A15, "", "")))))

Få fornavnet

Anta at du har datasettet som vist nedenfor, og du vil raskt skille fornavnet i en celle og etternavn i en celle.

Formelen nedenfor gir deg fornavnet:

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

Formelen ovenfor bruker SEARCH -funksjonen for å få posisjonen til mellomromstegnet mellom fornavnet og etternavnet. VENSTRE -funksjonen bruker deretter dette mellomromsposisjonsnummeret til å trekke ut all teksten før den.

Dette er en ganske grei bruk av å trekke ut en del av tekstverdien. Siden alt vi trenger å gjøre er å identifisere den første mellomromstypen, spiller det ingen rolle om navnet har et mellomnavn eller ikke. Formelen ovenfor kommer til å fungere helt fint.

La oss nå bli litt mer avanserte med hvert eksempel.

Få etternavnet

La oss si at du har det samme datasettet, og denne gangen må du få etternavnet.

Formelen nedenfor trekker ut etternavnet fra datasettet ovenfor:

= HØYRE (A2, LENG (A2) -SØK ("", A2))

Igjen, ganske greit.

Denne gangen finner vi først mellomromstegnposisjonen, som deretter brukes til å finne ut antall tegn som er igjen etter mellomrom (som ville være etternavnet).

Dette oppnås ved å trekke plasseringsverdien til mellomromstegnet med det totale antallet tegn i navnet.

Dette tallet brukes deretter i HØYRE -funksjonen for å hente alle disse tegnene til høyre for navnet.

Selv om denne formelen fungerer bra når det bare er for- og etternavn, vil det ikke fungere hvis du også har et mellomnavn. Dette er fordi vi bare sto for ett mellomromstegn (mellom fornavn og etternavn). Å ha et mellomnavn legger til flere mellomromstegn i navnet.

For å hente etternavnet når du også har et mellomnavn, bruk formelen nedenfor:

= HØYRE (A15, LEN (A15) -SØK ("@", ERSTATNING (A15, "", "@", LEN (A15) -LEN (ERSTATNING (A15, "", "")))))

Nå har dette begynt å bli litt komplisert … ikke sant?

La meg forklare hvordan dette fungerer.

Formelen ovenfor finner først det totale antallet mellomromstegn i navnet. Dette gjøres ved å få lengden på navnet med og uten mellomromstegnet og deretter trekke den uten mellomrom fra den med mellomrom. Dette gir totalt antall mellomromstegn.

SUBSTITUTE -funksjonen brukes deretter til å erstatte det siste mellomromstegnet med et "@" -symbol (du kan bruke et hvilket som helst symbol - noe som usannsynlig vil forekomme som en del av navnet).

Når @ -symbolet er byttet ut i stedet for det siste mellomromstegnet, kan du enkelt finne posisjonen til dette @ -symbolet. Dette gjøres ved hjelp av SEARCH -funksjonen.

Alt du trenger å gjøre er å trekke ut alle tegnene til høyre for dette @ -symbolet. Dette gjøres ved å bruke HØYRE -funksjonen.

Få mellomnavnet

Anta at du har datasettet som vist nedenfor, og du vil trekke ut mellomnavnet.

Følgende formel vil gjøre dette:

= MIDT (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

Formelen ovenfor bruker MID -funksjonen, som lar deg angi en startposisjon og antall tegn som skal hentes fra den posisjonen.

Startposisjonen er lett å finne ved hjelp av SEARCH -funksjonen.

Den vanskelige delen er å finne ut hvor mange tegn som skal trekkes ut etter denne startposisjonen. For å få dette må du identifisere hvor mange tegn det er fra startposisjonen til det siste mellomromstegnet.

Dette kan gjøres ved å bruke SUBSTITUTE -funksjonen og erstatte det siste mellomromstegnet med et "@" -symbol. Når dette er gjort, kan du enkelt bruke SEARCH -funksjonen til å finne posisjonen til dette siste mellomromstegnet.

Nå som du har startposisjonen og posisjonen til den siste plassen, kan du enkelt hente mellomnavnet ved å bruke MID -funksjonen.

En av fordelene med å bruke en formel for å skille navnene er at resultatet er dynamisk. Så hvis datasettet ditt utvides og flere navn legges til, eller hvis noen navn endres i det originale datasettet, trenger du ikke bekymre deg for de resulterende dataene.

Skill navn ved hjelp av Finn og erstatt

Jeg elsker fleksibiliteten som følger med 'Finn og erstatt' - fordi du kan bruke jokertegn i den.

La meg først forklare hva som er et wild card -tegn.

Et jokertegn er noe du kan bruke i stedet for hvilken som helst tekst. For eksempel kan du bruke et stjernesymbol (*), og det vil representere et hvilket som helst antall tegn i Excel. For å gi deg et eksempel, hvis jeg vil finne alle navnene som begynner med alfabetet A, kan jeg bruke A* for å finne og erstatte. Dette vil finne og velge alle cellene der navnet begynner med A.

Hvis du fortsatt ikke er klar, ikke bekymre deg. Fortsett å lese, og de neste eksemplene vil gjøre det klart hva jokertegn er og hvordan du bruker disse til raskt å skille navn (eller eventuelle tekstverdier i Excel).

I alle eksemplene nedenfor, må du sørge for at du oppretter en sikkerhetskopi av datasettet. Finn og erstatt endrer dataene de brukes på. Det er best å kopiere og lime inn dataene først og deretter bruke Finn og erstatt på det kopierte datasettet.

Få fornavnet

Anta at du har et datasett som vist nedenfor, og du vil bare få fornavnet.

Nedenfor er trinnene for å gjøre dette:

1. Kopier navnedataene i kolonne A og lim dem inn i kolonne B.
2. Når dataene i kolonne B er valgt, klikker du på kategorien Hjem
3. Klikk på Finn og velg i redigeringsgruppen.
4. Klikk på Erstatt. Dette åpner dialogboksen "Finn og erstatt".
5. Skriv inn følgende i dialogboksen "Finn og erstatt"
   1. Finn hva: * (mellomromstegn etterfulgt av stjernesymbolet)
   2. Erstatt med: la dette stå tomt
6. Klikk på Erstatt alle.

Trinnene ovenfor vil gi deg fornavnet og fjerne alt etter fornavnet.

Dette fungerer selv om du har navn som har et mellomnavn.

Pro tips: Hurtigtasten for å åpne dialogboksen Finn og erstatt er Kontroll + H (hold inne kontrolltasten og trykk deretter på H -tasten).

Få etternavnet

Anta at du har et datasett som vist nedenfor, og du vil bare få etternavnet.

Nedenfor er trinnene for å gjøre dette:

1. Kopier navnedataene i kolonne A og lim dem inn i kolonne B.
2. Når dataene i kolonne B er valgt, klikker du på kategorien Hjem
3. Klikk på Finn og velg i redigeringsgruppen.
4. Klikk på Erstatt. Dette åpner dialogboksen "Finn og erstatt".
5. Skriv inn følgende i dialogboksen "Finn og erstatt"
   1. Finn hva: * (stjerne -symbol etterfulgt av et mellomrom)
   2. Erstatt med: la dette stå tomt
6. Klikk på Erstatt alle.

Trinnene ovenfor vil gi deg etternavnet og fjerne alt før fornavnet.

Dette fungerer selv om du har navn som har et mellomnavn.

Fjern mellomnavnet

Hvis du bare vil bli kvitt mellomnavnet og bare har for- og etternavnet, kan du gjøre det ved å bruke Finn og erstatt.

Anta at du har et datasett som vist nedenfor, og du vil fjerne mellomnavnet fra disse.

Nedenfor er trinnene for å gjøre dette:

1. Kopier navnedataene i kolonne A og lim dem inn i kolonne B.
2. Når dataene i kolonne B er valgt, klikker du på kategorien Hjem
3. Klikk på Finn og velg i redigeringsgruppen.
4. Klikk på Erstatt. Dette åpner dialogboksen "Finn og erstatt".
5. Skriv inn følgende i dialogboksen "Finn og erstatt"
   1. Finn hva: * (mellomromstegn etterfulgt av stjernesymbolet etterfulgt av mellomromstegn)
   2. Erstatt med: (bare legg et mellomrom her)
6. Klikk på Erstatt alle.

De ovennevnte trinnene vil fjerne mellomnavnet fra et fullt navn. Hvis noen navn ikke har mellomnavn, vil de ikke bli endret.

Separate navn ved hjelp av Flash Fill

Flash fill ble introdusert i Excel 2013 og gjør det veldig enkelt å endre eller rengjøre et tekstdatasett.

Og når det gjelder å skille navnedata, er det rett opp i Flash Fill -smuget.

Det viktigste å vite når du bruker Flash Fill er at det er et mønster som flash fill kan identifisere. Når det har identifisert mønsteret, vil det enkelt hjelpe deg med å dele navn i Excel (du vil få mer klarhet om dette når du går gjennom noen eksempler nedenfor).

Få for- eller etternavnet fra fullt navn

Anta at du har et datasett som vist nedenfor, og du vil bare få fornavnet.

1. I den tilstøtende cellen skriver du fornavnet manuelt fra det fulle navnet. I dette eksemplet ville jeg skrive Rick.
2. I den andre cellen skriver du fornavnet manuelt fra navnet på den tilstøtende cellen. Mens du skriver, vil du se Flash Fill som viser deg en liste over fornavnet automatisk (i grått).
3. Når du ser navnene i grått, blar du raskt gjennom det for å sikre at det viser de riktige navnene. Hvis disse er riktige, trykker du på enter -tasten, og Flash Fill vil automatisk fylle resten av cellene med fornavnet.

Flash Fill trenger deg til å gi den et mønster som den kan følge når du gir deg de endrede dataene. I vårt eksempel, når du skriver fornavnet i den første cellen, kan ikke Flash Fill finne ut mønsteret.

Men så snart du begynner å skrive inn fornavnet i den andre cellen, forstår Flash Fill mønsteret og viser deg et forslag. Hvis forslaget er riktig, trykker du bare på enter -tasten.

Og hvis det ikke er riktig, kan du prøve å skrive inn manuelt i noen flere celler og sjekke om Flash Fill kan skille mønsteret eller ikke.

Noen ganger ser du kanskje ikke mønsteret i grått (som vist i trinn 2 ovenfor). Hvis det er tilfelle, følg trinnene nedenfor for å få Flash Fill -resultatet:

1. Skriv inn teksten manuelt i to celler.
2. Velg begge disse cellene
3. Hold markøren nederst til høyre i utvalget. Du vil merke at markøren endres til et plussikon
4. Dobbeltklikk på den (venstre musetast). Dette fyller alle cellene. På dette tidspunktet er resultatene sannsynligvis feil og ikke det du forventet.
5. Nederst til høyre i de resulterende dataene ser du et lite ikon for automatisk utfylling. Klikk på dette ikonet for automatisk fylling
6. Klikk på Flash Fill

Trinnene ovenfor vil gi deg resultatet fra Flash Fill (basert på mønsteret det har utledet).

Du kan også bruke Flash Fill for å få etternavnet eller mellomnavnet. I de to første cellene skriver du inn etternavnet (eller mellomnavnet), og flash fill vil kunne forstå mønsteret

Omorganiser navn ved hjelp av blitsfyll

Flash Fill er et smart verktøy, og det kan også dechiffrere litt komplekse mønstre

Anta for eksempel at du har et datasett som vist nedenfor, og du vil omorganisere navnet fra Rick Novak til Novak, Rick (hvor etternavnet først kommer etterfulgt av et komma og deretter fornavnet).

Nedenfor er trinnene for å gjøre dette:

1. I den tilstøtende cellen skriver du inn manuelt Novak, Rick
2. I den andre cellen skriver du inn manuelt Connor, Susan. Mens du skriver, vil du se Flash Fill vise deg en liste over navnene i samme format (i grått).
3. Når du ser navnene i grått, blar du raskt gjennom det for å sikre at det viser de riktige navnene. Hvis disse er riktige, trykker du på enter -tasten, og Flash Fill vil automatisk fylle resten av cellene med navnene i samme format.

Fjern mellomnavnet (eller bare få mellomnavnet)

Du kan også bruke Flash Fill for å bli kvitt mellomnavnet eller bare få mellomnavnet.

Anta for eksempel at du har et datasett som vist nedenfor, og du vil bare få for- og etternavn og ikke mellomnavn.

Nedenfor er trinnene for å gjøre dette:

1. I den tilstøtende cellen skriver du inn manuelt Rick Novak
2. I den andre cellen skriver du inn manuelt Susan Connor. Mens du skriver, vil du se Flash Fill vise deg en liste over navnene i samme format (i grått).
3. Når du ser navnene i grått, blar du raskt gjennom det for å sikre at det viser de riktige navnene. Hvis disse er riktige, trykker du på Enter -tasten, og Flash Fill vil automatisk fylle resten av cellene med navnene uten mellomnavnet.

På samme måte, hvis du bare vil få mellomnavnene, skriver du mellomnavnet i de to første cellene og bruker Flash Fill for å få mellomnavnet fra alle de gjenværende navnene.

Eksemplene vist i denne opplæringen bruker navn mens du manipulerer tekstdataene. Du kan bruke de samme konseptene til også å jobbe med andre dataformater (for eksempel adresser, produktnavn, etc.)