Jak extrahovat podřetězec v aplikaci Excel (pomocí textových vzorců)

Excel má sadu textových funkcí, které mohou dělat zázraky. Pomocí těchto funkcí můžete provádět všechny druhy operací s řezem textu a kostkami.

jedním z běžných úkolů pro lidi pracující s textovými daty je extrahovat podřetězec v Excelu (tj. získat psrt textu z buňky).

bohužel v aplikaci Excel neexistuje žádná funkce podřetězu, která to dokáže snadno. To však lze ještě provést pomocí textových vzorců a některých dalších vestavěných funkcí aplikace Excel.,

Tento tutoriál zahrnuje:

pojďme se nejprve podívat na některé z textových funkcí, které budeme používat v tomto tutoriálu.

Excel textové funkce

Excel má řadu textových funkcí, díky nimž by bylo opravdu snadné extrahovat podřetězec z původního textu v Excelu. Zde jsou textové funkce aplikace Excel, které použijeme v tomto tutoriálu:

správná funkce: extrahuje zadaná čísla znaků z pravé strany textového řetězce.
levá funkce: extrahuje zadaná čísla znaků zleva textového řetězce.,
střední funkce: extrahuje zadaná čísla znaků ze zadané výchozí pozice v textovém řetězci.
najít funkci: najde výchozí pozici zadaného textu v textovém řetězci.
LEN funkce: vrací počet znaků v textovém řetězci.,

Extrahovat Podřetězec v Excelu Pomocí Funkce

Předpokládejme, že máte soubor údajů, jak je uvedeno níže:

Tyto jsou některé náhodné (ale superhrdinu) e-mail id (kromě mě), a v níže uvedených příkladech, Ukážu vám, jak získat uživatelské jméno a název domény pomocí Textové funkce v Excelu.

Příklad 1-extrahování uživatelských jmen z e-mailových ID

při použití textových funkcí je důležité identifikovat vzor (pokud existuje). To dělá to opravdu snadné vytvořit vzorec., Ve výše uvedeném případě je vzorem znak @ mezi uživatelským jménem a názvem domény a použijeme jej jako odkaz na získání uživatelských jmen.

Tady je vzorec pro získání uživatelské jméno:

=LEFT(A2,FIND("@",A2)-1)

výše uvedený vzorec používá funkci VLEVO extrahovat uživatelské jméno určením polohy na znak @ v id. To se provádí pomocí funkce FIND, která vrací polohu @.

například v případě [email protected], FIND ( “ @ “ , A2) vrátí 11, což je jeho pozice v textovém řetězci.,

nyní použijeme levou funkci k extrahování 10 znaků zleva řetězce (o jeden méně než hodnota vrácená levou funkcí).

příklad 2-extrahování názvu domény z e-mailových ID

stejná logika použitá ve výše uvedeném příkladu může být použita k získání názvu domény. Menší rozdíl je v tom, že musíme extrahovat znaky z pravé strany textového řetězce.,

Tady je vzorec, který bude dělat toto:

=RIGHT(A2,LEN(A2)-FIND("@",A2))

Ve výše uvedeném vzorci, můžeme použít stejnou logiku, ale upravit to, aby se ujistil, jsme stále správný řetězec.

vezměme si opět příklad [email protected]. Funkce NAJÍT vrátí pozici znak@, který je 11 v tomto případě. Nyní musíme extrahovat všechny znaky po @. Takže identifikujeme celkovou délku řetězce a odečteme počet znaků do @. To nám dává počet znaků, které pokrývají název domény na pravé straně.,

Nyní můžeme jednoduše použít správnou funkci pro získání názvu domény.

Příklad 3 – Extracting Název Domény z E-mailové Ids (bez .com)

extrahovat podřetězec z poloviny textový řetězec, je třeba identifikovat pozici značky před a po podřetězec.

například v níže uvedeném příkladu, aby se název domény dostal bez části .com, značka by byla @ (což je těsně před názvem domény) a . (což je hned po něm).,

Tady je vzorec, který bude extrahovat doménového jména pouze:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)

aplikace Excel funkce MID výtažky zadaný počet znaků od zadané počáteční pozice. V tomto příkladu výše, najít ( “ @ “ , A2) + 1 určuje výchozí pozici (která je hned za@), a najít(„.“, A2)-FIND („@ “ , A2)-1 identifikuje počet znaků mezi ‚@‘ a ‚.‘

Update: jeden ze čtenářů William19 zmínil, že výše uvedený vzorec by nefungoval v případě, že je tečka(.) v e-mailu id (například bruce.wayne@batman.,KOMA). Zde je tedy vzorec pro řešení těchto případů:

=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)

pomocí textu do sloupců extrahujte podřetězec v Excelu

pomocí funkcí extrahovat podřetězec v Excelu má tu výhodu, že je dynamický. Pokud změníte původní text, vzorec automaticky aktualizuje výsledky.

Pokud je to něco, co možná nepotřebujete, pak pomocí funkce Text to Columns může být rychlý a snadný způsob, jak rozdělit text na podřetězce na základě zadaných značek.

zde je návod, jak to provést:

Vyberte buňky, ve kterých máte text.,
Data –> Datové Nástroje –> Text do Sloupců.
v Průvodci textem ke sloupci Krok 1 Vyberte oddělený a stiskněte další.
v kroku 2 zaškrtněte druhou možnost a do pole zadejte @ přímo k ní. To bude náš oddělovač, který by Excel použil k rozdělení textu na podřetězy. Můžete vidět náhled dat níže. Klikněte na další.
v kroku 3 funguje Obecné nastavení v tomto případě dobře., Pokud však rozdělujete čísla/data, můžete zvolit jiný formát. Ve výchozím nastavení je cílová buňka, kde máte původní data. Pokud chcete zachovat původní data neporušená, změňte je na jinou buňku.
klikněte na Dokončit.

to vám okamžitě poskytne dvě sady podřetězců pro každé id e-mailu použité v tomto příkladu.

Pokud chcete dále rozdělit text (například, split batman.com batman a com), opakujte stejný postup s tím.,

použití najít a nahradit extrahovat Text z buňky v Excelu

najít a nahradit může být výkonná technika, když pracujete s textem v Excelu. V níže uvedených příkladech se dozvíte, jak používat najít a nahradit zástupnými znaky dělat úžasné věci v Excelu.

See Also: Learn All about Wildcard Characters in Excel.

Vezměme si stejné příklady e-mailových ID.

Příklad 1 – Extrakce Jména z E-mailové Ids

Zde jsou kroky, extrahujte uživatelská jména z E-mail Id pomocí Najít a Nahradit funkce:

Zkopírujte a vložte na původní data., Protože najít a nahradit práce a změnit data,na kterých je použita, je nejlepší mít zálohu původních dat.
Vyberte data, a jít Domů –> Editace –> & Vyberte –> Nahradit (nebo použijte klávesovou zkratku Ctrl + H).,
V Najít a Nahradit dialogové okno, zadejte následující:
- Najít co: @*
- Nahradit s: (nechat prázdné)
Klepněte na Nahradit Vše.

to okamžitě odstraní veškerý text před @ v e-mailových ID. Budete mít výsledek, jak je uvedeno níže:

Příklad 2 – Extracting Název Domény z E-mailové Ids

Použití stejné logiky, můžete upravit Najít co ‚ kritéria získat název domény.,

zde jsou kroky: