Na prvý pohľad jednoduchý problém s nezrozumiteľným riešením: extrahovať posledné slovo z riadku textu. No, alebo vo všeobecnosti posledný fragment, oddelený daným oddeľovacím znakom (medzera, čiarka atď.) Inými slovami, je potrebné implementovať spätné vyhľadávanie (od konca po začiatok) v reťazci daný znak a potom extrahujte všetky znaky napravo od neho.
Pozrime sa na tradične niekoľko spôsobov, z ktorých si môžete vybrať: vzorce, makrá a cez Power Query.
Metóda 1. Vzorce
Aby sme ľahšie pochopili podstatu a mechaniku vzorca, začnime trochu z diaľky. Najprv zväčšíme počet medzier medzi slovami v našom zdrojovom texte napríklad na 20 kusov. Môžete to urobiť pomocou funkcie nahradiť. NÁHRADA (NÁHRADIE) a funkcia opakovania daného znaku N-krát – REPEAT (OPAKOVAŤ):
Teraz pomocou funkcie odrežeme 20 znakov z konca výsledného textu PRÁVO (SPRÁVNY):
Už sa otepľuje, však? Zostáva odstrániť nadbytočné medzery pomocou funkcie TRIM (TRIM) a problem bude vyrieseny:
V anglickej verzii bude náš vzorec vyzerať takto:
=TRIM(RIGHT(SUBSTITUTE(A1;» «;REPT(» «;20));20))
Dúfam, že je jasné, že v zásade nie je potrebné vkladať presne 20 medzier – postačí ľubovoľné číslo, pokiaľ je väčšie ako dĺžka najdlhšieho slova v zdrojovom texte.
A ak je potrebné rozdeliť zdrojový text nie medzerou, ale iným oddeľovacím znakom (napríklad čiarkou), potom bude potrebné náš vzorec mierne opraviť:
Metóda 2. Funkcia makra
Úlohu extrahovať posledné slovo alebo fragment z textu je možné vyriešiť aj pomocou makier, a to napísaním funkcie spätného vyhľadávania vo Visual Basicu, ktorá urobí to, čo potrebujeme – vyhľadá daný podreťazec v reťazci v opačnom smere – od koniec na začiatok.
Stlačte klávesovú skratku ostatné+F11 alebo tlačidlo Visual Basic pútko vývojka (vývojár)otvorte editor makier. Potom cez menu pridajte nový modul Vložiť – Modul a skopírujte tam nasledujúci kód:
Funkcia LastWord(txt As String, Voliteľné delim As String = " ", Voliteľné n As Integer = 1) As String arFragments = Split(txt, delm) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Teraz môžete zošit uložiť (vo formáte s povoleným makrom!) a použiť vytvorenú funkciu v nasledujúcej syntaxi:
=PoslednéSlovo(txt ; delim ; n)
kde
- txt – bunka so zdrojovým textom
- DELI — oddeľovací znak (predvolené — medzera)
- n – aké slovo má byť extrahované z konca (štandardne prvé od konca)
Pri akýchkoľvek budúcich zmenách v zdrojovom texte bude naša funkcia makra prepočítaná za behu, ako každá štandardná funkcia listu Excel.
Metóda 3. Power Query
mocenský dotaz je bezplatný doplnok od spoločnosti Microsoft na import údajov do Excelu z takmer akéhokoľvek zdroja a následnú transformáciu stiahnutých údajov do akejkoľvek formy. Sila a štýl tohto doplnku sú také veľké, že Microsoft štandardne zabudoval všetky svoje funkcie do Excelu 2016. Power Query pre Excel 2010-2013 si môžete bezplatne stiahnuť tu.
Naša úloha oddeliť posledné slovo alebo fragment cez daný oddeľovač pomocou Power Query je vyriešená veľmi jednoducho.
Najprv premeňme našu tabuľku údajov na inteligentnú tabuľku pomocou klávesových skratiek. ctrl+T alebo príkazy Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku):
Potom pomocou príkazu načítame vytvorenú „inteligentnú tabuľku“ do Power Query Z tabuľky/rozsahu (Z tabuľky/rozsahu) pútko dátum (ak máte Excel 2016) alebo na karte mocenský dotaz (ak máte Excel 2010-2013):
V okne editora dotazov, ktoré sa otvorí, na karte Premena (Transformácia) vybrať tím Rozdeliť stĺpec – podľa oddeľovača (Rozdelený stĺpec – podľa oddeľovača) a potom zostáva nastaviť znak oddeľovača a vybrať možnosť Úplne vpravo oddeľovačvystrihnúť nie všetky slová, ale iba posledné:
Po kliknutí na OK posledné slovo bude oddelené do nového stĺpca. Nepotrebný prvý stĺpec možno odstrániť kliknutím pravým tlačidlom myši na jeho hlavičku a výberom odstrániť (Odstrániť). Môžete tiež premenovať zostávajúci stĺpec v hlavičke tabuľky.
Výsledky je možné nahrať späť do listu pomocou príkazu Domov — Zavrieť a načítať — Zavrieť a načítať do… (Domov — Zavrieť a načítať — Zavrieť a načítať do...):
A ako výsledok dostaneme:
Takto – lacno a veselo, bez vzorcov a makier, takmer bez dotyku klávesnice 🙂
Ak sa pôvodný zoznam v budúcnosti zmení, bude stačiť kliknúť pravým tlačidlom myši alebo použiť klávesovú skratku ctrl+ostatné+F5 aktualizovať našu požiadavku.
- Rozdelenie lepkavého textu do stĺpcov
- Analýza a analýza textu s regulárnymi výrazmi
- Extrahovanie prvých slov z textu pomocou funkcie SUBSTITUTE