Pri práci s textovými údajmi v programe Excel môže byť potrebné odstrániť text pred alebo za konkrétnym znakom alebo textovým reťazcom.
Ak napríklad máte meno a údaje o označení ľudí, možno budete chcieť odstrániť označenie za čiarkou a ponechať iba meno (alebo naopak, ak ponecháte označenie a odstránite meno).
Niekedy sa to dá urobiť jednoduchým vzorcom alebo rýchlym vyhľadaním a nahradením a niekedy to vyžaduje zložitejšie vzorce alebo alternatívne riešenia.
V tomto tutoriále vám ukážem, ako odstrániť text pred alebo za konkrétnym znakom v programe Excel (pomocou rôznych príkladov).
Začnime teda niekoľkými jednoduchými príkladmi.
Odstráňte text za znakom pomocou funkcie Nájsť a nahradiť
Ak chcete rýchlo odstrániť všetok text za konkrétnym textovým reťazcom (alebo pred textovým reťazcom), môžete to urobiť pomocou príkazov Hľadať a nahradiť a zástupných znakov.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete odstrániť označenie za čiarkou a ponechať text pred čiarkou.
Nasledujú kroky, ako to urobiť:
- Skopírujte a prilepte údaje zo stĺpca A do stĺpca B (tým sa zachovajú aj pôvodné údaje)
- Keď sú vybraté bunky v stĺpci B, kliknite na kartu Domov
- V skupine Úpravy kliknite na možnosť Nájsť a vybrať
- V možnostiach, ktoré sa zobrazia v rozbaľovacom zozname, kliknite na možnosť Nahradiť. Otvorí sa dialógové okno Hľadať a nahradiť
- Do poľa „Čo nájsť“ zadajte ,* (tj. čiarka, za ktorou nasleduje znak hviezdičky)
- Nechajte pole „Nahradiť“ prázdne
- Kliknite na tlačidlo Nahradiť všetko
Vyššie uvedené kroky nájdu v množine údajov čiarku a odstránia všetok text za čiarkou (vrátane čiarky).
Pretože sa tým nahradí text z vybraných buniek, je vhodné skopírovať text do iného stĺpca a potom vykonať túto operáciu hľadania a nahradenia alebo vytvoriť záložnú kópiu údajov, aby boli pôvodné údaje neporušené.
Ako to funguje?
* (znak hviezdičky) je zástupný znak, ktorý môže predstavovať ľubovoľný počet znakov.
Keď ho použijem za čiarkou (v poli „Čo nájsť“) a potom kliknem na tlačidlo Nahradiť všetko, nájde prvú čiarku v bunke a považuje ju za zhodu.
Dôvodom je, že znak hviezdičky (*) sa považuje za zhodu s celým textovým reťazcom, ktorý nasleduje za čiarkou.
Keď teda stlačíte tlačidlo nahradiť všetko, nahradí sa čiarkou a všetkým nasledujúcim textom.
Poznámka: Táto metóda funguje dobre, potom máte v každej bunke iba jednu čiarku (ako v našom prípade). V prípade, že máte viac čiarok, táto metóda vždy nájde prvú čiarku a potom odstráni všetko za ňou. Túto metódu teda nemôžete použiť, ak chcete nahradiť celý text za druhou čiarkou a ponechať prvý taký, aký je.V prípade, že chcete odstrániť všetky znaky pred čiarkou, zmeňte hľadaný záznam v poli tak, že pred čiarkou uvediete znak hviezdičky (*, namiesto,*)
Odstráňte text pomocou vzorcov
Ak potrebujete väčšiu kontrolu nad tým, ako nájsť a nahradiť text pred alebo za konkrétnym znakom, je lepšie použiť vstavané textové vzorce v programe Excel.
Predpokladajme, že máte nižšie uvedenú množinu údajov, z ktorej chcete odstrániť celý text za čiarkou.
Nasleduje vzorec, ako to urobiť:
= LEFT (A2, FIND (",", A2) -1)
Vyššie uvedený vzorec používa funkciu FIND na nájdenie polohy čiarky v bunke.
Toto číslo pozície potom použije funkcia DOLEVA na extrahovanie všetkých znakov pred čiarkou. Pretože nechcem ako súčasť výsledku čiarku, odpočítal som 1 od výslednej hodnoty vzorca Hľadať.
Toto bol jednoduchý scenár.
Zoberme si trochu komplexný.
Predpokladajme, že mám nižšie uvedenú množinu údajov, z ktorej chcem odstrániť všetok text za druhou čiarkou.
Tu je vzorec, ktorý to urobí:
= LEFT (A2, FIND ("!", NÁHRADA (A2, ",", "!", 2))-1)
Pretože tento súbor údajov obsahuje viac čiarok, nemôžem použiť funkciu NÁJSŤ na získanie polohy prvej čiarky a extrahovanie všetkého naľavo od nej.
Potrebujem nejako zistiť polohu druhej čiarky a potom extrahovať všetko, čo je naľavo od druhej čiarky.
Na tento účel som pomocou funkcie SUBSTITUTE zmenil druhú čiarku na výkričník. Teraz mi to dáva v cele jedinečný charakter. Teraz môžem pomocou polohy výkričníka extrahovať všetko naľavo od druhej čiarky.
Táto poloha výkričníka sa používa vo funkcii LEFT na extrahovanie všetkého pred druhou čiarkou.
Zatiaľ je všetko dobré!
Ale čo keď je v súbore údajov nekonzistentný počet čiarok.
Napríklad v nižšie uvedenom súbore údajov majú niektoré bunky dve čiarky a niektoré bunky majú tri čiarky a ja musím pred poslednou čiarkou extrahovať celý text.
V tomto prípade musím nejako zistiť polohu posledného výskytu čiarky a potom extrahovať všetko naľavo od nej.
Nasleduje vzorec, ktorý to urobí
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Vyššie uvedený vzorec používa funkciu LEN na nájdenie celkovej dĺžky textu v bunke a tiež dĺžky textu bez čiarky.
Keď odčítam tieto dve hodnoty, zobrazí sa mi celkový počet čiarok v bunke.
Takže by mi to dalo 3 pre bunku A2 a 2 pre bunku A4.
Táto hodnota sa potom použije vo vzorci NÁHRADA na nahradenie poslednej čiarky výkričníkom. A potom môžete použiť ľavú funkciu na extrakciu všetkého, čo je naľavo od výkričníka (kde bývala posledná čiarka)
Ako vidíte na príkladoch, kombinácia textových vzorcov vám umožňuje zvládnuť mnoho rôznych situácií.
Pretože je výsledok prepojený s pôvodnými údajmi, pri zmene pôvodných údajov sa výsledok automaticky aktualizuje.
Odstráňte text pomocou výplne Flash
Flash Fill je nástroj, ktorý bol predstavený v programe Excel 2013 a je k dispozícii vo všetkých ďalších verziách.
Funguje to tak, že identifikujete vzory pri manuálnom zadaní údajov a následnom extrapolácii, aby ste získali údaje pre celý stĺpec.
Ak teda chcete odstrániť text pred alebo za konkrétnym znakom, stačí ukázať aplikácii Flash Fairy, ako by výsledok vyzeral (zadaním niekoľkokrát ručne), a funkcia flash fill automaticky porozumie vzoru a poskytne vám výsledky.
Ukážem vám to na príklade.
Nasleduje súbor údajov, z ktorého chcem odstrániť všetok text za čiarkou.
Tu sú kroky, ako to urobiť pomocou Flash Fill:
- Do bunky B2, ktorá je priľahlým stĺpcom našich údajov, zadajte ručne „Jeffery Haggins“ (čo je očakávaný výsledok)
- Do bunky B3 zadajte „Tim Scott“ (čo je očakávaný výsledok pre druhú bunku)
- Vyberte rozsah B2: B10
- Kliknite na kartu Domov
- V skupine Úpravy kliknite na rozbaľovaciu možnosť Vyplniť
- Kliknite na položku Flash Fill
Vyššie uvedené kroky vám poskytnú výsledok uvedený nižšie:
Môžete tiež použiť klávesovú skratku Flash Fill Ovládanie + E po výbere buniek v stĺpci výsledkov (v našom prípade stĺpci B)
Flash Fill je úžasný nástroj a vo väčšine prípadov dokáže rozpoznať vzor a poskytnúť vám správny výsledok. ale v niektorých prípadoch nemusí byť schopný správne rozpoznať vzor a môže vám poskytnúť nesprávne výsledky.
Nezabudnite si preto výsledky Flash Fill znova skontrolovať
A rovnako ako sme odstránili všetok text za konkrétnym znakom pomocou funkcie flash fill, môžete použiť rovnaký postup na odstránenie textu pred konkrétnym znakom. jednoducho ukážte flash fill, ako by mal výsledok vyzerať ako môj internet ručne v susednom stĺpci, a urobí to ostatné.
Odstrániť text pomocou VBA (vlastná funkcia)
Celý koncept odstránenia textu pred alebo za konkrétnym znakom závisí od nájdenia polohy tohto znaku.
Ako je uvedené vyššie, nájdenie posledného výskytu tohto znaku dobrého znamená použitie zmesi vzorcov.
Ak je to niečo, čo musíte robiť dosť často, môžete tento proces zjednodušiť vytvorením vlastnej funkcie pomocou jazyka VBA (nazývaného funkcie definované používateľom).
Po vytvorení môžete túto funkciu znova a znova používať. Je tiež oveľa jednoduchšie a jednoduchšie použitie (pretože väčšina ťažkých bremien sa vykonáva pomocou kódu VBA na zadnom konci).
Pod kódom VBA, ktorý môžete použiť na vytvorenie vlastnej funkcie v programe Excel:
Funkcia LastPosition (rCell As Range, rChar As String) „Táto funkcia uvádza poslednú pozíciu zadaného znaku“ Tento kód bol vyvinutý spoločnosťou Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) Pre i = rPožičať 1 krok -1 Ak je stred (rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Ukončiť funkciu Koniec Ak ďalej i Ukončiť funkciu
Kód VBA musíte vložiť do bežného modulu v editore VB alebo do osobného zošita makier. Keď ho tam máte, môžete ho použiť ako akúkoľvek inú bežnú funkciu pracovného hárka v zošite.
Táto funkcia má 2 argumenty:
- Odkaz na bunku, pre ktorú chcete nájsť posledný výskyt konkrétneho znaku alebo textového reťazca
- Znak alebo textový reťazec, ktorého pozíciu musíte nájsť
Predpokladajme, že teraz máte nižšie uvedenú množinu údajov a chcete odstrániť všetok text za poslednou čiarkou a mať iba text pred poslednou čiarkou.
Nasleduje vzorec, ktorý to urobí:
= VLEVO (A2, posledná pozícia (A2, ",")-1)
Vo vyššie uvedenom vzorci som určil nájdenie polohy poslednej čiarky. V prípade, že chcete nájsť pozíciu nejakého iného znaku alebo textového reťazca, mali by ste to použiť ako druhý argument vo funkcii.
Ako vidíte, je to oveľa kratšie a jednoduchšie použitie v porovnaní s dlhým textovým vzorcom, ktorý sme použili v predchádzajúcej časti
Ak vložíte kód VBA do modulu v zošite, budete môcť používať túto vlastnú funkciu iba v tomto konkrétnom zošite. Ak to chcete použiť vo všetkých zošitoch vo vašom systéme, musíte skopírovať a vložiť tento kód do osobného zošita makier.
Toto sú niektoré z e -mailov, ktoré môžete použiť na rýchle odstránenie textu pred alebo za konkrétnym znakom v programe Excel.
Ak je to jednoduchá jednorazová vec, môžete to urobiť pomocou funkcie Nájsť a nahradiť. Čo keď je to trochu zložitejšie, potom musíte použiť kombináciu vstavaných vzorcov programu Excel alebo dokonca vytvoriť svoj vlastný vzorec pomocou jazyka VBA.
Dúfam, že ste našli tento návod užitočný.
