Možete koristiti Excel za razbijanje podataka na manje dijelove. Pronalaženje podataka koji su vam potrebni i upravljanje njima važan je cilj mnogih korisnika Excela.
Ako imate puno ime osobe, možda ćete trebati unijeti samo njezino ime ili prezime. Na primjer, ako svojim klijentima šaljete prijateljsku automatiziranu e-poštu, morate upotrijebiti njihova imena kako biste izbjegli zvučati bezlično. Ako pregledavate popis ispitanika s ankete, možda bi bilo važno samo upotrijebiti njihova prezimena ili sakriti njihova prezimena kako biste zadržali anonimnost.
Excel ovaj postupak čini jednostavnim i postoji mnogo različitih pristupa. Evo vodiča koji će vam pomoći stvoriti zasebne stupce Ime i Prezime koristeći formule. Pokrivamo i pitanje srednjih imena.
Excel formule za razdvajanje imena na dijelove
Gdje započinjete?
Razdvajanje imena
Ovo je generička formula:
= LIJEVO (ćelija, PRONAĐI (, ćelija, 1) -1)
Da biste je izvršili, zamijenite stanica 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 se na nekim uređajima ova formula koristi zarezima i zarezima. Dakle, ako vam gornja formula ne uspije, možda ćete umjesto nje trebati upotrijebiti sljedeću verziju:
= LIJEVO (ćelija; PRONAĐI (; ćelija; 1) -1)
U primjeru biste upotrijebili:
= LIJEVO (A2; PRONAĐI (; A2; 1) -1)
Sada možete jednostavno povući ručicu za punjenje do kraja na stupac Ime.
Funkcija LIJEVO omogućuje odvajanje niza, počevši od lijevog kraja teksta. FIND dio ove formule smješta prvi razmak u punom imenu, tako da ćete dobiti dio svog punog imena koji dolazi ispred praznog prostora.
Stoga vezana imena ostaju zajedno, a isto tako i imena koja sadrže posebne znakove. No, stupac Puno ime neće sadržavati srednja imena ili srednje inicijale.
Zarez ili zarez?
Zašto formula nije ista za sve?
Za mnoge korisnike programa Excel funkcije Excela koriste zareze za odvajanje ulaznih podataka. Ali na nekim su uređajima regionalne postavke različite.
Da biste otkrili koji simbol koristi vaš Excel, jednostavno počnite upisivati formulu. Kad počnete ulaziti = LIJEVO ( , vidjet ćete lebdeći tekst koji će sugerirati ispravno oblikovanje.
Razdvajanje prezimena
Primijenite isti pristup razdvajanju prezimena. Ovaj put trebali biste upotrijebiti DESNU formulu koja razdvaja žice počevši s desne strane.
Formula koja vam treba je:
= DESNO (ćelija, LEN (ćelija) - TRAŽENJE (#, ZAMJENA (ćelija,, #, LEN (ćelija) - LEN (ZAMJENA (ćelija,,)))))
U gornjem primjeru koristili 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 trebati prijeći sa zareza u zarez, što znači da ćete možda trebati koristiti:
= DESNO (A2; LEN (A2) - TRAŽENJE (#; ZAMJENA (A2 ;; #; LEN (A2) - LEN (ZAMJENA (A2 ;;))))))
Prenesena prezimena 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 se srednja imena i inicijali navode s prezimenima, možete upotrijebiti formulu:
= DESNO (ćelija, LEN (ćelija) - TRAŽENJE (, ćelija))
ili:
= DESNO (A2, LEN (A2) - TRAŽENJE (, A2))
ili:
= DESNO (A2; LEN (A2) - TRAŽENJE (; A2))
Ali što ako želite razdvojiti srednja imena? To je rjeđe, ali može biti korisno znati.
Odvajanje srednjih imena
Formula za srednja imena je sljedeća:
možete li kupiti dionice nakon radnog vremena
= MID (ćelija, SEARCH (, ćelija) + 1, SEARCH (, ćelija, SEARCH (, ćelija) +1) - SEARCH (, ćelija) -1)
U gornjem primjeru dobivate:
= SREDNJA (A2, TRAŽI (, A2) + 1, TRAŽI (, A2, TRAŽI (, A2) +1) - TRAŽI (, A2) -1)
Ako vaš Excel koristi točke sa zarezom, formula je:
= SREDNJA (A2; TRAŽENJE (; A2) + 1; TRAŽENJE (; A2; TRAŽENJE (; A2) +1) - TRAŽENJE (; A2) -1)
Nakon unosa formule povucite ručicu za popunjavanje prema dolje. Evo gornjeg stupca koji je dodan u gornji primjer:
Ako puno ime ne sadrži srednje ime ili početno slovo, u ovom stupcu dobit ćete nulte vrijednosti, koje se mogu prikazati kao #Vrijednost !. Da biste dobili prazne ćelije umjesto #Vrijednost!, Možete koristiti funkciju IFERROR.
Tada vaša formula postaje:
= IFERROR (MID (ćelija, SEARCH (, ćelija) + 1, SEARCH (, ćelija, SEARCH (, ćelija) +1) - SEARCH (, ćelija) -1), 0)
ili:
= IFERROR (SREDINA (A2, TRAŽENJE (, A2) + 1, TRAŽENJE (, A2, TRAŽENJE (, A2) +1) - TRAŽENJE (, A2) -1), 0)
ili:
= IFERROR (SREDINA (A2; TRAŽENJE (; A2) + 1; TRAŽENJE (; A2; TRAŽENJE (; A2) +1) - TRAŽENJE (; A2) -1); 0)
Jedan pristup razdvajanju višestrukih srednjih imena
Što se događa ako netko s vašeg popisa ima više srednjih imena? Pomoću gornje formule dobit će se samo njihovo srednje ime.
Da biste riješili ovaj problem, možete isprobati drugačiji pristup razdvajanju srednjih imena. Ako ste stvorili stupce s imenom i prezimenom, možete ih jednostavno odrezati. Sve što je ostalo 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 odnosi se na pokazivač ćelije u stupcu Ime, dok se ćelija3 odnosi na pokazivač ćelije u stupcu Prezime. U gornjem primjeru dobivamo:
= TRIM (SREDINA (A2, LEN (B2) + 1, LEN (A2) -LEN (B2 & D2))))
ili:
= TRIM (MID (A2; LEN (B2) +1; LEN (A2) -LEN (B2 & D2)))
Ako pristanete na ovu formulu, nećete morati brinuti o nultim vrijednostima.
Brzi prikaz
Evo formula koje možete koristiti za razdvajanje punih imena na dijelove:
Imena: = LIJEVO (ćelija, PRONAĐI (, ćelija, 1) -1)
Prezimena: = DESNO (ćelija, LEN (ćelija) - TRAŽENJE (#, ZAMJENA (ćelija,, #, LEN (ćelija) - LEN (ZAMJENA (ćelija,,)))))
Srednja imena: = IFERROR (MID (ćelija, SEARCH (, ćelija) + 1, SEARCH (, ćelija, SEARCH (, ćelija) +1) - SEARCH (, ćelija) -1), 0)
Alternativna formula za srednja imena: = TRIM (MID (ćelija1, LEN (ćelija2) + 1, LEN (ćelija1) -LEN (ćelija2 & ćelija3)))
Razdvajanje imena i prezimena bez korištenja formula
Ako vam se ne da utipkati gomilu formula koje se mogu unijeti pogrešno, iskoristite ugrađeni Excelov čarobnjak za pretvaranje teksta u stupce.
- Obavezno provjerite Podaci kartica odabire se s izbornika na vrhu i označava stupac koji želite pretvoriti.
- Zatim kliknite na Tekst u stupce .
- Dalje, provjerite Razgraničena je odabrano i kliknite Sljedeći
. - Sada odaberite Prostor iz opcija i kliknite Sljedeći .
- Zatim promijenite Odredište do2 B $ 2i kliknite Završi.
Krajnji rezultat trebao bi izgledati ovako.
.
Krajnji rezultat trebao bi izgledati ovako.
Završna riječ
Postoje mnogi drugi načini za rješavanje ovog problema u programu Excel. Ako niti jedna od dostupnih opcija ne učini ono što trebate, istražite još.
Korištenje formula relativno je jednostavno i ne ovisi o verziji Excela koju koristite. No, nažalost, i dalje možete naići na pogreške.
Na primjer, ako puno ime osobe započne s njezinim obiteljskim prezimenom, razdvojit ć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 da biste riješili ove probleme čim se pojave. Rad s formulama daje vam fleksibilnost koja vam je potrebna za rješavanje ovih složenosti.
