Excel ima skup TEXT funkcija koje mogu učiniti čuda. Pomoću ovih funkcija možete izvoditi sve vrste tekstualnih operacija.
Jedan od uobičajenih zadataka za ljude koji rade s tekstualnim podacima je izdvajanje podniz u Excelu (tj. Dobivanje psrt teksta iz ćelije).
Nažalost, u Excelu nema funkcije podniz koji to može učiniti jednostavno. Međutim, to se još uvijek može učiniti pomoću tekstualnih formula, kao i nekih drugih ugrađenih značajki programa Excel.
Pogledajmo prvo neke tekstualne funkcije koje ćemo koristiti u ovom vodiču.
Excel TEKST Funkcije
Excel ima niz tekstualnih funkcija koje bi doista olakšale izdvajanje podniz iz izvornog teksta u Excelu. Evo funkcija programa Excel Text koje ćemo koristiti u ovom vodiču:
- Funkcija RIGHT: Izdvaja navedene brojeve znakova s desne strane tekstualnog niza.
- LEFT funkcija: Izdvaja navedene brojeve znakova s lijeve strane tekstualnog niza.
- MID funkcija: Izdvaja navedene brojeve znakova iz navedene početne pozicije u tekstualnom nizu.
- FIND funkcija: Pronalazi početnu poziciju navedenog teksta u tekstualnom nizu.
- LEN funkcija: Vraća broj znakova u tekstualnom nizu.
Izdvojite podniz u Excelu pomoću funkcija
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:
Ovo su neki nasumični (ali superherojski) ID-ovi e-pošte (osim mog), a u donjim primjerima pokazat ću vam kako izvući korisničko ime i naziv domene pomoću funkcija Text u Excelu.
Primjer 1 - Izdvajanje korisničkih imena iz ID -ova e -pošte
Tijekom korištenja tekstualnih funkcija važno je identificirati uzorak (ako postoji). To čini stvarno lakim konstruiranje formule. U gornjem slučaju uzorak je znak @ između korisničkog imena i naziva domene, a mi ćemo ga koristiti kao referencu za dobivanje korisničkih imena.
Evo formule za dobivanje korisničkog imena:
= LIJEVO (A2, FIND ("@", A2) -1)
Gornja formula koristi funkciju LEFT za izdvajanje korisničkog imena identificiranjem položaja znaka @ u ID -u. To se radi pomoću funkcije FIND koja vraća položaj znaka @.
Na primjer, u slučaju [email protected], FIND (“@”, A2) bi vratio 11, što je njegov položaj u tekstualnom nizu.
Sada koristimo funkciju LEFT za izdvajanje 10 znakova s lijeve strane niza (jedan manje od vrijednosti koju vraća funkcija LEFT).
Primjer 2 - Izdvajanje naziva domene iz ID -ova e -pošte
Ista logika korištena u gornjem primjeru može se koristiti za dobivanje naziva domene. Manja razlika ovdje je što moramo izdvojiti znakove s desne strane tekstualnog niza.
Evo formule koja će to učiniti:
= DESNO (A2, LEN (A2) -FIND ("@", A2))
U gornjoj formuli koristimo istu logiku, ali je prilagodimo kako bismo bili sigurni da dobivamo ispravan niz.
Uzmimo opet primjer [email protected]. Funkcija FIND vraća položaj znaka @, što je u ovom slučaju 11. Sada moramo izdvojiti sve znakove iza @. Dakle, identificiramo ukupnu duljinu niza i oduzimamo broj znakova do @. Daje nam broj znakova koji pokrivaju naziv domene s desne strane.
Sada možemo jednostavno koristiti funkciju DESNO za dobivanje naziva domene.
Primjer 3 - Izdvajanje naziva domene iz ID -ova e -pošte (bez .com)
Da biste izdvojili podniz iz sredine tekstualnog niza, morate identificirati položaj markera neposredno prije i poslije podniza.
Na primjer, u donjem primjeru, da biste dobili naziv domene bez .com dijela, marker bi bio @ (što je neposredno ispred naziva domene) i. (što je odmah nakon njega).
Evo formule koja će izdvojiti samo naziv domene:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Excel MID funkcija izvlači navedeni broj znakova iz navedene početne pozicije. U ovom primjeru gore, FIND (“@”, A2) +1 navodi početnu poziciju (koja je odmah iza@), a FIND (“.”, A2) -FIND (“@”, A2) -1 identificira broj znakova između '@'I'.‘
Ažuriraj: Jedan od čitatelja William19 spomenuo je da gornja formula ne bi funkcionirala u slučaju da postoji točka (.) U ID -u e -pošte (na primjer, [email protected]). Evo formule za rješavanje takvih slučajeva:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Korištenje teksta u stupce za izdvajanje podniz u Excelu
Korištenje funkcija za izdvajanje podniz u Excelu ima prednost što je dinamično. Ako promijenite izvorni tekst, formula će automatski ažurirati rezultate.
Ako vam ovo možda ne treba, upotreba značajke Tekst u stupce mogla bi biti brz i jednostavan način za podjelu teksta u podnizove na temelju navedenih oznaka.
Evo kako to učiniti:
- Odaberite ćelije u kojima imate tekst.
- Idite na Podaci -> Alati za podatke -> Tekst u stupce.
- U 1. čarobnjaku za tekst u stupac odaberite Razgraničeno i pritisnite Dalje.
- U 2. koraku provjerite opciju Ostalo i unesite @ u okvir desno od nje. Ovo će biti naš razdjelnik koji će Excel koristiti za podjelu teksta na podniz. Pregled podataka možete vidjeti u nastavku. Pritisnite Dalje.
- U koraku 3, Opće postavke u ovom slučaju dobro funkcioniraju. Međutim, možete podijeliti format ako dijelite brojeve/datume. Prema zadanim postavkama, odredišna ćelija je mjesto gdje imate izvorne podatke. Ako želite zadržati izvorne podatke netaknutima, promijenite ovo u neku drugu ćeliju.
- Kliknite na Finish.
Ovo će vam odmah dati dva skupa podnizova za svaki id e -pošte koji se koristi u ovom primjeru.
Ako želite dodatno podijeliti tekst (na primjer, podijelite batman.com na batman i com), ponovite isti postupak s njim.
Korištenje FIND i REPLACE za izdvajanje teksta iz ćelije u Excelu
FIND i ZAMJENA mogu biti moćna tehnika kada radite s tekstom u Excelu. U donjim primjerima naučit ćete kako koristiti FIND i ZAMJENU s zamjenskim znakovima za izvršavanje nevjerojatnih stvari u Excelu.
Vidi također: Saznajte sve o zamjenskim znakovima u Excelu.
Uzmimo iste primjere ID -ova e -pošte.
Primjer 1 - Izdvajanje korisničkih imena iz ID -ova e -pošte
Evo koraka za izdvajanje korisničkih imena iz ID -ova e -pošte pomoću funkcije Pronađi i zamijeni:
- Kopirajte i zalijepite izvorne podatke. Budući da Find and Replace funkcionira i mijenja podatke na koje se primjenjuje, najbolje je imati sigurnosnu kopiju izvornih podataka.
- Odaberite podatke i idite na Početna -> Uređivanje -> Pronađi i odaberi -> Zamijeni (ili upotrijebite tipkovnički prečac Ctrl + H).
- U dijaloški okvir Pronađi i zamijeni unesite sljedeće:
- Naći što: @*
- Zamijeni sa: (ovo polje ostavite prazno)
- Pritisnite Zamijeni sve.
Time ćete odmah ukloniti sav tekst ispred @ u ID -ovima e -pošte. Dobit ćete rezultat kao što je prikazano u nastavku:
Kako ovo radi ?? - U gornjem primjeru koristili smo kombinaciju @ i *. Zvjezdica (*) je zamjenski znak koji predstavlja bilo koji broj znakova. Dakle, @* bi značilo, tekstualni niz koji počinje sa @ i može imati bilo koji broj znakova iza sebe. Na primjer u [email protected], @* bi bilo @batman.com. Kada zamijenimo @* praznim, on uklanja sve znakove iza @(uključujući @).
Primjer 2 - Izdvajanje naziva domene iz ID -ova e -pošte
Koristeći istu logiku, možete izmijeniti kriterije "Pronađi što" kako biste dobili naziv domene.
Evo koraka:
- Odaberite podatke.
- Idite na Početna -> Uređivanje -> Pronađi i odaberi -> Zamijeni (ili upotrijebite tipkovnički prečac Ctrl + H).
- U dijaloški okvir Pronađi i zamijeni unesite sljedeće:
- Naći što: *@
- Zamijeni sa: (ovo polje ostavite prazno)
- Pritisnite Zamijeni sve.
Time ćete odmah ukloniti sav tekst ispred @ u ID -ovima e -pošte. Dobit ćete rezultat kao što je prikazano u nastavku:
