Separera för- och efternamn i Excel (dela namn med formler)

Titta på video - Dela namn i Excel (i för-, mellan- och efternamn)

Excel är ett fantastiskt verktyg när det gäller att skära och tärna textdata.

Det finns så många användbara formler och funktioner som du kan använda för att arbeta med textdata i Excel.

En av de mycket vanliga frågorna jag får om att manipulera textdata är - ”Hur skiljer man för- och efternamn (eller för-, mellan- och efternamn) i Excel?“.

Det finns ett par enkla sätt att dela namn i Excel. Vilken metod du väljer beror på hur dina data är uppbyggda och om du vill att resultatet ska vara statiskt eller dynamiskt.

Så låt oss komma igång och se olika sätt att dela namn i Excel.

Dela namn med text till kolumner

Funktionen Text till kolumner i Excel låter dig snabbt dela upp textvärden i separata celler i rad.

Anta till exempel att du har datauppsättningen enligt nedan och du vill separera för- och efternamnet och få dessa i separata celler.

Nedan följer stegen för att skilja för- och efternamnet med text till kolumner:

1. Markera alla namn i kolumnen (A2: A10 i det här exemplet)
2. Klicka på fliken "Data"
3. Klicka på alternativet "Text till kolumner" i gruppen "Dataverktyg".
4. Gör följande ändringar i guiden Konvertera text till kolumn:
   1. Steg 1 av 3: Välj Avgränsad (detta låter dig använda utrymme som separator) och klicka på Nästa
   2. Steg 2 av 3: Välj alternativet Mellanslag och klicka på Nästa
   3. Steg 3 av 3: Ange B2 som målcell (annars kommer den att skriva över befintlig data)
5. Klicka på Slutför

Stegen ovan skulle omedelbart dela upp namnen i för- och efternamn (med förnamn i kolumn B och efternamn i kolumn C).

Obs! Om det redan finns data i cellerna (de där text till kolumner förväntas matas ut) kommer Excel att visa dig en varning som låter dig veta att det redan finns data i cellerna. Du kan välja att skriva över data eller avbryta text till kolumner och ta bort den manuellt först.

När du är klar kan du radera fullständiga namndata om du vill.

Några saker att veta när du använder Text till kolumner för att skilja för- och efternamn i Excel:

1. Resultatet av detta är statiskt. Det betyder att om du har ny data eller om den ursprungliga informationen har några ändringar måste du göra det igen för att dela upp namnen.
2. Om du bara vill ha förnamnet eller bara efternamnet kan du hoppa över de kolumner du inte vill ha i steg 3 i dialogrutan "Guiden Text till kolumn". För att göra detta, välj den kolumn i förhandsgranskningen som du vill hoppa över och välj sedan alternativet "Importera inte kolumn (hoppa över)".
3. Om du inte anger målcellen kommer Text till kolumn att skriva över den aktuella kolumnen

Alternativet Text till kolumner passar bäst när du har konsekventa data (till exempel har alla namn endast för- och efternamn eller alla namn har ett för-, mellan- och efternamn).

I det här exemplet har jag visat dig hur man skiljer namn som har utrymme som avgränsare. Om avgränsaren är ett komma eller en kombination av komma och mellanslag kan du fortfarande använda samma steg. I det här fallet kan du ange avgränsaren i steg 2 av 3 i guiden. Det finns redan ett alternativ att använda komma som avgränsare, eller så kan du välja alternativet "Annat" och ange en anpassad avgränsare också.

Även om text till kolumner är ett snabbt och effektivt sätt att dela namn, är det endast lämpligt när du vill att utmatningen ska vara ett statiskt resultat. Om du har en datamängd som kan expandera eller ändras är det bättre att använda formler för att skilja namnen.

Separera förnamn, mellannamn och efternamn med hjälp av formler

Med formler kan du skiva och tärna textdata och extrahera det du vill ha.

I det här avsnittet kommer jag att dela olika formler som du kan använda för att separera namndata (baserat på hur din data är uppbyggd).

Nedan finns de tre formler som du kan använda för att skilja förnamn, mellan- och efternamn (förklaras i detalj senare i följande avsnitt).

Formel för att få förnamnet:

= VÄNSTER (A2, SÖK ("", A2) -1)

Formel för att få mellannamnet:

= MIDDEL (A2, SÖK ("", A2)+1, SÖK ("", ERSÄTTNING (A2, "", "@", 1))-SÖK ("", A2))

Formel för att få efternamnet:

= HÖGER (A15, LEN (A15) -SÖK ("@", ERSÄTTNING (A15, "", "@", LEN (A15) -LEN (ERSTÄLLNING (A15, "", ""))))))

Få förnamnet

Anta att du har datauppsättningen enligt nedan och du vill snabbt separera förnamnet i en cell och efternamn i en cell.

Nedanstående formel ger dig förnamnet:

= VÄNSTER (A2, SÖK ("", A2) -1)

Ovanstående formel använder SEARCH -funktionen för att få platsen för mellanslagstecken mellan för- och efternamnet. Funktionen VÄNSTER använder sedan detta mellanslagsposition för att extrahera all text före den.

Detta är en ganska enkel användning av att extrahera en del av textvärdet. Eftersom allt vi behöver göra är att identifiera den första platsen för mellanslagstecken spelar det ingen roll om namnet har något mellannamn eller inte. Ovanstående formel kommer att fungera bra.

Låt oss nu bli lite mer avancerade med varje exempel.

Få efternamnet

Låt oss säga att du har samma dataset och den här gången måste du få efternamnet.

Nedanstående formel extraherar efternamnet från ovanstående dataset:

= HÖGER (A2, LEN (A2) -SÖK ("", A2))

Återigen, ganska enkelt.

Den här gången hittar vi först platsens teckenposition, som sedan används för att ta reda på antalet tecken som är kvar efter mellanslag (vilket skulle vara efternamnet).

Detta uppnås genom att subtrahera platsvärdet för mellanslagstecknet med det totala antalet tecken i namnet.

Detta nummer används sedan i HÖGER -funktionen för att hämta alla dessa tecken från höger om namnet.

Även om denna formel fungerar bra när det bara finns för- och efternamn, skulle det inte fungera om du också har ett mellannamn. Detta beror på att vi bara stod för ett mellanslagstecken (mellan för- och efternamn). Att ha ett mellannamn lägger till fler blanksteg i namnet.

För att hämta efternamnet när du också har ett mellannamn, använd formeln nedan:

= HÖGER (A15, LEN (A15) -SÖK ("@", ERSÄTTNING (A15, "", "@", LEN (A15) -LEN (ERSTÄLLNING (A15, "", ""))))))

Nu har detta börjat bli lite komplext … eller hur?

Låt mig förklara hur detta fungerar.

Ovanstående formel hittar först det totala antalet blanksteg i namnet. Detta görs genom att få längden på namnet med och utan mellanslagstecken och sedan subtrahera den utan mellanrum från den med mellanslag. Detta ger det totala antalet blanksteg.

SUBSTITUTE -funktionen används sedan för att ersätta det sista mellanslagstecknet med en "@" -symbol (du kan använda valfri symbol - något som osannolikt förekommer som en del av namnet).

När @ -symbolen har ersatts i stället för det sista mellanslagstecknet kan du enkelt hitta positionen för denna @ -symbol. Detta görs med hjälp av SEARCH -funktionen.

Allt du behöver göra är att extrahera alla tecken till höger om denna @ -symbol. Detta görs med hjälp av RIGHT -funktionen.

Få mellannamnet

Anta att du har datauppsättningen enligt nedan och du vill extrahera mellannamnet.

Följande formel gör detta:

= MIDDEL (A2, SÖK ("", A2)+1, SÖK ("", ERSÄTTNING (A2, "", "@", 1))-SÖK ("", A2))

Ovanstående formel använder MID -funktionen, som låter dig ange en startposition och antalet tecken som ska extraheras från den positionen.

Startpositionen är lätt att hitta med hjälp av SEARCH -funktionen.

Det svåra är att hitta hur många tecken som ska extraheras efter denna startposition. För att få detta måste du identifiera hur många tecken det finns från startpositionen till det sista mellanslagstecknet.

Detta kan göras med hjälp av SUBSTITUTE -funktionen och ersätt det sista mellanslagstecknet med en "@" -symbol. När detta är gjort kan du enkelt använda SEARCH -funktionen för att hitta positionen för det sista mellantecknet.

Nu när du har startpositionen och positionen för det sista utrymmet kan du enkelt hämta mellannamnet med hjälp av MID -funktionen.

