Excel ima nabor funkcij BESEDILO, ki lahko delajo čudeže. S temi funkcijami lahko delate vse vrste besedilnih rezin in kock.
Ena od skupnih nalog ljudi, ki delajo z besedilnimi podatki, je izvleči podniz v Excelu (tj. Pridobiti psrt besedila iz celice).
Na žalost v Excelu ni podnizne funkcije, ki bi to zlahka naredila. Vendar je to še vedno mogoče storiti z uporabo besedilnih formul in nekaterih drugih vgrajenih funkcij programa Excel.
Najprej si oglejmo nekatere besedilne funkcije, ki jih bomo uporabljali v tej vadnici.
Excel BESEDILNE funkcije
Excel ima vrsto besedilnih funkcij, s katerimi bi bilo zelo enostavno izvleči podniz iz prvotnega besedila v Excelu. Tu so funkcije besedila Excel, ki jih bomo uporabili v tej vadnici:
- Funkcija RIGHT: izvleče določeno število znakov z desne strani besedilnega niza.
- Funkcija LEFT: izvleče določeno število znakov iz leve strani besedilnega niza.
- MID funkcija: Izvleče določeno število znakov iz podanega začetnega položaja v besedilnem nizu.
- Funkcija FIND: Poišče začetni položaj podanega besedila v besedilnem nizu.
- Funkcija LEN: Vrne število znakov v besedilnem nizu.
Izvlecite podniz v Excelu s funkcijami
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
To je nekaj naključnih (vendar superjunaških) e-poštnih ID-jev (razen mojih), v spodnjih primerih pa vam bom pokazal, kako izvlečete uporabniško ime in ime domene s pomočjo besedilnih funkcij v Excelu.
Primer 1 - Izvlečenje uporabniških imen iz e -poštnih ID -jev
Pri uporabi funkcij Besedilo je pomembno prepoznati vzorec (če obstaja). Tako je zelo enostavno sestaviti formulo. V zgornjem primeru je vzorec znak @ med uporabniškim imenom in imenom domene in ga bomo uporabili kot referenco za pridobivanje uporabniških imen.
Tu je formula za pridobitev uporabniškega imena:
= LEVO (A2, NAJDI ("@", A2) -1)
Zgornja formula uporablja funkcijo LEFT za ekstrahiranje uporabniškega imena z identifikacijo položaja znaka @ v ID -ju. To se naredi s funkcijo FIND, ki vrne položaj @.
Na primer, v primeru [email protected] bi FIND (“@”, A2) vrnil 11, kar je njegov položaj v besedilnem nizu.
Zdaj s funkcijo LEFT izvlečemo 10 znakov iz leve strani niza (enega manj kot vrednost, ki jo vrne funkcija LEFT).
Primer 2 - Izvlečenje imena domene iz e -poštnih ID -jev
Ista logika, uporabljena v zgornjem primeru, se lahko uporabi za pridobitev imena domene. Manjša razlika je v tem, da moramo izvleči znake z desne strani besedilnega niza.
Tukaj je formula, ki bo to naredila:
= DESNO (A2, LEN (A2) -FIND ("@", A2))
V zgornji formuli uporabljamo isto logiko, vendar jo prilagodimo, da zagotovimo, da dobimo pravi niz.
Ponovimo primer [email protected]. Funkcija FIND vrne položaj znaka @, ki je v tem primeru 11. Zdaj moramo izvleči vse znake za @. Tako določimo skupno dolžino niza in odštejemo število znakov do @. Poda nam število znakov, ki pokrivajo ime domene na desni.
Zdaj lahko preprosto uporabimo funkcijo DESNO, da dobimo ime domene.
Primer 3 - Izvlečenje imena domene iz e -poštnih ID -jev (brez .com)
Če želite izvleči podniz iz sredine besedilnega niza, morate določiti položaj označevalnika tik pred in po podnizu.
Na primer, v spodnjem primeru, da bi dobili ime domene brez dela .com, bi bil označevalec @ (kar je tik pred imenom domene) in. (kar je takoj za njim).
Tu je formula, ki bo izvlekla samo ime domene:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Excel MID funkcija izvleče določeno število znakov iz podanega začetnega položaja. V tem zgornjem primeru FIND (“@”, A2) +1 določa začetni položaj (ki je tik za@), in FIND (“.”, A2) -FIND (“@”, A2) -1 označuje število znakov med "@' in '.‘
Nadgradnja: Eden od bralcev William19 je omenil, da zgornja formula ne bi delovala, če je v e -poštnem ID -ju pika (.) (Na primer [email protected]). Torej, tukaj je formula za obravnavo takih primerov:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Uporaba besedila v stolpcih za ekstrakcijo podniz v Excelu
Prednost uporabe funkcij za pridobivanje podniz v Excelu je dinamična. Če spremenite izvirno besedilo, bo formula samodejno posodobila rezultate.
Če tega morda ne potrebujete, je lahko uporaba funkcije Besedilo v stolpce hiter in enostaven način za razdelitev besedila v podniz na podlagi določenih označevalcev.
Evo, kako to storiti:
- Izberite celice, v katerih imate besedilo.
- Pojdite na Podatki -> Podatkovna orodja -> Besedilo v stolpce.
- V 1. čarovniku za besedilo v stolpec izberite Razmejeno in pritisnite Naprej.
- V 2. koraku preverite možnost Drugo in v polje @ vnesite @. To bo naš ločilo, ki bi ga Excel uporabil za razdelitev besedila na podniz. Spodaj si lahko ogledate predogled podatkov. Kliknite Naprej.
- V 3. koraku Splošne nastavitve v tem primeru dobro delujejo. Če pa delite številke/datume, lahko izberete drugo obliko. Privzeto je ciljna celica tam, kjer imate izvirne podatke. Če želite ohraniti izvirne podatke nedotaknjene, to spremenite v drugo celico.
- Kliknite Dokončaj.
To vam bo takoj dalo dva niza podnizov za vsak e -poštni ID, uporabljen v tem primeru.
Če želite besedilo še bolj razdeliti (na primer razdeliti batman.com na batman in com), ponovite isti postopek z njim.
Uporabite FIND in REPLACE za ekstrahiranje besedila iz celice v Excelu
FIND in REPLACE sta lahko učinkovita tehnika, ko delate z besedilom v Excelu. V spodnjih primerih se boste naučili uporabljati FIND in REPLACE z nadomestnimi znaki za neverjetne stvari v Excelu.
Poglej tudi: Več o nadomestnih znakih v Excelu.
Vzemimo iste primere e -poštnih ID -jev.
Primer 1 - Izvlečenje uporabniških imen iz e -poštnih ID -jev
Spodaj so navedeni koraki za ekstrakcijo uporabniških imen iz e -poštnih ID -jev s pomočjo funkcije Najdi in zamenjaj:
- Kopirajte in prilepite izvirne podatke. Ker Find and Replace deluje in spreminja podatke, na katere se nanaša, je najbolje, da imate varnostno kopijo prvotnih podatkov.
- Izberite podatke in pojdite na Domov -> Urejanje -> Poišči in izberite -> Zamenjaj (ali uporabite bližnjico na tipkovnici Ctrl + H).
- V pogovorno okno Najdi in zamenjaj vnesite naslednje:
- Najdi kaj: @*
- Zamenjaj z: (pusti prazno)
- Kliknite Zamenjaj vse.
S tem boste takoj odstranili vse besedilo pred @ v e -poštnih ID -jih. Dobili boste rezultat, kot je prikazano spodaj:
Kako to deluje ?? - V zgornjem primeru smo uporabili kombinacijo @ in *. Zvezdica (*) je nadomestni znak, ki predstavlja poljubno število znakov. Zato bi @* pomenilo besedilni niz, ki se začne z @ in ima lahko za seboj poljubno število znakov. Na primer v [email protected] bi bilo** @batman.com. Ko zamenjamo @* s praznim, odstrani vse znake za @(vključno z @).
Primer 2 - Izvlečenje imena domene iz e -poštnih ID -jev
Z isto logiko lahko spremenite merila »Poišči kaj«, da dobite ime domene.
Tu so koraki:
- Izberite podatke.
- Pojdite na Domov -> Urejanje -> Poišči in izberite -> Zamenjaj (ali uporabite bližnjico na tipkovnici Ctrl + H).
- V pogovorno okno Najdi in zamenjaj vnesite naslednje:
- Najdi kaj: *@
- Zamenjaj z: (pusti prazno)
- Kliknite Zamenjaj vse.
S tem boste takoj odstranili vse besedilo pred @ v e -poštnih ID -jih. Dobili boste rezultat, kot je prikazano spodaj:
