Odvojite ime i prezime u Excelu (podijelite imena pomoću formula)

Gledajte video - Podijelite imena u Excelu (na ime, srednje i prezime)

Excel je nevjerojatan alat kada je riječ o rezanju i izrezivanju tekstualnih podataka.

Postoji toliko korisnih formula i funkcija koje možete koristiti za rad s tekstualnim podacima u Excelu.

Jedno od vrlo čestih pitanja koje dobivam o manipulaciji tekstualnim podacima je - “Kako odvojiti ime i prezime (ili ime, srednje i prezime) u Excelu?“.

Postoji nekoliko jednostavnih načina za podjelu imena u Excelu. Metoda koju odaberete ovisit će o tome kako su vaši podaci strukturirani i želite li da rezultat bude statičan ili dinamičan.

Pa počnimo i vidimo različite načine podjele imena u Excelu.

Podijelite imena pomoću teksta u stupce

Funkcija Text to Columns u Excelu omogućuje vam brzo razdvajanje tekstualnih vrijednosti u zasebne ćelije u nizu.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite odvojiti ime i prezime i unijeti ih u zasebne ćelije.

U nastavku su navedeni koraci za odvajanje imena i prezimena pomoću teksta u stupce:

1. Odaberite sva imena u stupcu (A2: A10 u ovom primjeru)
2. Kliknite karticu "Podaci"
3. U grupi "Data Tools" kliknite opciju "Text to Columns".
4. Napravite sljedeće promjene u čarobnjaku Pretvori tekst u stupac:
   1. Korak 1 od 3: Odaberite Razgraničeno (ovo vam omogućuje da koristite razmak kao razdjelnik) i kliknite Dalje
   2. Korak 2 od 3: Odaberite opciju Razmak i kliknite Dalje
   3. Korak 3 od 3: Postavite B2 kao odredišnu ćeliju (inače će prebrisati postojeće podatke)
5. Kliknite na Finish

Gore navedeni koraci odmah bi podijelili imena na ime i prezime (s imenima u stupcu B i prezimenom u stupcu C).

Napomena: U slučaju da u ćelijama već postoje neki podaci (oni gdje se očekuje izlaz teksta u stupce), Excel će vam prikazati upozorenje koje vas obavještava da već postoje neki podaci u ćelijama. Možete odabrati prebrisati podatke ili otkazati tekst u stupce i prvo ih ručno ukloniti.

Kada to učinite, možete izbrisati podatke o punom imenu ako želite.

Nekoliko stvari koje trebate znati kada koristite tekst u stupce za odvajanje imena i prezimena u Excelu:

1. Rezultat toga je statičan. To znači da u slučaju da imate nove podatke ili izvorni podaci imaju neke promjene, morate to ponoviti kako biste podijelili imena.
2. Ako želite samo ime ili samo prezime, možete preskočiti stupce koje ne želite u 3. koraku dijaloškog okvira "Čarobnjak za tekst u stupce". Da biste to učinili, odaberite stupac u pregledu koji želite preskočiti, a zatim odaberite opciju "Ne uvozi stupac (preskoči)".
3. U slučaju da ne navedete odredišnu ćeliju, Tekst u stupac će prebrisati trenutni stupac

Opcija Tekst u stupce najbolje odgovara kada imate dosljedne podatke (na primjer, svi nazivi imaju samo ime i prezime ili svi nazivi imaju ime, srednje i prezime).

U ovom primjeru sam vam pokazao kako odvojiti imena koja imaju razmak kao graničnik. U slučaju da je graničnik zarez ili kombinacija zareza i razmaka, još uvijek možete koristiti iste korake. U tom slučaju možete odrediti graničnik u koraku 2 od 3 čarobnjaka. Već postoji mogućnost korištenja zareza kao graničnika ili možete odabrati opciju 'Ostalo' i navesti prilagođeni razdjelnik.

Iako je tekst u stupce brz i učinkovit način za podjelu imena, prikladan je samo kada želite da izlaz bude statički rezultat. U slučaju da imate skup podataka koji se može proširiti ili promijeniti, bolje je koristiti formule za odvajanje naziva.

Odvojite ime, srednje i prezime pomoću formula

Formule vam omogućuju da isečete i isečete tekstualne podatke i izvučete ono što želite.

U ovom odjeljku podijelit ću različite formule pomoću kojih možete odvojiti podatke o imenima (ovisno o strukturi podataka).

U nastavku su tri formule pomoću kojih možete odvojiti ime, srednje i prezime (detaljno objašnjeno kasnije u sljedećim odjeljcima).

Formula za dobivanje imena:

= LIJEVO (A2, TRAŽI ("", A2) -1)

Formula za dobivanje srednjeg imena:

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

Formula za dobivanje prezimena:

= DESNO (A15, LEN (A15) -TRAGANJE ("@", ZAMJENA (A15, "", "@", LEN (A15) -LEN (ZAMJENA (A15, "", ""))))

Dobijte ime

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brzo odvojiti ime u jednoj ćeliji i prezime u jednoj ćeliji.

Formula u nastavku će vam dati ime:

= LIJEVO (A2, TRAŽI ("", A2) -1)

Gornja formula koristi funkciju SEARCH za dobivanje pozicije razmaka između imena i prezimena. Funkcija LIJEVO tada koristi ovaj broj razmaka za izdvajanje cijelog teksta prije njega.

Ovo je prilično jednostavna upotreba izdvajanja dijela tekstualne vrijednosti. Budući da sve što trebamo učiniti je identificirati prvu poziciju razmaka, nije važno ima li ime srednje ime ili ne. Gornja formula dobro će funkcionirati.

Idemo sada napredovati sa svakim primjerom.

Uzmi prezime

Recimo da imate isti skup podataka i ovaj put morate dobiti prezime.

Formula u nastavku izvući će prezime iz gornjeg skupa podataka:

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

Opet, sasvim jasno.

Ovaj put prvo pronalazimo poziciju razmaka, koja se zatim koristi za doznavanje broja znakova koji su ostali nakon razmaka (što bi bilo prezime).

To se postiže oduzimanjem vrijednosti pozicije razmaka s ukupnim brojem znakova u imenu.

Taj se broj zatim koristi u funkciji DESNO za dohvaćanje svih ovih znakova s ​​desne strane imena.

Iako ova formula odlično funkcionira kada postoje samo ime i prezime, ne bi uspjela u slučaju da imate i srednje ime. To je zato što smo uzeli u obzir samo jedan razmak (između imena i prezimena). Srednje ime dodaje imenu više razmaka.

Da biste dohvatili prezime kada imate i srednje ime, upotrijebite donju formulu:

= DESNO (A15, LEN (A15) -TRAGANJE ("@", ZAMJENA (A15, "", "@", LEN (A15) -LEN (ZAMJENA (A15, "", ""))))

Ovo je počelo postajati pomalo složeno … zar ne?

Dopustite mi da objasnim kako to funkcionira.

Gornja formula prvo pronalazi ukupni broj razmaka u imenu. To se postiže dobivanjem duljine imena sa i bez razmaka, a zatim se oduzima ono bez razmaka od onog s razmakom. To daje ukupan broj razmaka.

Funkcija SUBSTITUTE tada se koristi za zamjenu zadnjeg razmaka znakom ‘@’ (možete koristiti bilo koji simbol - nešto što se vjerojatno neće pojaviti kao dio imena).

Nakon što je simbol @ zamijenjen zadnjim razmakom, lako ćete pronaći položaj ovog simbola @. To se radi pomoću funkcije SEARCH.

Sada sve što trebate učiniti je izdvojiti sve znakove desno od ovog @ simbola. To se radi pomoću funkcije DESNO.

Uzmi srednje ime

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izdvojiti srednje ime.

Sljedeća formula će to učiniti:

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

Gornja formula koristi funkciju MID, koja vam omogućuje da navedete početnu poziciju i broj znakova za izdvajanje iz te pozicije.

Početni položaj lako je pronaći pomoću funkcije SEARCH.

Najteži dio je pronaći koliko znakova izdvojiti nakon ove početne pozicije. Da biste to dobili, morate identificirati koliko znakova ima od početne pozicije do posljednjeg razmaka.

To se može učiniti pomoću funkcije ZAMJENA i zamijeniti posljednji razmak znakom '@'. Nakon što to učinite, možete jednostavno upotrijebiti funkciju SEARCH za pronalaženje položaja ovog zadnjeg razmaka.

Sada kada imate početni položaj i položaj posljednjeg razmaka, možete lako dohvatiti srednje ime pomoću funkcije MID.

Jedna od prednosti korištenja formule za odvajanje naziva je to što je rezultat dinamičan. Dakle, u slučaju da se vaš skup podataka proširi i da mu se doda još imena ili ako se neki nazivi promijene u izvornom skupu podataka, ne morate brinuti o rezultirajućim podacima.

Odvojena imena pomoću funkcije Pronađi i zamijeni

Volim fleksibilnost koja dolazi s "Pronađi i zamijeni" - jer u njoj možete koristiti zamjenske znakove.

Dopustite mi prvo da objasnim što je to wild card karakter.

Zamjenski znak je nešto što možete koristiti umjesto bilo kojeg teksta. Na primjer, možete koristiti simbol zvjezdice (*) i on će predstavljati bilo koji broj znakova u Excelu. Da vam dam primjer, ako želim pronaći sva imena koja počinju s abecedom A, mogu upotrijebiti A* za pronalaženje i zamjenu. Ovo će pronaći i odabrati sve ćelije u kojima ime počinje s A.

Ako vam i dalje nije jasno, ne brinite. Nastavite čitati i sljedećih nekoliko primjera će vam pojasniti što su zamjenski znakovi i kako ih koristiti za brzo odvajanje imena (ili bilo koje tekstualne vrijednosti u Excelu).

U svim dolje navedenim primjerima svakako napravite sigurnosnu kopiju skupa podataka. Find and Replace mijenja podatke na kojima se koristi. Najbolje je prvo kopirati i zalijepiti podatke, a zatim upotrijebiti Find and Replace na kopiranom skupu podataka.

Dobijte ime

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dobiti samo ime.

U nastavku su navedeni koraci za to:

1. Kopirajte podatke o imenu u stupac A i zalijepite ih u stupac B.
2. S odabranim podacima u stupcu B kliknite karticu Početna
3. U grupi Uređivanje kliknite Nađi i odaberi.
4. Pritisnite Zamijeni. Ovo će otvoriti dijaloški okvir "Pronađi i zamijeni".
5. U dijaloški okvir "Pronađi i zamijeni" unesite sljedeće
   1. Pronađite što: * (razmak nakon kojeg slijedi simbol zvjezdice)
   2. Zamijeni sa: ovo polje ostavite prazno
6. Pritisnite Zamijeni sve.

Gore navedeni koraci dali bi vam ime i uklonili sve iza imena.

Ovo funkcionira čak i ako imate imena koja imaju srednje ime.

Profesionalni savjet: Tipkovni prečac za otvaranje dijaloškog okvira Pronađi i zamijeni jest Control + H (držite kontrolnu tipku, a zatim pritisnite tipku H).

Uzmi prezime

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dobiti samo prezime.

U nastavku su navedeni koraci za to:

1. Kopirajte podatke o imenu u stupac A i zalijepite ih u stupac B.
2. S odabranim podacima u stupcu B kliknite karticu Početna
3. U grupi Uređivanje kliknite Nađi i odaberi.
4. Pritisnite Zamijeni. Ovo će otvoriti dijaloški okvir "Pronađi i zamijeni".
5. U dijaloški okvir "Pronađi i zamijeni" unesite sljedeće
   1. Pronađite što: * (simbol zvjezdice iza kojeg slijedi razmak)
   2. Zamijeni sa: ovo polje ostavite prazno
6. Pritisnite Zamijeni sve.

Gore navedeni koraci dali bi vam prezime i uklonili sve prije imena.

Ovo funkcionira čak i ako imate imena koja imaju srednje ime.

Uklonite Srednje ime

U slučaju da se želite riješiti samo srednjeg imena i imati samo ime i prezime, to možete učiniti pomoću značajke Pronađi i zamijeni.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite ukloniti srednje ime iz njih.

U nastavku su navedeni koraci za to:

1. Kopirajte podatke o imenu u stupac A i zalijepite ih u stupac B.
2. S odabranim podacima u stupcu B kliknite karticu Početna
3. U grupi Uređivanje kliknite Nađi i odaberi.
4. Pritisnite Zamijeni. Ovo će otvoriti dijaloški okvir "Pronađi i zamijeni".
5. U dijaloški okvir "Pronađi i zamijeni" unesite sljedeće
   1. Pronađite što: * (znak razmaka nakon kojeg slijedi simbol zvjezdice, a zatim znak razmaka)
   2. Zamijenite s: (ovdje samo stavite razmak)
6. Pritisnite Zamijeni sve.

Gore navedeni koraci uklonili bi srednje ime iz punog imena. U slučaju da neka imena nemaju srednje ime, ne bi se mijenjala.

Odvojena imena pomoću Flash Fill -a

Flash fill predstavljen je u programu Excel 2013 i olakšava izmjenu ili čišćenje skupa tekstualnih podataka.

A što se tiče odvajanja podataka o imenima, to je točno u uličici Flash Fill -a.

Najvažnija stvar koju trebate znati pri korištenju Flash Fill -a je da mora postojati uzorak koji Flash fill može identificirati. Nakon što identificira uzorak, lako će vam pomoći podijeliti imena u Excelu (ovo će vam biti jasnije kada prođete kroz nekoliko primjera u nastavku).

Dobijte ime ili prezime od punog imena

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dobiti samo ime.

1. U susjednu ćeliju ručno upišite ime iz punog imena. U ovom primjeru upisao bih Ricka.
2. U drugoj ćeliji ručno upišite prvo ime iz naziva susjedne ćelije. Dok tipkate, vidjet ćete da vam Flash Fill automatski prikazuje popis imena (sivo).
3. Kad vidite imena u sivoj boji, brzo ga pregledajte kako biste bili sigurni da prikazuje prava imena. Ako su točne, pritisnite tipku enter i Flash Fill će automatski popuniti ostale ćelije imenom.

Flash Fill mora dati obrazac koji može slijediti kada vam daje izmijenjene podatke. U našem primjeru, kada upišete ime u prvu ćeliju, Flash Fill ne može shvatiti uzorak.

No, čim počnete unositi ime u drugu ćeliju, Flash Fill razumije uzorak i pokaže vam neki prijedlog. Ako je prijedlog točan, samo pritisnite tipku enter.

A ako nije točno, možete pokušati ručno unijeti još nekoliko ćelija i provjeriti može li Flash Fill razaznati uzorak ili ne.

Ponekad možda nećete vidjeti uzorak u sivoj boji (kao što je prikazano u koraku 2 gore). U tom slučaju slijedite korake u nastavku da biste dobili rezultat Flash Fill:

1. Unesite tekst ručno u dvije ćelije.
2. Odaberite obje ove ćelije
3. Zadržite pokazivač na donjem desnom dijelu odabira. Primijetit ćete da se kursor mijenja u ikonu plus
4. Dvaput kliknite na nju (lijeva tipka miša). Time će se ispuniti sve ćelije. U ovom trenutku rezultati su vjerojatno netočni i nisu ono što ste očekivali.
5. U donjem desnom kutu rezultirajućih podataka vidjet ćete malu ikonu Automatskog popunjavanja. Kliknite na ovu ikonu Automatsko popunjavanje
6. Kliknite na Flash Fill

Gore navedeni koraci dali bi vam rezultat iz Flash Fill -a (na temelju uzorka koji je zaključio).

Također možete upotrijebiti Flash Fill da biste dobili prezime ili srednje ime. U prve dvije ćelije unesite prezime (ili srednje ime) i bljeskalica će moći razumjeti uzorak

Promijenite naziv pomoću Flash Fill -a

Flash Fill je pametan alat koji može dešifrirati i pomalo složene uzorke

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite preurediti ime iz Ricka Novaka u Novak, Rick (gdje prezime dolazi prvo nakon čega slijedi zarez, a zatim ime).

U nastavku su navedeni koraci za to:

1. U susjednu ćeliju ručno upišite Novak, Rick
2. U drugu ćeliju ručno upišite Connor, Susan. Dok tipkate, vidjet ćete da vam Flash Fill prikazuje popis imena u istom formatu (sivom bojom).
3. Kad vidite imena u sivoj boji, brzo ga pregledajte kako biste bili sigurni da prikazuje prava imena. Ako su točne, pritisnite tipku enter i Flash Fill će automatski ispuniti ostale ćelije imenima u istom formatu.

Uklonite srednje ime (ili samo preuzmite srednje ime)

Također možete upotrijebiti Flash Fill da biste se riješili srednjeg imena ili dobili samo srednje ime.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite dobiti samo ime i prezime, a ne srednje ime.

U nastavku su navedeni koraci za to:

1. U susjednu ćeliju ručno upišite Rick Novak
2. U drugu ćeliju ručno upišite Susan Connor. Dok tipkate, vidjet ćete da vam Flash Fill prikazuje popis imena u istom formatu (sivom bojom).
3. Kad vidite imena u sivoj boji, brzo ga pregledajte kako biste bili sigurni da prikazuje prava imena. Ako su točne, pritisnite tipku enter i Flash Fill će automatski popuniti ostale ćelije s imenima bez srednjeg imena.

Slično, ako samo želite dobiti srednja imena, upišite srednje ime u prve dvije ćelije i upotrijebite Flash Fill da biste dobili srednje ime iz svih preostalih imena.

Primjeri prikazani u ovom vodiču koriste imena dok manipuliraju tekstualnim podacima. Iste koncepte možete koristiti i za rad s drugim formatima podataka (poput adresa, naziva proizvoda itd.)