En av fördelarna med att använda en formel för att separera namnen är att resultatet är dynamiskt. Så om din dataset expanderar och fler namn läggs till eller om några namn ändras i den ursprungliga datamängden behöver du inte oroa dig för den resulterande data.

Separata namn med Sök och ersätt

Jag älskar flexibiliteten som följer med "Hitta och ersätt" - eftersom du kan använda jokertecken i den.

Låt mig först förklara vad en wild card -karaktär är.

Ett jokertecken är något som du kan använda istället för vilken text som helst. Till exempel kan du använda en asterisk -symbol (*) och den kommer att representera valfritt antal tecken i Excel. För att ge dig ett exempel, om jag vill hitta alla namn som börjar med alfabetet A, kan jag använda A* för att hitta och ersätta. Detta kommer att hitta och markera alla celler där namnet börjar med A.

Om du fortfarande inte är tydlig, oroa dig inte. Fortsätt läsa och de närmaste exemplen kommer att göra det tydligt vad jokertecken är och hur man använder dessa för att snabbt skilja namn (eller eventuella textvärden i Excel).

Se till att du skapar en säkerhetskopia av datamängden i alla exemplen nedan. Sök och ersätt ändrar data som den används på. Det är bäst att kopiera och klistra in data först och sedan använda Sök och ersätt i den kopierade datauppsättningen.

Få förnamnet

Anta att du har en dataset som visas nedan och att du bara vill få förnamnet.

Nedan följer stegen för att göra detta:

1. Kopiera namndata i kolumn A och klistra in dem i kolumn B.
2. Med data i kolumn B markerad klickar du på fliken Hem
3. Klicka på Sök och välj i gruppen Redigering.
4. Klicka på Ersätt. Detta öppnar dialogrutan "Sök och ersätt".
5. I dialogrutan "Sök och ersätt" anger du följande
   1. Hitta vad: * (mellanslagstecken följt av asterisk -symbolen)
   2. Ersätt med: lämna detta tomt
6. Klicka på Ersätt alla.

Stegen ovan skulle ge dig förnamnet och ta bort allt efter förnamnet.

Detta fungerar även om du har namn som har ett mellannamn.

Proffstips: Kortkommandot för att öppna dialogrutan Sök och ersätt är Kontroll + H (håll ned kontrollknappen och tryck sedan på H -knappen).

Få efternamnet

Anta att du har en dataset som visas nedan och att du bara vill få efternamnet.

Nedan följer stegen för att göra detta:

1. Kopiera namndata i kolumn A och klistra in dem i kolumn B.
2. Med data i kolumn B markerad klickar du på fliken Hem
3. Klicka på Sök och välj i gruppen Redigering.
4. Klicka på Ersätt. Detta öppnar dialogrutan "Sök och ersätt".
5. I dialogrutan "Sök och ersätt" anger du följande
   1. Hitta vad: * (asterisk -symbol följt av ett mellanslagstecken)
   2. Ersätt med: lämna detta tomt
6. Klicka på Ersätt alla.

Stegen ovan skulle ge dig efternamnet och ta bort allt före förnamnet.

Detta fungerar även om du har namn som har ett mellannamn.

Ta bort mellannamnet

Om du bara vill bli av med mellannamnet och bara har för- och efternamnet kan du göra det med Sök och ersätt.

Anta att du har en dataset som visas nedan och du vill ta bort mellannamnet från dessa.

Nedan följer stegen för att göra detta:

1. Kopiera namndata i kolumn A och klistra in dem i kolumn B.
2. Med data i kolumn B markerad klickar du på fliken Hem
3. Klicka på Sök och välj i gruppen Redigering.
4. Klicka på Ersätt. Detta öppnar dialogrutan "Sök och ersätt".
5. I dialogrutan "Sök och ersätt" anger du följande
   1. Hitta vad: * (mellanslagstecken följt av asterisk -symbolen följt av mellanslagstecken)
   2. Ersätt med: (lägg bara ett mellanslagstecken här)
6. Klicka på Ersätt alla.

Stegen ovan skulle ta bort mellannamnet från ett fullständigt namn. Om vissa namn inte har något mellannamn, skulle de inte ändras.

Separata namn med Flash Fill

Flash fill introducerades i Excel 2013 och gör det väldigt enkelt att ändra eller rengöra en textdatauppsättning.

Och när det gäller att separera namndata ligger det precis i Flash Fill -gränden.

