✅ Kako podijeliti ćelije u Excelu (odvojiti u više stupaca)

Sadržaj

Kako podijeliti ćelije u Excelu

Mogu se dogoditi situacije kada morate podijeliti ćelije u Excelu. To bi moglo biti kada podatke dobijete iz baze podataka ili ih kopirate s interneta ili ih nabavite od kolege.

Jednostavan primjer gdje trebate podijeliti ćelije u Excelu je kada imate puna imena i želite ih podijeliti na ime i prezime.

Ili dobijete adresu ’i želite podijeliti adresu tako da možete zasebno analizirati gradove ili PIN kôd.

Kako podijeliti ćelije u Excelu

U ovom ćete vodiču naučiti kako podijeliti ćelije u Excelu pomoću sljedećih tehnika:

Korištenje značajke Tekst u stupac.
Korištenje Excel tekstualnih funkcija.
Korištenje Flash Fill -a (dostupno 2013. i 2016.).

Započnimo!

Podijelite ćelije u Excelu pomoću teksta za stupac

U nastavku imam popis imena nekih od mojih omiljenih izmišljenih likova i želim ih podijeliti u zasebne ćelije .:

Evo koraka za podjelu ovih imena na ime i prezime:

Odaberite ćelije u kojima imate tekst koji želite podijeliti (u ovom slučaju A2: A7).
Kliknite karticu Podaci
U grupi "Data Tools" kliknite "Text to Columns".
U čarobnjaku Pretvorba teksta u stupce:
- Korak 1 od 3 čarobnjaka za tekst u stupce: Provjerite je li odabrano Razgraničeno (to je zadani odabir). To bi vam omogućilo da odvojite ime i prezime na temelju navedenog razdjelnika (u ovom slučaju razmaknica).
- Pritisnite Dalje.
- Korak 2 od 3 Čarobnjak za slanje teksta u stupce: Odaberite Razmak kao graničnik i poništite odabir svega ostalog. Možete vidjeti kako bi vaš rezultat izgledao u odjeljku Pregled podataka u dijaloškom okviru.
- Pritisnite Dalje.
- Korak 3 od 3 Čarobnjak za slanje teksta u stupce: U ovom koraku možete odrediti format podataka i mjesto na kojem želite rezultat. Format podataka zadržat ću kao općenit jer moram podijeliti tekstualne podatke. Zadano odredište je A2 i ako nastavite s ovim, zamijenit će izvorni skup podataka. Ako želite zadržati izvorne podatke netaknutima, odaberite drugu ćeliju kao odredište. U tom slučaju odabire se B2.
- Kliknite na Finish.

Ovo će trenutno podijeliti tekst ćelije u dva različita stupca.

Bilješka:

Značajka Tekst u stupac dijeli sadržaj ćelija na temelju graničnika. Iako ovo dobro funkcionira ako želite odvojiti ime i prezime, u slučaju imena, srednjeg i prezimena podijelit će se na tri dijela.
Rezultat koji dobivate korištenjem značajke Tekst u stupac je statičan. To znači da ćete, ako dođe do promjena u izvornim podacima, morati ponoviti postupak da biste dobili ažurirane rezultate.

Podijelite ćelije u Excelu pomoću tekstualnih funkcija

Excel Text funkcije izvrsne su kada želite izrezati i izrezati tekstualne nizove.

Dok značajka Tekst u stupac daje statički rezultat, rezultat koji dobivate korištenjem funkcija je dinamičan i automatski će se ažurirati kada promijenite izvorne podatke.

Dijeljenje imena koja imaju ime i prezime

Pretpostavimo da imate iste podatke kao što je prikazano u nastavku:

Izdvajanje imena

Da biste dobili ime s ovog popisa, upotrijebite sljedeću formulu:

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

Ova formula bi uočila prvi razmak, a zatim vratila sav tekst prije tog razmaka:

Ova formula koristi funkciju SEARCH za dobivanje pozicije razmaka. U slučaju Brucea Waynea, svemirski lik je na 6. poziciji. Zatim ekstrahira sve znakove lijevo od njega pomoću funkcije LIJEVO.

Izdvajanje prezimena

Slično, da biste dobili prezime, upotrijebite sljedeću formulu:

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

Ova formula koristi funkciju pretraživanja za pronalaženje položaja razmaknice pomoću funkcije SEARCH. Zatim oduzima taj broj od ukupne duljine imena (koju daje funkcija LEN). To daje broj znakova u prezimenu.

Ovo se prezime zatim ekstrahira pomoću funkcije DESNO.

Bilješka: Ove funkcije možda neće dobro funkcionirati ako u nazivima imate vodeći, zadnji ili dvostruki razmak. Kliknite ovdje da biste saznali kako ukloniti početne/zadnje/dvostruke razmake u Excelu.

Dijeljenje imena koja imaju ime, srednje ime i prezime

Mogu se dogoditi slučajevi kada dobijete kombinaciju imena gdje neka imena imaju i srednje ime.

Formula je u takvim slučajevima pomalo složena.

Izdvajanje imena

Da biste dobili ime:

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

Ovo je ista formula koju smo koristili kad nije bilo srednjeg imena. Jednostavno traži prvi znak razmaka i vraća sve znakove prije razmaka.

Izdvajanje srednjeg imena

Da biste dobili srednje ime:

= IFERROR (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")

MID funkcija počinje od prvog znaka razmaka i izdvaja srednje ime pomoću razlike u položaju prvog i drugog razmaka.

U slučaju da nema srednjeg imena, funkcija MID vraća pogrešku. Kako bi se izbjegla pogreška, ona je umotana u funkciju IFERROR.

Izdvajanje prezimena

Da biste dobili prezime, upotrijebite donju formulu:

= IF (LEN (A2) -LEN (ZAMJENA (A2, "", "")) = 1, DESNO (A2, LEN (A2) -TRAGANJE ("", A2)), DESNO (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))

Ova formula provjerava postoji li srednje ime ili ne (brojeći broj znakova razmaka). Ako postoji samo 1 znak za razmak, jednostavno vraća sav tekst desno od razmaka.

Ali ako postoje 2, tada se uočava drugi znak razmaka i vraća broj znakova nakon drugog razmaka.

Napomena: Ova formula dobro funkcionira ako imate imena koja imaju samo ime šake i prezime, ili ime, srednje i prezime. Međutim, ako imate mješavinu u kojoj imate sufikse ili pozdrave, morat ćete dodatno izmijeniti formule.

Podijelite ćelije u Excelu pomoću Flash Fill -a

Flash Fill nova je značajka uvedena u Excel 2013.

Moglo bi biti jako zgodno kada imate uzorak i želite brzo izvući dio njega.

Na primjer, uzmimo podatke o imenu i prezimenu:

Flash fill radi tako što identificira uzorke i replicira ih za sve ostale ćelije.

Evo kako možete izvući ime s popisa pomoću Flash Fill -a:

U ćeliju B2 unesite prvo ime Brucea Waynea (tj. Bruce).
Kad je ćelija odabrana, primijetit ćete mali kvadrat na desnom kraju odabira ćelije. Dvaput kliknite na nju. To će ispuniti isti naziv u svim ćelijama.
Kad se ćelije ispune, u donjem desnom kutu vidjet ćete ikonu Mogućnosti automatskog popunjavanja. Kliknite na nju.
Odaberite Flash Fill s popisa.
Čim odaberete Flash Fill, primijetit ćete da se sve ćelije same ažuriraju i sada prikazuju ime za svako ime.

Kako funkcionira Flash Fill?

Flash Fill traži uzorke u skupu podataka i replicira uzorak.

Flash Fill je iznenađujuće pametna značajka i u većini slučajeva radi očekivano. Ali i to u nekim slučajevima ne uspijeva.

Na primjer, ako imam popis imena koji ima kombinaciju imena od kojih neki imaju srednje ime, a neki nemaju.

Ako u takvom slučaju izvučem srednje ime, Flash Fill će pogrešno vratiti prezime u slučaju da nema imena.

Da budem iskren, to je još uvijek dobra aproksimacija trenda. Međutim, nije ono što sam želio.

No, to je i dalje dovoljno dobro oruđe za držanje u svom arsenalu i korištenje kad god se ukaže potreba.

Evo još jednog primjera gdje Flash Fill radi sjajno.

Imam skup adresa s kojih želim brzo izvući grad.

Da biste brzo dobili grad, unesite naziv grada za prvu adresu (unesite London u ćeliju B2 u ovom primjeru) i upotrijebite automatsko popunjavanje da biste popunili sve ćelije. Sada upotrijebite Flash Fill i odmah će vam dati naziv grada sa svake adrese.

Slično, možete podijeliti adresu i izdvojiti bilo koji dio adrese.

Imajte na umu da bi za to trebala adresa biti homogen skup podataka s istim razdjelnikom (u ovom slučaju zarez).

U slučaju da pokušate upotrijebiti Flash Fill kad nema uzorka, prikazat će vam se pogreška kao što je prikazano u nastavku: