Na první pohled jednoduchý problém s nezřejmým řešením: extrahovat poslední slovo z řádku textu. No, nebo, v obecném případě, poslední fragment, oddělený daným oddělovacím znakem (mezera, čárka atd.) Jinými slovy, je nutné implementovat zpětné vyhledávání (od konce k začátku) v řetězci daný znak a poté extrahujte všechny znaky napravo od něj.
Podívejme se na tradičně několik způsobů, ze kterých lze vybírat: vzorce, makra a prostřednictvím Power Query.
Metoda 1. Vzorce
Abychom snáze pochopili podstatu a mechaniku formule, začneme trochu zpovzdálí. Nejprve zvětšíme počet mezer mezi slovy v našem zdrojovém textu například na 20 kusů. Můžete to udělat pomocí funkce nahradit. NÁHRADNÍ (NÁHRADNÍ) a funkce opakování daného znaku N-krát – REPEAT (OPAKOVÁNÍ):
Nyní pomocí funkce odřízneme 20 znaků z konce výsledného textu PRÁVO (ŽE JO):
Začíná se oteplovat, že? Zbývá odstranit přebytečné mezery pomocí funkce TRIM (TRIM) a problém bude vyřešen:
V anglické verzi bude náš vzorec vypadat takto:
=TRIM(RIGHT(SUBSTITUTE(A1;» «;REPT(» «;20));20))
Doufám, že je jasné, že v zásadě není nutné vkládat přesně 20 mezer – postačí libovolné číslo, pokud je větší než délka nejdelšího slova ve zdrojovém textu.
A pokud je třeba zdrojový text rozdělit nikoli mezerou, ale jiným oddělovacím znakem (například čárkou), bude třeba náš vzorec mírně upravit:
Metoda 2. Funkce makra
Úkol extrahování posledního slova nebo fragmentu z textu lze také vyřešit pomocí maker, a to napsáním funkce zpětného vyhledávání ve Visual Basicu, která udělá to, co potřebujeme – vyhledá daný podřetězec v řetězci v opačném směru – od konec na začátek.
Stiskněte klávesovou zkratku Další+F11 nebo tlačítko Visual Basic Karta vývojka (Vývojář)otevřete editor maker. Poté pomocí nabídky přidejte nový modul Vložit – Modul a zkopírujte tam následující kód:
Funkce LastWord(txt As String, Volitelné delim As String = " ", Volitelné n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Nyní můžete sešit uložit (ve formátu s podporou maker!) a použít vytvořenou funkci v následující syntaxi:
=PosledníSlovo(txt ; delim ; n)
kde
- txt – buňka se zdrojovým textem
- vymezit — znak oddělovače (výchozí — mezera)
- n – jaké slovo má být extrahováno z konce (standardně – první od konce)
Při jakýchkoli budoucích změnách ve zdrojovém textu bude naše funkce makra přepočítána za běhu, jako každá standardní funkce listu Excel.
Metoda 3. Power Query
Dotaz na napájení je bezplatný doplněk od společnosti Microsoft pro import dat do Excelu z téměř jakéhokoli zdroje a následnou transformaci stažených dat do libovolné podoby. Síla a coolness tohoto doplňku je tak velká, že Microsoft zabudoval všechny jeho funkce do Excelu 2016 ve výchozím nastavení. Power Query pro Excel 2010–2013 lze zdarma stáhnout zde.
Náš úkol oddělit poslední slovo nebo fragment přes daný oddělovač pomocí Power Query je vyřešen velmi snadno.
Nejprve přeměňme naši datovou tabulku na chytrou tabulku pomocí klávesových zkratek. Ctrl+T nebo příkazy Domů – Formát jako tabulka (Domů — Formátovat jako tabulku):
Vytvořenou „chytrou tabulku“ pak pomocí příkazu načteme do Power Query Z tabulky/rozsahu (Z tabulky/rozsahu) Karta Data (pokud máte Excel 2016) nebo na záložce Dotaz na napájení (pokud máte Excel 2010-2013):
V okně editoru dotazů, které se otevře, na kartě Proměna (Přeměnit) vybrat tým Rozdělit sloupec – podle oddělovače (Rozdělit sloupec – podle oddělovače) a pak zbývá nastavit znak oddělovače a vybrat možnost Oddělovač úplně vpravovystřihnout ne všechna slova, ale pouze to poslední:
Po kliknutí na tlačítko OK poslední slovo bude odděleno do nového sloupce. Nepotřebný první sloupec lze odstranit kliknutím pravým tlačítkem na jeho záhlaví a výběrem Odstranit (Vymazat). Můžete také přejmenovat zbývající sloupec v záhlaví tabulky.
Výsledky lze nahrát zpět do listu pomocí příkazu Domů — Zavřít a načíst — Zavřít a načíst do… (Domů — Zavřít a načíst — Zavřít a načíst do…):
A jako výsledek dostaneme:
Takhle – levně a vesele, bez vzorečků a maker, skoro bez sahání na klávesnici 🙂
Pokud se původní seznam v budoucnu změní, bude stačit kliknout pravým tlačítkem nebo použít klávesovou zkratku Ctrl+Další+F5 aktualizujte naši žádost.
- Rozdělení lepivého textu do sloupců
- Analýza a analýza textu s regulárními výrazy
- Vyjmutí prvních slov z textu funkcí SUBSTITUTE