Excel má sadu TEXTOVÝCH funkcií, ktoré dokážu zázraky. Pomocou týchto funkcií môžete vykonávať všetky druhy operácií s textovým rezom a kockami.
Jednou z bežných úloh pre ľudí pracujúcich s textovými údajmi je extrahovať podreťazec v programe Excel (t.j. získať psrt textu z bunky).
V programe Excel bohužiaľ neexistuje žiadna funkcia podreťazca, ktorá by to dokázala ľahko. To sa však dá urobiť pomocou textových vzorcov a niektorých ďalších vstavaných funkcií programu Excel.
Najprv sa pozrime na niektoré textové funkcie, ktoré v tomto návode použijeme.
Excel TEXT funkcie
Excel má množstvo textových funkcií, ktoré by skutočne uľahčili extrahovanie podreťazca z pôvodného textu v programe Excel. Tu sú funkcie programu Excel Text, ktoré použijeme v tomto návode:
- PRAVÁ funkcia: Extrahuje zadaný počet znakov z pravej strany textového reťazca.
- Funkcia LEFT: Extrahuje určený počet znakov z ľavej časti textového reťazca.
- Funkcia MID: Extrahuje určený počet znakov zo zadanej počiatočnej polohy v textovom reťazci.
- Funkcia FIND: Nájde počiatočnú pozíciu zadaného textu v textovom reťazci.
- Funkcia LEN: Vráti počet znakov v textovom reťazci.
Extrahujte podreťazec v programe Excel pomocou funkcií
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Toto je niekoľko náhodných (ale superhrdinských) e-mailových ID (okrem tých mojich) a v nižšie uvedených príkladoch vám ukážem, ako extrahovať používateľské meno a názov domény pomocou textových funkcií v programe Excel.
Príklad 1 - Extrahovanie používateľských mien z e -mailových ID
Pri používaní textových funkcií je dôležité identifikovať vzor (ak existuje). To skutočne uľahčuje zostavenie vzorca. Vo vyššie uvedenom prípade je vzor znakom @ medzi používateľským menom a názvom domény a použijeme ho ako referenciu na získanie používateľských mien.
Tu je vzorec na získanie používateľského mena:
= LEFT (A2, FIND ("@", A2) -1)
Vyššie uvedený vzorec používa funkciu VĽAVO na extrahovanie používateľského mena identifikáciou polohy znaku @ v id. Vykonáva sa to pomocou funkcie NÁHĽAD, ktorá vráti pozíciu znaku @.
Napríklad v prípade [email protected] by funkcia FIND („@“, A2) vrátila hodnotu 11, čo je jej pozícia v textovom reťazci.
Teraz použijeme funkciu LEFT na extrahovanie 10 znakov z ľavej časti reťazca (o jeden menej, ako je hodnota vrátená funkciou LEFT).
Príklad 2 - Extrahovanie názvu domény z e -mailových ID
Na získanie názvu domény je možné použiť rovnakú logiku, aká bola použitá vo vyššie uvedenom príklade. Menším rozdielom je, že musíme extrahovať znaky sprava od textového reťazca.
Tu je vzorec, ktorý to urobí:
= VPRAVO (A2, LEN (A2)-NÁJDETE ("@", A2))
Vo vyššie uvedenom vzorci používame rovnakú logiku, ale upravujeme ju, aby sme sa uistili, že dostávame správny reťazec.
Zoberme si znova príklad [email protected]. Funkcia FIND vráti pozíciu znaku @, ktorý je v tomto prípade 11. Teraz musíme extrahovať všetky znaky za znak @. Identifikujeme teda celkovú dĺžku reťazca a odpočítame počet znakov až do znaku @. Udáva počet znakov, ktoré pokrývajú názov domény napravo.
Teraz môžeme jednoducho použiť funkciu DOPRAVA na získanie názvu domény.
Príklad 3 - Extrahovanie názvu domény z e -mailových ID (bez .com)
Ak chcete extrahovať podreťazec zo stredu textového reťazca, musíte identifikovať polohu značky tesne pred a za podreťazcom.
Napríklad v nižšie uvedenom príklade na získanie názvu domény bez časti .com bude značka @ (čo je tesne pred názvom domény) a. (čo je hneď po ňom).
Tu je vzorec, ktorý extrahuje iba názov domény:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Funkcia Excel MID extrahuje určený počet znakov zo zadanej počiatočnej polohy. V tomto prípade vyššie FIND („@“, A2) +1 určuje počiatočnú pozíciu (ktorá je hneď za znakom@) a FIND („.“, A2) -FIND („@“, A2) -1 identifikuje počet znakov medzi@"A".‘
Aktualizácia: Jeden z čitateľov William19 uviedol, že vyššie uvedený vzorec by nefungoval v prípade, že je v ID e -mailu bodka (.) (Napríklad [email protected]). Tu je teda vzorec na riešenie takýchto prípadov:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Použitie textu na stĺpce na extrahovanie podreťazca v programe Excel
Použitie funkcií na extrahovanie podreťazca v programe Excel má tú výhodu, že je dynamický. Ak zmeníte pôvodný text, vzorec by výsledky automaticky aktualizoval.
Ak je to niečo, čo možno nepotrebujete, potom by používanie funkcie Text do stĺpcov mohlo byť rýchlym a jednoduchým spôsobom rozdelenia textu na podreťazce na základe určených značiek.
Postupujte takto:
- Vyberte bunky, v ktorých máte text.
- Prejdite na položku Údaje -> Nástroje údajov -> Text do stĺpcov.
- V kroku 1 sprievodcu textom do stĺpca vyberte položku Oddelené a stlačte tlačidlo Ďalej.
- V kroku 2 začiarknite možnosť Iné a do poľa vpravo zadajte @. Toto bude náš oddeľovač, ktorý by Excel použil na rozdelenie textu na podreťazce. Ukážku údajov si môžete pozrieť nižšie. Kliknite na Ďalej.
- V kroku 3 v tomto prípade funguje všeobecné nastavenie. Ak však rozdeľujete čísla/dátumy, môžete zvoliť iný formát. V predvolenom nastavení je cieľová bunka tam, kde máte pôvodné údaje. Ak chcete zachovať pôvodné údaje nedotknuté, zmeňte to na inú bunku.
- Kliknite na Dokončiť.
To vám okamžite poskytne dve sady podreťazcov pre každé e -mailové ID použité v tomto príklade.
Ak chcete text ďalej rozdeliť (napríklad rozdeliť batman.com na batman a com), zopakujte s ním rovnaký postup.
Použitie funkcie NAJÍT a VYMENIŤ na extrahovanie textu z bunky v programe Excel
FIND and REPLACE môže byť výkonnou technikou pri práci s textom v programe Excel. V nižšie uvedených príkladoch sa naučíte, ako používať funkcie NAJÍŤ a VYMENIŤ so zástupnými znakmi na úžasné veci v Exceli.
Pozri tiež: Získajte informácie o zástupných znakoch v programe Excel.
Zoberme si rovnaké príklady ID e -mailov.
Príklad 1 - Extrahovanie používateľských mien z e -mailových ID
Tu je postup, ako extrahovať používateľské mená z e -mailových ID pomocou funkcie Nájsť a nahradiť:
- Skopírujte a prilepte pôvodné údaje. Pretože funkcia Nájsť a nahradiť funguje a mení údaje, na ktoré je aplikovaná, je najlepšie mať k dispozícii zálohu pôvodných údajov.
- Vyberte údaje a prejdite na položku Domov -> Úpravy -> Nájsť a vybrať -> Nahradiť (alebo použite klávesovú skratku Ctrl + H).
- V dialógovom okne Hľadať a nahradiť zadajte nasledujúce:
- Nájsť čo: @*
- Nahradiť týmto: (nechajte toto prázdne)
- Kliknite na položku Nahradiť všetko.
To okamžite odstráni všetok text pred @ v e -mailových ID. Výsledok získate takto:
Ako to funguje ?? - Vo vyššie uvedenom príklade sme použili kombináciu @ a *. Hviezdička (*) je zástupný znak, ktorý predstavuje ľubovoľný počet znakov. @* By teda znamenal textový reťazec, ktorý začína na @ a môže mať za ním ľubovoľný počet znakov. Napríklad na adrese [email protected] bude @* @batman.com. Keď nahradíme @* prázdnym, odstráni všetky znaky za @(vrátane @).
Príklad 2 - Extrahovanie názvu domény z e -mailových ID
Pomocou rovnakej logiky môžete upraviť kritériá „Hľadať čo“, aby získali názov domény.
Tu sú kroky:
- Vyberte údaje.
- Prejdite na položku Domov -> Úpravy -> Nájsť a vybrať -> Nahradiť (alebo použite klávesovú skratku Ctrl + H).
- V dialógovom okne Hľadať a nahradiť zadajte nasledujúce:
- Nájsť čo: *@
- Nahradiť týmto: (nechajte toto prázdne)
- Kliknite na položku Nahradiť všetko.
To okamžite odstráni všetok text pred @ v e -mailových ID. Výsledok budete mať uvedený nižšie:
