Při práci s textovými daty v aplikaci Excel může být nutné odstranit text před nebo za konkrétním znakem nebo textovým řetězcem.
Pokud například máte jméno a údaje o označení lidí, možná budete chtít odstranit označení za čárkou a ponechat pouze jméno (nebo naopak tam, kde ponecháte označení a odstraníte jméno).
Někdy to lze provést pomocí jednoduchého vzorce nebo rychlého hledání a nahrazení a někdy to vyžaduje složitější vzorce nebo řešení.
V tomto tutoriálu vám ukážu, jak odstranit text před nebo za konkrétním znakem v aplikaci Excel (pomocí různých příkladů).
Začněme tedy několika jednoduchými příklady.
Odebrání textu za znakem pomocí funkce Najít a nahradit
Pokud chcete rychle odebrat veškerý text po konkrétním textovém řetězci (nebo před textovým řetězcem), můžete to provést pomocí funkce Najít a nahradit a zástupných znaků.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete odstranit označení za znakem čárky a ponechat text před čárkou.
Níže jsou uvedeny kroky, jak to provést:
- Zkopírujte a vložte data ze sloupce A do sloupce B (zachová se také původní data)
- S vybranými buňkami ve sloupci B klikněte na kartu Domů
- Ve skupině Úpravy klikněte na možnost Najít a vybrat
- V možnostech, které se zobrazí v rozevíracím seznamu, klikněte na možnost Nahradit. Otevře se dialogové okno Najít a nahradit
- Do pole „Najít co“ zadejte ,* (tj. čárka následovaná znakem hvězdičky)
- Pole „Nahradit“ nechte prázdné
- Klikněte na tlačítko Nahradit vše
Výše uvedené kroky by našly čárku v datové sadě a odstranily by veškerý text za čárkou (včetně čárky).
Protože se tím nahradí text z vybraných buněk, je vhodné zkopírovat text do jiného sloupce a poté provést tuto operaci hledání a nahrazení, nebo vytvořit záložní kopii dat, abyste měli původní data neporušená
Jak to funguje?
* (hvězdička) je zástupný znak, který může představovat libovolný počet znaků.
Když ji použiji za čárkou (v poli „Najít co“) a poté kliknu na tlačítko Nahradit vše, najde první čárku v buňce a považuje ji za shodu.
Důvodem je, že znak hvězdičky (*) je považován za shodu s celým textovým řetězcem, který následuje za čárkou.
Když tedy stisknete tlačítko nahradit vše, nahradí to čárkou a veškerým následujícím textem.
Poznámka: Tato metoda funguje dobře, pak máte v každé buňce pouze jednu čárku (jako v našem příkladu). V případě, že máte více čárek, tato metoda by vždy našla první čárku a poté odstranila vše za ní. Tuto metodu tedy nemůžete použít, pokud chcete nahradit veškerý text za druhou čárkou a ponechat první tak, jak je.V případě, že chcete odstranit všechny znaky před čárkou, změňte vyhledávací pole tak, že před čárkou bude znak hvězdičky (*, místo,*)
Odebrat text pomocí vzorců
Pokud potřebujete větší kontrolu nad tím, jak najít a nahradit text před nebo za konkrétním znakem, je lepší použít vestavěné textové vzorce v Excelu.
Předpokládejme, že máte níže uvedenou datovou sadu, kde chcete odstranit veškerý text za čárkou.
Níže je uveden vzorec, jak to provést:
= LEFT (A2, FIND (",", A2) -1)
Výše uvedený vzorec používá funkci FIND k nalezení polohy čárky v buňce.
Toto číslo pozice pak používá funkce DOLEVA k extrahování všech znaků před čárkou. Protože nechci jako výsledek výsledek čárku, odečetl jsem 1 od výsledné hodnoty vzorce Najít.
To byl jednoduchý scénář.
Vezměme si trochu složitý.
Předpokládejme, že mám níže uvedenou sadu dat, kde chci po druhé čárce odstranit veškerý text.
Zde je vzorec, který to provede:
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Protože tato sada dat obsahuje více čárek, nemohu použít funkci NAJÍT k získání pozice první čárky a extrahovat vše nalevo od ní.
Potřebuji nějak zjistit polohu druhé čárky a poté extrahovat vše, co je nalevo od druhé čárky.
K tomu jsem pomocí funkce SUBSTITUTE změnil druhou čárku na vykřičník. Nyní mi to v buňce dává jedinečný charakter. Nyní mohu pomocí polohy vykřičníku extrahovat vše nalevo od druhé čárky.
Tato poloha vykřičníku se používá ve funkci VLEVO k extrahování všeho před druhou čárkou.
Zatím je vše dobré!
Ale co když je v datové sadě nekonzistentní počet čárek.
Například v níže uvedené sadě dat mají některé buňky dvě čárky a některé buňky tři čárky a já potřebuji extrahovat veškerý text před poslední čárkou.
V tomto případě potřebuji nějak zjistit polohu posledního výskytu čárky a poté extrahovat vše nalevo od ní.
Níže je vzorec, který to udělá
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Výše uvedený vzorec používá funkci LEN k nalezení celkové délky textu v buňce a také délky textu bez čárky.
Když odečtu tyto dvě hodnoty, zobrazí se mi celkový počet čárek v buňce.
Takže by mi to dalo 3 pro buňku A2 a 2 pro buňku A4.
Tato hodnota je poté použita ve vzorci SUBSTITUTE k nahrazení poslední čárky vykřičníkem. A pak můžete pomocí levé funkce extrahovat vše, co je nalevo od vykřičníku (kde bývala poslední čárka)
Jak vidíte na příkladech, kombinace textových vzorců vám umožňuje zvládnout mnoho různých situací.
Protože je výsledek spojen s původními daty, výsledek se při změně původních dat automaticky aktualizuje.
Odebrat text pomocí Flash Fill
Flash Fill je nástroj, který byl představen v aplikaci Excel 2013 a je k dispozici ve všech dalších verzích.
Funguje to tak, že identifikujete vzory při ručním zadávání dat a následném extrapolaci, abyste získali data pro celý sloupec.
Pokud tedy chcete odstranit text před nebo za konkrétním znakem, stačí ukázat flash Fairy, jak by výsledek vypadal (několikanásobným zadáním ručně) a flash fill automaticky porozumí vzoru a dá vám výsledky.
Ukážu vám to na příkladu.
Níže mám datovou sadu, kde chci odstranit veškerý text za čárkou.
Zde jsou kroky, jak to provést pomocí Flash Fill:
- Do buňky B2, která je sousedním sloupcem našich dat, zadejte ručně „Jeffery Haggins“ (což je očekávaný výsledek)
- Do buňky B3 zadejte „Tim Scott“ (což je očekávaný výsledek pro druhou buňku)
- Vyberte rozsah B2: B10
- Klikněte na kartu Domů
- Ve skupině Úpravy klikněte na rozevírací nabídku Vyplnit
- Klikněte na Flash Fill
Výše uvedené kroky vám poskytnou výsledek, jak je uvedeno níže:
Můžete také použít klávesovou zkratku Flash Fill Ovládání + E po výběru buněk ve sloupci výsledků (v našem příkladu sloupec B)
Flash Fill je úžasný nástroj a ve většině případů dokáže rozpoznat vzor a poskytnout vám správný výsledek. ale v některých případech nemusí být schopen správně rozpoznat vzor a může vám poskytnout špatné výsledky.
Ujistěte se tedy, že jste si znovu zkontrolovali výsledky Flash Fill
A stejně jako jsme odstranili veškerý text po konkrétním znaku pomocí flash fill, můžete stejným způsobem odstranit text před konkrétním znakem. stačí ukázat flash fill, jak by měl výsledek vypadat jako můj internet ručně v sousedním sloupci, a to by udělalo zbytek.
Odebrat text pomocí VBA (vlastní funkce)
Celý koncept odstranění textu před nebo za konkrétním znakem závisí na nalezení polohy tohoto znaku.
Jak je vidět výše, nalezení posledního výskytu tohoto znakového zboží znamená použití odvarů vzorců.
Pokud je to něco, co musíte dělat poměrně často, můžete tento proces zjednodušit vytvořením vlastní funkce pomocí VBA (nazývané funkce definované uživatelem).
Po vytvoření můžete tuto funkci znovu a znovu používat. Je také mnohem jednodušší a jednodušší (protože většina těžkých zvedání se provádí pomocí kódu VBA na zadním konci).
Pod kódem VBA, který můžete použít k vytvoření vlastní funkce v aplikaci Excel:
Funkce LastPosition (rCell As Range, rChar As String) 'Tato funkce udává poslední pozici zadaného znaku' Tento kód byl vyvinut společností Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
Kód VBA musíte umístit do běžného modulu v editoru VB nebo do osobního sešitu maker. Jakmile ji tam máte, můžete ji použít jako jakoukoli jinou běžnou funkci listu v sešitu.
Tato funkce má 2 argumenty:
- Odkaz na buňku, u které chcete najít poslední výskyt konkrétního znaku nebo textového řetězce
- Znak nebo textový řetězec, jehož pozici musíte najít
Předpokládejme, že nyní máte níže uvedenou sadu dat a chcete odstranit veškerý text za poslední čárkou a mít pouze text před poslední čárkou.
Níže je uveden vzorec, který to provede:
= VLEVO (A2, poslední pozice (A2, ",")-1)
Ve výše uvedeném vzorci jsem určil nalezení polohy poslední čárky. V případě, že chcete najít pozici nějakého jiného znaku nebo textového řetězce, měli byste to použít jako druhý argument ve funkci.
Jak vidíte, je to mnohem kratší a jednodušší ve srovnání s dlouhým textovým vzorcem, který jsme použili v předchozí části
Pokud vložíte kód VBA do modulu v sešitu, budete moci tuto vlastní funkci používat pouze v tomto konkrétním sešitu. Pokud to chcete použít ve všech sešitech ve vašem systému, musíte tento kód zkopírovat a vložit do osobního sešitu maker.
Toto jsou tedy některé z e -mailů, které můžete použít k rychlému odstranění textu před nebo za konkrétním znakem v Excelu.
Pokud se jedná o jednoduchou jednorázovou věc, můžete to udělat pomocí funkce Najít a nahradit. co když je to trochu složitější, pak musíte použít kombinaci vestavěných vzorců aplikace Excel nebo dokonce vytvořit vlastní vlastní vzorec pomocí jazyka VBA.
Doufám, že jste našli tento návod užitečný.
