Ločite ime in priimek v Excelu (delite imena s formulami)

Oglejte si video - Razdelite imena v Excelu (na ime, ime in priimek)

Excel je neverjetno orodje, ko gre za rezanje in rezanje besedilnih podatkov.

Obstaja toliko uporabnih formul in funkcij, ki jih lahko uporabite za delo z besedilnimi podatki v Excelu.

Eno najpogostejših vprašanj, ki jih dobim o manipulaciji besedilnih podatkov, je - "Kako ločiti ime in priimek (ali ime, priimek in priimek) v Excelu?“.

Obstaja nekaj preprostih načinov za razdelitev imen v Excelu. Izbrana metoda bo odvisna od tega, kako so podatki strukturirani in ali želite, da je rezultat statičen ali dinamičen.

Začnimo torej in poglejmo različne načine razdelitve imen v Excelu.

Razdelite imena z uporabo besedila v stolpce

Funkcija Besedilo v stolpce v Excelu vam omogoča hitro razdelitev besedilnih vrednosti v ločene celice v vrsti.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite ločiti ime in priimek ter jih dati v ločene celice.

Spodaj so navedeni koraki za ločitev imena in priimka z besedilom v stolpce:

1. Izberite vsa imena v stolpcu (A2: A10 v tem primeru)
2. Kliknite zavihek »Podatki«
3. V skupini »Podatkovna orodja« kliknite možnost »Besedilo v stolpce«.
4. V čarovniku za pretvorbo besedila v stolpec naredite naslednje spremembe:
   1. Korak 1 od 3: Izberite Delimited (to vam omogoča, da uporabite prostor kot ločilo) in kliknite Naprej
   2. 2. korak od 3: Izberite možnost Preslednica in kliknite Naprej
   3. Korak 3 od 3: Nastavite B2 kot ciljno celico (sicer bo prepisala obstoječe podatke)
5. Kliknite Dokončaj

Zgornji koraki bi takoj razdelili imena na ime in priimek (z imeni v stolpcu B in priimkom v stolpcu C).

Opomba: Če so v celicah že podatki (tisti, pri katerih se pričakuje izpis besedila v stolpce), vam bo Excel pokazal opozorilo, s katerim boste vedeli, da so v celicah že nekateri podatki. Izberete lahko prepis podatkov ali preklic besedila v stolpce in ga najprej ročno odstranite.

Ko končate, lahko po želji izbrišete podatke o polnem imenu.

Pri ločevanju imen in priimkov v Excelu morate uporabiti nekaj besed v stolpcih:

1. Rezultat tega je statičen. To pomeni, da morate v primeru, da imate nove podatke ali pa so se prvotni podatki spremenili, to ponoviti, da razdelite imena.
2. Če želite samo ime ali samo priimek, lahko preskočite stolpce, ki jih ne želite v 3. koraku pogovornega okna »Čarovnik za besedilo v stolpec«. To storite tako, da v predogledu izberete stolpec, ki ga želite preskočiti, nato pa izberite možnost »Ne uvozi stolpca (preskoči)«.
3. Če ne določite ciljne celice, bo besedilo v stolpec prepisalo trenutni stolpec

Možnost Besedilo v stolpce je najbolj primerna, če imate dosledne podatke (na primer vsa imena imajo samo ime in priimek ali vsa imena imajo ime, priimek in priimek).

V tem primeru sem vam pokazal, kako ločiti imena, ki imajo prostor kot ločilo. Če je ločilo vejica ali kombinacija vejice in presledka, lahko še vedno uporabite iste korake. V tem primeru lahko ločitelec določite v 2. koraku od 3. čarovnika. Že obstaja možnost uporabe vejice kot ločevalnika ali pa izberete možnost »Drugo« in določite tudi ločilo po meri.

Čeprav je besedilo v stolpce hiter in učinkovit način za razdelitev imen, je primeren le, če želite, da je izhod statičen rezultat. Če imate nabor podatkov, ki se lahko razširi ali spremeni, raje uporabite formule za ločevanje imen.

Ločite ime, srednje in priimek s formulami

Formule vam omogočajo, da razrežete in narežete besedilne podatke in izvlečete, kar želite.

V tem razdelku bom predstavil različne formule, s katerimi lahko ločite podatke o imenih (glede na to, kako so vaši podatki strukturirani).

Spodaj so tri formule, s katerimi lahko ločite ime, priimek in priimek (podrobno razloženo kasneje v naslednjih razdelkih).

Formula za pridobitev imena:

= LEVO (A2, ISKANJE ("", A2) -1)

Formula za srednje ime:

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

Formula za priimek:

= DESNO (A15, LEN (A15) -ISKANJE ("@", ZAMENJA (A15, "", "@", LEN (A15) -LEN (ZAMENIK (A15, "", ""))))

Pridobite ime

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite hitro ločiti ime v eni celici in priimek v eni celici.

Spodnja formula vam bo dala ime:

= LEVO (A2, ISKANJE ("", A2) -1)

Zgornja formula uporablja funkcijo SEARCH, da dobi položaj presledka med imenom in priimkom. Funkcija LEFT nato uporabi to številko presledka za izvlečenje vsega besedila pred njo.

To je dokaj preprosta uporaba izvlečenja dela besedilne vrednosti. Ker moramo le identificirati prvi znak presledka, ni pomembno, ali ima ime srednje ime ali ne. Zgornja formula bo delovala v redu.

Zdaj pa z vsakim primerom malo napredujmo.

Pridobite priimek

Recimo, da imate isti nabor podatkov in tokrat morate dobiti priimek.

Spodnja formula bo izvlekla priimek iz zgornjega nabora podatkov:

= DESNO (A2, LEN (A2) -SEARCH ("", A2))

Še enkrat, zelo preprosto.

Tokrat najprej najdemo preslednico, ki se nato uporabi za ugotavljanje števila znakov, ki ostanejo za presledkom (kar bi bil priimek).

To dosežemo z odštevanjem vrednosti položaja presledka s skupnim številom znakov v imenu.

Ta številka se nato uporabi v funkciji DESNO za pridobivanje vseh teh znakov z desne strani imena.

Čeprav ta formula odlično deluje, če obstajata samo ime in priimek, ne bi delovala, če imate tudi srednje ime. To je zato, ker smo upoštevali le en presledek (med imenom in priimkom). Ob srednjem imenu se imenu doda več presledkov.

Če želite dobiti priimek tudi pri srednjem imenu, uporabite spodnjo formulo:

= DESNO (A15, LEN (A15) -ISKANJE ("@", ZAMENJA (A15, "", "@", LEN (A15) -LEN (ZAMENIK (A15, "", ""))))

Zdaj se je to začelo nekoliko zapletati … kajne?

Naj razložim, kako to deluje.

Zgornja formula najprej najde skupno število presledkov v imenu. To naredimo tako, da dolžino imena dobimo s presledkom in brez, nato pa tistega brez presledka odštejemo od tistega s presledkom. To daje skupno število presledkov.

Funkcija SUBSTITUTE se nato uporabi za zamenjavo zadnjega presledka s simbolom '@' (lahko uporabite kateri koli simbol - nekaj, kar se verjetno ne bo pojavilo kot del imena).

Ko je simbol @ zamenjan namesto zadnjega presledka, lahko preprosto najdete položaj tega simbola @. To naredite s funkcijo SEARCH.

Zdaj morate le izvleči vse znake desno od tega simbola @. To naredite s funkcijo DESNO.

Pridobite srednje ime

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izvleči srednje ime.

Naslednja formula bo to naredila:

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

Zgornja formula uporablja funkcijo MID, ki vam omogoča, da določite začetni položaj in število znakov, ki jih želite izvleči iz tega položaja.

Začetni položaj je enostavno najti s funkcijo SEARCH.

Najtežje je najti, koliko znakov je treba izločiti po tem začetnem položaju. Če želite to narediti, morate ugotoviti, koliko znakov je od začetnega položaja do zadnjega presledka.

To lahko storite s funkcijo SUBSTITUTE in zadnji presledek zamenjate s simbolom '@'. Ko to storite, lahko preprosto uporabite funkcijo SEARCH za iskanje položaja tega zadnjega presledka.

Zdaj, ko imate začetni položaj in položaj zadnjega presledka, lahko preprosto uporabite srednje ime s funkcijo MID.

Ena od prednosti uporabe formule za ločevanje imen je, da je rezultat dinamičen. Torej, če se vaš nabor podatkov razširi in mu dodate več imen ali če se v prvotnem naboru podatkov spremenijo nekatera imena, vam ni treba skrbeti za nastale podatke.

Ločena imena z uporabo Find and Replace

Všeč mi je fleksibilnost, ki jo prinaša „Najdi in zamenjaj“ - ker lahko v njem uporabite znake nadomestnih znakov.

Naj najprej razložim, kaj je znak wildcard.

Nadomestni znak je nekaj, kar lahko uporabite namesto katerega koli besedila. Na primer, lahko uporabite simbol zvezdice (*) in bo predstavljal poljubno število znakov v Excelu. Če želite navesti primer, če želim najti vsa imena, ki se začnejo z abecedo A, lahko uporabim A* pri iskanju in zamenjavi. S tem boste našli in izbrali vse celice, kjer se ime začne z A.

Če vam še vedno ni jasno, ne skrbite. Nadaljujte z branjem in v naslednjih nekaj primerih bo jasno, kaj so nadomestni znaki in kako jih uporabiti za hitro ločevanje imen (ali besedilnih vrednosti v Excelu).

V vseh spodaj navedenih primerih ustvarite varnostno kopijo nabora podatkov. Poišči in zamenjaj spremeni podatke, za katere se uporablja. Najbolje je, da podatke najprej kopirate in prilepite, nato pa v iskanem nizu uporabite funkcijo Najdi in zamenjaj.

Pridobite ime

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite dobiti samo ime.

Spodaj so navedeni koraki za to:

1. Kopirajte podatke o imenu v stolpec A in jih prilepite v stolpec B.
2. Ko so podatki v stolpcu B izbrani, kliknite zavihek Domov
3. V skupini Urejanje kliknite Poišči in izberite.
4. Kliknite na Zamenjaj. S tem se odpre pogovorno okno »Poišči in zamenjaj«.
5. V pogovornem oknu »Poišči in zamenjaj« vnesite naslednje
   1. Poiščite kaj: * (presledek, ki mu sledi simbol zvezdice)
   2. Zamenjaj z: pustite prazno
6. Kliknite Zamenjaj vse.

Zgornji koraki bi vam dali ime in odstranili vse za imenom.

To deluje, tudi če imate imena s srednjim imenom.

Nasvet za profesionalce: Bližnjica na tipkovnici za odpiranje pogovornega okna Poišči in zamenjaj je Control + H (držite kontrolno tipko in nato pritisnite tipko H).

Pridobite priimek

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite dobiti samo priimek.

Spodaj so navedeni koraki za to:

1. Kopirajte podatke o imenu v stolpec A in jih prilepite v stolpec B.
2. Ko so podatki v stolpcu B izbrani, kliknite zavihek Domov
3. V skupini Urejanje kliknite Poišči in izberite.
4. Kliknite na Zamenjaj. S tem se odpre pogovorno okno »Poišči in zamenjaj«.
5. V pogovornem oknu »Poišči in zamenjaj« vnesite naslednje
   1. Poiščite kaj: * (simbol zvezdice, ki mu sledi presledek)
   2. Zamenjaj z: pustite prazno
6. Kliknite Zamenjaj vse.

Zgornji koraki bi vam dali priimek in odstranili vse pred imenom.

To deluje, tudi če imate imena s srednjim imenom.

Odstranite srednje ime

Če se želite znebiti samo srednjega imena in imate samo ime in priimek, lahko to storite s funkcijo Poišči in Zamenjaj.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite odstraniti srednje ime iz teh.

Spodaj so navedeni koraki za to:

1. Kopirajte podatke o imenu v stolpec A in jih prilepite v stolpec B.
2. Ko so podatki v stolpcu B izbrani, kliknite zavihek Domov
3. V skupini Urejanje kliknite Poišči in izberite.
4. Kliknite na Zamenjaj. S tem se odpre pogovorno okno »Poišči in zamenjaj«.
5. V pogovornem oknu »Poišči in zamenjaj« vnesite naslednje
   1. Poiščite kaj: * (presledek, ki mu sledi zvezdica, ki mu sledi presledek)
   2. Zamenjaj z: (tukaj vnesite presledek)
6. Kliknite Zamenjaj vse.

Zgornji koraki bi odstranili srednje ime iz polnega imena. Če nekatera imena nimajo srednjega imena, se ne bi spremenila.

Ločena imena z uporabo Flash Fill

Flash fill je bil predstavljen v Excelu 2013 in olajša spreminjanje ali čiščenje nabora besedilnih podatkov.

Ko gre za ločevanje podatkov o imenih, je to zgoraj v uličici Flash Fill.

Najpomembnejša stvar, ki jo morate vedeti pri uporabi bliskovnega polnjenja, je, da mora obstajati vzorec, ki ga lahko zazna polnjenje bliskavice. Ko bo identificiral vzorec, vam bo z lahkoto pomagal razdeliti imena v Excelu (o tem boste dobili več jasnosti, ko boste preučili nekaj primerov spodaj).

Pridobite ime ali priimek od polnega imena

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite dobiti samo ime.

1. V sosednjo celico ročno vnesite ime iz polnega imena. V tem primeru bi vnesel Ricka.
2. V drugo celico ročno vnesite ime iz imena sosednje celice. Med tipkanjem boste videli, da Flash Fill samodejno prikaže seznam imena (v sivi barvi).
3. Ko vidite imena v sivi barvi, jih hitro poglejte, da se prepričate, ali prikazuje prava imena. Če so te pravilne, pritisnite tipko enter in Flash Fill bo samodejno zapolnil preostale celice z imenom.

Flash Fill mora dati vzorec, ki mu lahko sledi, ko vam posreduje spremenjene podatke. V našem primeru, ko v prvo celico vnesete ime, Flash Fill ne more ugotoviti vzorca.

Toda takoj, ko začnete vnašati ime v drugo celico, Flash Fill razume vzorec in vam pokaže nekaj predlogov. Če je predlog pravilen, samo pritisnite tipko enter.

Če ni pravilno, lahko poskusite ročno vnesti še nekaj celic in preverite, ali Flash Fill lahko zazna vzorec ali ne.

Včasih morda ne vidite vzorca v sivi barvi (kot je prikazano v 2. koraku zgoraj). V tem primeru sledite spodnjim korakom, da dobite rezultat Flash Fill:

1. Besedilo vnesite ročno v dve celici.
2. Izberite obe celici
3. Premaknite kazalec na spodnji desni del izbire. Opazili boste, da se kazalec spremeni v ikono plus
4. Dvokliknite nanjo (leva tipka miške). S tem boste napolnili vse celice. V tem trenutku so rezultati verjetno napačni in niso takšni, kot ste pričakovali.
5. V spodnjem desnem kotu nastalih podatkov boste videli majhno ikono samodejnega izpolnjevanja. Kliknite to ikono za samodejno izpolnjevanje
6. Kliknite na Flash Fill

Zgornji koraki bi dali rezultat iz Flash Fill (glede na vzorec, ki ga je izvedel).

Za priimek ali srednje ime lahko uporabite tudi Flash Fill. V prvih dveh celicah vnesite priimek (ali srednje ime) in bliskavica bo lahko razumela vzorec

Preuredite ime z uporabo Flash Fill

Flash Fill je pametno orodje, ki lahko dešifrira tudi nekoliko zapleten vzorec

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite ime preurediti iz Ricka Novaka v Novaka, Ricka (kjer je najprej priimek, ki mu sledi vejica in nato ime).

Spodaj so navedeni koraki za to:

1. V sosednjo celico ročno vnesite Novak, Rick
2. V drugo celico ročno vnesite Connor, Susan. Med tipkanjem boste videli, da Flash Fill prikaže seznam imen v isti obliki (v sivi barvi).
3. Ko vidite imena v sivi barvi, jih hitro poglejte, da se prepričate, ali prikazuje prava imena. Če so te pravilne, pritisnite tipko enter in Flash Fill bo samodejno zapolnil preostale celice z imeni v isti obliki.

Odstranite srednje ime (ali pa samo dobite srednje ime)

Uporabite lahko tudi Flash Fill, da se znebite srednjega imena ali dobite samo srednje ime.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite dobiti le ime in priimek, ne pa srednjega imena.

Spodaj so navedeni koraki za to:

1. V sosednjo celico ročno vnesite Rick Novak
2. V drugo celico ročno vnesite Susan Connor. Med tipkanjem boste videli, da Flash Fill prikaže seznam imen v isti obliki (v sivi barvi).
3. Ko vidite imena v sivi barvi, jih hitro poglejte, da se prepričate, ali prikazuje prava imena. Če so te pravilne, pritisnite tipko enter in Flash Fill bo samodejno zapolnil preostale celice z imeni brez srednjega imena.

Podobno, če želite dobiti le srednja imena, vnesite srednje ime v prvi dve celici in uporabite Flash Fill, da dobite srednje ime iz vseh preostalih imen.

Primeri, prikazani v tem priročniku, pri imenovanju besedilnih podatkov uporabljajo imena. Iste koncepte lahko uporabite tudi za delo z drugimi oblikami podatkov (kot so naslovi, imena izdelkov itd.)