Excel má sadu TEXTOVÝCH funkcí, které dokážou zázraky. Pomocí těchto funkcí můžete provádět všechny druhy operací s řezy a kostkami textu.
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).
V Excelu bohužel neexistuje funkce podřetězců, která by to dokázala snadno. To však lze stále provést pomocí textových vzorců a některých dalších integrovaných funkcí aplikace Excel.
Pojďme se nejprve podívat na některé textové funkce, které v tomto tutoriálu použijeme.
Excel TEXT funkce
Excel má řadu textových funkcí, díky nimž je opravdu snadné extrahovat podřetězec z původního textu v aplikaci Excel. Zde jsou funkce aplikace Excel Text, které použijeme v tomto kurzu:
- PRAVÁ funkce: Extrahuje zadaný počet znaků z pravé části textového řetězce.
- Funkce LEFT: Extrahuje zadaný počet znaků z levé části textového řetězce.
- Funkce MID: Extrahuje zadaný počet znaků ze zadané počáteční pozice v textovém řetězci.
- Funkce FIND: Najde počáteční pozici zadaného textu v textovém řetězci.
- Funkce LEN: Vrátí počet znaků v textovém řetězci.
Extrahujte podřetězec v aplikaci Excel pomocí funkcí
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Toto jsou některá náhodná (ale superhrdinská) e-mailová ID (kromě mého) a v níže uvedených příkladech vám ukážu, jak extrahovat uživatelské jméno a název domény pomocí textových funkcí v Excelu.
Příklad 1 - Extrahování uživatelských jmen z ID e -mailu
Při používání funkcí textu je důležité identifikovat vzor (pokud existuje). Díky tomu je sestavení vzorce opravdu snadné. 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 referenci k získání uživatelských jmen.
Zde je vzorec pro získání uživatelského jména:
= VLEVO (A2, NAJÍT ("@", A2) -1)
Výše uvedený vzorec používá funkci VLEVO k extrahování uživatelského jména identifikací polohy znaku @ v id. To se provádí pomocí funkce NAJÍT, která vrací pozici @.
Například v případě [email protected] vrátí funkce FIND („@“, A2) hodnotu 11, což je její pozice v textovém řetězci.
Nyní pomocí funkce DOLEVA extrahujeme 10 znaků z levé části řetězce (o jeden méně, než je hodnota vrácená funkcí LEFT).
Příklad 2 - Extrahování názvu domény z ID e -mailu
Stejnou logiku použitou ve výše uvedeném příkladu lze použít k získání názvu domény. Menším rozdílem je, že musíme extrahovat znaky z pravé části textového řetězce.
Zde je vzorec, který to provede:
= RIGHT (A2, LEN (A2) -FIND ("@", A2))
Ve výše uvedeném vzorci používáme stejnou logiku, ale upravíme ji, abychom se ujistili, že dostáváme správný řetězec.
Vezměme si znovu příklad [email protected]. Funkce FIND vrací pozici znaku @, což je v tomto případě 11. Nyní musíme extrahovat všechny znaky za znak @. Identifikujeme tedy celkovou délku řetězce a odečteme počet znaků až do @. Udává počet znaků, které pokrývají název domény napravo.
Nyní můžeme jednoduše použít PRAVOU funkci k získání názvu domény.
Příklad 3 - Extrahování názvu domény z ID e -mailu (bez .com)
Chcete -li extrahovat podřetězec ze středu textového řetězce, musíte identifikovat polohu značky těsně před a za podřetězcem.
Například v níže uvedeném příkladu pro získání názvu domény bez části .com bude značka @ (což je přímo před názvem domény) a. (což je hned za tím).
Zde je vzorec, který extrahuje pouze název domény:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Funkce Excel MID extrahuje zadaný počet znaků ze zadané počáteční pozice. V tomto příkladu výše FIND („@“, A2) +1 určuje počáteční pozici (která je hned za@) a FIND („.“, A2) -FIND („@“, A2) -1 identifikuje počet znaků mezi@‘A‘.‘
Aktualizace: Jeden ze čtenářů William19 uvedl, že výše uvedený vzorec by nefungoval v případě, že je v ID e -mailu tečka (.) (Například [email protected]). Zde je tedy vzorec pro řešení takových případů:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Pomocí textu na sloupce extrahujte podřetězec v aplikaci Excel
Použití funkcí k extrahování podřetězce v aplikaci Excel má tu výhodu, že je dynamické. Pokud změníte původní text, vzorec by výsledky automaticky aktualizoval.
Pokud je to něco, co možná nepotřebujete, pak by použití funkce Text do sloupců mohl být rychlý a snadný způsob rozdělení textu na podřetězce na základě zadaných značek.
Postupujte takto:
- Vyberte buňky, kde máte text.
- Přejděte na Data -> Datové nástroje -> Text do sloupců.
- V Průvodci textem do sloupce, krok 1, vyberte Oddělovač a stiskněte Další.
- V kroku 2 zaškrtněte možnost Další a do pole vpravo zadejte @. Toto bude náš oddělovač, který by Excel použil k rozdělení textu na podřetězce. Náhled dat si můžete prohlédnout níže. Klikněte na Další.
- V kroku 3 v tomto případě funguje obecné nastavení. Pokud však rozdělujete čísla/data, můžete zvolit jiný formát. Ve výchozím nastavení je cílová buňka tam, kde máte původní data. Pokud chcete zachovat původní data nedotčená, změňte to na jinou buňku.
- Klikněte na Dokončit.
To vám okamžitě poskytne dvě sady podřetězců pro každé e -mailové ID použité v tomto příkladu.
Pokud chcete text dále rozdělit (například rozdělit batman.com na batman a com), opakujte s ním stejný postup.
Pomocí funkce NAJÍT a VYMĚNIT extrahujte text z buňky v aplikaci Excel
NAJÍT a VYMĚNIT může být výkonnou technikou při práci s textem v Excelu. V níže uvedených příkladech se naučíte, jak pomocí funkce NAJÍT a VYMĚNIT se zástupnými znaky dělat úžasné věci v Excelu.
Viz také: Zjistěte vše o zástupných znacích v Excelu.
Vezměme si stejné příklady ID e -mailů.
Příklad 1 - Extrahování uživatelských jmen z ID e -mailu
Zde jsou kroky k extrahování uživatelských jmen z ID e -mailů pomocí funkce Najít a nahradit:
- Zkopírujte a vložte původní data. Protože funkce Najít a nahradit funguje a mění data, na která je použita, je nejlepší mít zálohu původních dat.
- Vyberte data a přejděte na Domů -> Úpravy -> Najít a vybrat -> Nahradit (nebo použijte klávesovou zkratku Ctrl + H).
- V dialogovém okně Najít a nahradit zadejte následující:
- Najít co: @*
- Nahradit: (ponechte toto prázdné)
- Klikněte na Nahradit vše.
To okamžitě odstraní veškerý text před @ v e -mailových ID. Výsledek získáte takto:
Jak to funguje ?? - Ve výše uvedeném příkladu jsme použili kombinaci @ a *. Hvězdička (*) je zástupný znak, který představuje libovolný počet znaků. @* By tedy znamenalo textový řetězec, který začíná na @ a může za ním mít libovolný počet znaků. Například na adrese [email protected] bude @* @batman.com. Když nahradíme @* prázdným, odstraní všechny znaky za @(včetně @).
Příklad 2 - Extrahování názvu domény z ID e -mailu
Pomocí stejné logiky můžete upravit kritéria „Najít co“ a získat tak název domény.
Zde jsou kroky:
- Vyberte data.
- Přejděte na Domů -> Úpravy -> Najít a vybrat -> Nahradit (nebo použijte klávesovou zkratku Ctrl + H).
- V dialogovém okně Najít a nahradit zadejte následující:
- Najít co: *@
- Nahradit: (nechte toto prázdné)
- Klikněte na Nahradit vše.
To okamžitě odstraní veškerý text před @ v e -mailových ID. Výsledek získáte takto:
