Kako odvojiti ime i prezime u Excelu

Možete koristiti Excel za razbijanje informacija na manje dijelove. Pronalaženje potrebnih podataka i rukovanje njima važan je cilj za mnoge korisnike programa Excel.

Ako imate puno ime osobe, možda ćete morati precizirati samo njeno ime ili prezime. Na primjer, ako svojim klijentima šaljete prijateljsku automatiziranu e-poštu, trebate koristiti njihova imena kako ne biste zvučali bezlično. Ako pregledavate popis ispitanika u anketi, možda bi bilo važno upotrijebiti samo njihova prezimena ili sakriti njihova prezimena kako biste zadržali anonimnost.

Excel ovaj proces čini jednostavnim, a postoji mnogo različitih pristupa koje možete primijeniti. Ovdje je vodič koji će vam pomoći da izradite zasebne stupce Ime i Prezime pomoću formula. Pokrivamo i pitanje srednjih imena.

Excel formule za dijeljenje imena na dijelove

Gdje početi?

Razdvajanje imena

Ovo je generička formula:

=LIJEVO(ćelija,PRONAĐI(” “,ćelija,1)-1)

Da biste ga izvršili, zamijenite stanica s pokazivačem ćelije koji sadrži prvo puno ime koje želite podijeliti. U ovom primjeru želite odabrati B2 i unijeti formulu:

=LIJEVO(A2,PRONAĐI(” “,A2,1)-1)

Međutim, važno je napomenuti da na nekim uređajima ova formula koristi točku i zarez umjesto zareza. Dakle, ako vam gornja formula ne radi, možda ćete umjesto toga morati upotrijebiti sljedeću verziju:

=LIJEVO(ćelija;PRONAĐI(” “;ćelija;1)-1)

U primjeru biste koristili:

=LIJEVO(A2;PRONAĐI(” “;A2;1)-1)

Sada možete jednostavno povući ručicu za popunjavanje prema dolje do kraja stupca Ime.

Funkcija LIJEVO omogućuje vam da odvojite niz, počevši od lijevog kraja teksta. Dio FIND ove formule locira prvi razmak u punom imenu, tako da dobivate dio vašeg punog imena koji dolazi ispred praznog prostora.

Dakle, imena s crticom ostaju zajedno, kao i imena koja sadrže posebne znakove. No vaš stupac Punog imena neće sadržavati srednja imena ili srednje inicijale.

Zarez ili točka-zarez?

Zašto formula nije ista za sve?

Za mnoge korisnike programa Excel, funkcije programa Excel koriste zareze za odvajanje ulaznih podataka. Ali na nekim uređajima regionalne su postavke drugačije.

Da biste otkrili koji simbol koristi vaš Excel, jednostavno počnite upisivati formulu. Kada počnete unesite =LIJEVO(, vidjet ćete lebdeći tekst koji će predložiti pravo oblikovanje.

Razdvajanje prezimena

Poduzmite isti pristup razdvajanju prezimena. Ovaj put trebate koristiti PRAVU formulu, koja odvaja nizove počevši s desne strane.

Formula koja vam je potrebna je:

=DESNO(ćelija, LEN(ćelija) – PRETRAŽIVANJE(“#”, ZAMJENA(ćelija,” “, “#”, LEN(ćelija) – LEN(ZAMJENA(ćelija, ” “, “”)))))

U gornjem primjeru upotrijebili biste sljedeću formulu u ćeliji C2:

=DESNO(A2, LEN(A2) – TRAŽI(“#”, ZAMJENA(A2,” “, “#”, LEN(A2) – LEN(ZAMJENA(A2, ” “, “”)))))

Još jednom, možda ćete se morati prebaciti sa zareza na točku i zarez, što znači da ćete možda morati upotrijebiti:

=DESNO(A2; LEN(A2) – TRAŽI(“#”; ZAMJENA(A2;” “; “#”; LEN(A2) – LEN(ZAMJENA(A2; ” “; “”)))))

Prezimena s crticom i prezimena s posebnim znakovima ostaju netaknuta.

Zašto je ova formula složenija od one za imena? Teže je odvojiti srednja imena i srednje inicijale od prezimena.

Ako želite da srednja imena i inicijali budu navedena uz prezimena, možete koristiti formulu:

=DESNO(ćelija, LEN(ćelija) – PRETRAŽIVANJE(” “, ćelija))

ili:

=DESNO(A2, LEN(A2) – TRAŽI(” “, A2))

ili:

=DESNO(A2; LEN(A2) – TRAŽI(” “; A2))

Ali što ako želite odvojiti srednja imena? Ovo je manje uobičajeno, ali može biti korisno znati.

Odvajanje srednjih imena

Formula za srednja imena je sljedeća:

=MID(ćelija, PRETRAŽIVANJE(” “, ćelija) + 1, PRETRAŽIVANJE(” “, ćelija, PRETRAŽIVANJE(” “, ćelija)+1) – PRETRAŽIVANJE(” “, ćelija)-1)

U gornjem primjeru dobivate:

=SREDINA(A2, PRETRAGA(” “, A2) + 1, PRETRAGA(” “, A2, PRETRAGA(” “, A2)+1) – PRETRAGA(” “, A2)-1)

Ako vaš Excel koristi točku i zarez, formula je:

=SREDINA(A2; PRETRAGA(” “; A2) + 1; PRETRAGA(” “; A2; PRETRAGA(” “; A2)+1) – PRETRAGA(” “; A2)-1)

Nakon unosa formule, povucite ručicu za punjenje prema dolje. Ovdje je stupac srednjeg imena koji je dodan u gornji primjer:

Ako puno ime ne sadrži srednje ime ili inicijale, vaše get nulte vrijednosti u ovom stupcu, koji se mogu prikazati kao #VRIJEDNOST!. Da biste dobili prazne ćelije umjesto #VRIJEDNOST!, možete koristiti funkciju IFERROR.

Tada vaša formula postaje:

=IFGREŠKA(SREDINA(ćelija, PRETRAŽIVANJE(” “, ćelija) + 1, PRETRAŽIVANJE(” “, ćelija, PRETRAŽIVANJE(” “, ćelija)+1) – PRETRAŽIVANJE(” “, ćelija)-1),0)

ili:

=IFERROR(MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1),0)

ili:

=IFERROR(MID(A2; SEARCH(” “; A2) + 1; SEARCH(” “; A2; SEARCH(” “; A2)+1) – SEARCH(” “; A2)-1);0)

Jedan pristup razdvajanju više srednjih imena

Što se događa ako netko na vašem popisu ima više srednjih imena? Koristeći gornju formulu, dohvatit će se samo njihovo srednje ime.

Da biste riješili ovaj problem, možete isprobati drugačiji pristup razdvajanju srednjih imena. Ako ste izradili stupce za ime i prezime, možete ih jednostavno odrezati. Sve što ostane računat će se kao srednje ime.

Ova formula je:

=TRIM(MID(ćelija1,LEN(ćelija2)+1,LEN(ćelija1)-LEN(ćelija2&ćelija3)))

Ovdje se ćelija1 odnosi na pokazivač ćelije u stupcu Puno ime, ćelija2 se odnosi na pokazivač ćelije u stupcu Ime, dok se ćelija3 odnosi na pokazivač ćelije u stupcu Prezime. U gornjem primjeru dobivamo:

=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2))))

ili:

=TRIM(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2))))

Ako koristite ovu formulu, nećete morati brinuti o nultim vrijednostima.

Brzi sažetak

Evo formula koje možete koristiti za dijeljenje punih imena na dijelove:

Imena: =LIJEVO(ćelija,PRONAĐI(” “,ćelija,1)-1)

Prezimena: =DESNO(ćelija, LEN(ćelija) – PRETRAŽIVANJE(“#”, ZAMJENA(ćelija,” “, “#”, LEN(ćelija) – LEN(ZAMJENA(ćelija, ” “, “”)))))

Srednja imena: =IFGREŠKA(SREDINA(ćelija, PRETRAŽIVANJE(” “, ćelija) + 1, PRETRAŽIVANJE(” “, ćelija, PRETRAŽIVANJE(” “, ćelija)+1) – PRETRAŽIVANJE(” “, ćelija)-1),0)

Alternativna formula za srednja imena: =TRIM(MID(ćelija1,LEN(ćelija2)+1,LEN(ćelija1)-LEN(ćelija2&ćelija3)))

Odvajanje imena i prezimena bez upotrebe formula

Ako ne želite upisivati hrpu formula koje bi mogle biti pogrešno unesene, tada iskoristite Excelov ugrađeni čarobnjak za pretvaranje teksta u stupce.

Provjerite je li Podaci kartica je odabrana s izbornika na vrhu i označite stupac koji želite pretvoriti.
Zatim kliknite na Tekst u stupcima.
Dalje, uvjerite se Razgraničeno je odabran i kliknite Sljedeći.
Sada odaberite Prostor iz opcija i kliknite Sljedeći.
Zatim promijenite Odredište na "2 B$” i kliknite Završi.Krajnji rezultat bi trebao izgledati ovako.

Završna riječ

Postoji mnogo drugih načina za rješavanje ovog problema u Excelu. Ako nijedna od dostupnih opcija ne čini ono što trebate, provedite još istraživanja.

Korištenje formula je relativno jednostavno i ne ovisi o verziji Excela koju koristite. No, nažalost, još uvijek možete naići na pogreške.

Na primjer, ako puno ime osobe počinje s prezimenom, podijelit će se na pogrešan način. Formule će također imati problema s prezimenima koja sadrže prefikse ili sufikse, kao što su le Carré ili van Gogh. Ako nečije ime završava na Jr., to će biti navedeno kao njegovo prezime.

Međutim, postoje izmjene koje možete dodati kako biste riješili te probleme kako se pojave. Rad s formulama daje vam fleksibilnost koja vam je potrebna za rješavanje ovih složenosti.