Det viktigaste att veta när du använder Flash Fill är att det behövs ett mönster som flash fill kan identifiera. När det väl har identifierat mönstret hjälper det dig enkelt att dela namn i Excel (du får mer klarhet om detta när du går igenom några exempel nedan).

Få för- eller efternamnet från fullständigt namn

Anta att du har en dataset som visas nedan och du vill bara få förnamnet.

1. I den intilliggande cellen skriver du förnamnet manuellt från det fullständiga namnet. I det här exemplet skulle jag skriva Rick.
2. I den andra cellen skriver du förnamnet manuellt från den intilliggande cellens namn. Medan du skriver ser du Flash Fill som automatiskt visar en lista med förnamnet (i grått).
3. När du ser namnen i grått, bläddra snabbt igenom det för att se till att det visar rätt namn. Om dessa stämmer trycker du på enter -tangenten och Flash Fill fyller automatiskt resten av cellerna med förnamnet.

Flash Fill behöver dig att ge den ett mönster som den kan följa när du ger dig den modifierade informationen. I vårt exempel, när du skriver förnamnet i den första cellen, kan Flash Fill inte räkna ut mönstret.

Men så snart du börjar ange förnamnet i den andra cellen, förstår Flash Fill mönstret och visar dig ett förslag. Om förslaget är korrekt, tryck bara på Enter -tangenten.

Och om det inte är korrekt kan du försöka ange manuellt i några fler celler och kontrollera om Flash Fill kan se mönstret eller inte.

Ibland kanske du inte ser mönstret i grått (som visas i steg 2 ovan). Om så är fallet, följ stegen nedan för att få Flash Fill -resultatet:

1. Ange texten manuellt i två celler.
2. Markera båda dessa celler
3. Håll muspekaren längst ned till höger i markeringen. Du kommer att märka att markören ändras till en plusikon
4. Dubbelklicka på den (vänster musknapp). Detta fyller alla celler. Vid denna tidpunkt är resultaten sannolikt felaktiga och inte vad du förväntat dig.
5. Längst ner till höger om den resulterande informationen ser du en liten ikon för automatisk fyllning. Klicka på denna ikon för automatisk fyllning
6. Klicka på Flash Fill

Stegen ovan skulle ge dig resultatet från Flash Fill (baserat på det mönster som det har härlett).

Du kan också använda Flash Fill för att få efternamnet eller mellannamnet. I de två första cellerna anger du efternamnet (eller mellannamnet) och flashfyllning kommer att kunna förstå mönstret

Ordna om namn med blixtfyllning

Flash Fill är ett smart verktyg och det kan också dechiffrera lite komplexa mönster

Anta till exempel att du har en datamängd som visas nedan och du vill ordna om namnet från Rick Novak till Novak, Rick (där efternamnet kommer först följt av ett kommatecken och sedan förnamnet).

Nedan följer stegen för att göra detta:

1. I den intilliggande cellen skriver du manuellt Novak, Rick
2. I den andra cellen skriver du manuellt Connor, Susan. Medan du skriver ser du Flash Fill som visar dig en lista med namnen i samma format (i grått).
3. När du ser namnen i grått, bläddra snabbt igenom det för att se till att det visar rätt namn. Om dessa stämmer trycker du på enter -tangenten och Flash Fill fyller automatiskt resten av cellerna med namnen i samma format.

Ta bort mellannamnet (eller få bara mellannamnet)

Du kan också använda Flash Fill för att bli av med mellannamnet eller bara få mellannamnet.

Anta till exempel att du har en dataset som visas nedan och att du bara vill få för- och efternamnet och inte mellannamnet.

Nedan följer stegen för att göra detta:

1. I den intilliggande cellen skriver du manuellt Rick Novak
2. I den andra cellen skriver du manuellt Susan Connor. Medan du skriver kommer du att se Flash Fill visa dig en lista med namnen i samma format (i grått).
3. När du ser namnen i grått, bläddra snabbt igenom det för att se till att det visar rätt namn. Om dessa stämmer trycker du på enter -tangenten och Flash Fill fyller automatiskt resten av cellerna med namnen utan mellannamnet.

På samma sätt, om du bara vill få mellannamnen, skriver du mittnamnet i de två första cellerna och använder Flash Fill för att få mellannamnet från alla återstående namn.

Exemplen som visas i den här självstudien använder namn vid manipulering av textdata. Du kan använda samma begrepp för att även arbeta med andra dataformat (t.ex. adresser, produktnamn, etc.)