Obsah
Konce riadkov v rámci tej istej bunky, pridané pomocou klávesovej skratky ostatné+vstúpiť je veľmi bežná a bežná vec. Niekedy ich vyrábajú samotní používatelia, aby dodali krásu dlhému textu. Niekedy sa takéto prenosy pridávajú automaticky pri sťahovaní údajov z ľubovoľných pracovných programov (ahoj 1C, SAP atď.) Problém je v tom, že potom musíte takéto tabuľky nielen obdivovať, ale s nimi aj pracovať – a potom tieto prenosy neviditeľných znakov môžu byť problém. A možno sa nestanú – ak viete, ako s nimi správne zaobchádzať.
Poďme sa na túto problematiku pozrieť podrobnejšie.
Odstránenie zlomov riadkov nahradením
Ak sa potrebujeme zbaviť pomlčiek, prvá vec, ktorá nás zvyčajne napadne, je klasická technika „nájdi a nahraď“. Vyberte text a potom pomocou klávesovej skratky zavolajte okno náhrady ctrl+H alebo cez Domov – Nájsť a vybrať – Nahradiť (Domov — Nájsť a vybrať — Nahradiť). Jedna nezrovnalosť – nie je veľmi jasné, ako zadať do horného poľa Nájsť (Nájsť čo) náš neviditeľný znak prerušenia riadku. ostatné+vstúpiť tu to už bohužiaľ nefunguje, skopírovanie tohto symbolu priamo z bunky a jeho vloženie sem tiež zlyháva.
Pomôže kombinácia ctrl+J – to je alternatíva ostatné+vstúpiť v dialógových oknách alebo vstupných poliach programu Excel:
Upozorňujeme, že potom, čo umiestnite blikajúci kurzor do horného poľa a stlačíte ctrl+J – v samotnom poli sa nič nezobrazí. Nezľaknite sa – je to normálne, symbol je neviditeľný 🙂
Do spodného poľa náhradka (Nahraď s) buď nezadávajte nič, alebo zadajte medzeru (ak chceme pomlčky nielen odstrániť, ale nahradiť medzerou, aby sa riadky nezlepili do jedného celku). Stačí stlačiť tlačidlo Vymeňte všetko (Nahradiť všetko) a naše pomlčky zmiznú:
Odtieň: po vykonaní výmeny zadaný s ctrl+J neviditeľný charakter zostáva v poli Nájsť a môže v budúcnosti prekážať – nezabudnite ho vymazať umiestnením kurzora do tohto poľa a niekoľkokrát (pre spoľahlivosť) stlačením kláves vymazať и Backspace.
Odstránenie zlomov riadkov pomocou vzorca
Ak potrebujete vyriešiť problém so vzorcami, môžete použiť vstavanú funkciu TLAČ (ČISTÝ), ktorý dokáže vymazať text od všetkých netlačiteľných znakov vrátane našich nešťastných zalomení riadkov:
Táto možnosť však nie je vždy vhodná, pretože čiary po tejto operácii môžu byť zlepené. Aby ste tomu zabránili, musíte pomlčku nielen odstrániť, ale nahradiť ju medzerou (pozri nasledujúci odsek).
Nahradenie zlomov riadkov vzorcom
A ak chcete nielen odstrániť, ale nahradiť ostatné+vstúpiť napríklad na priestore, potom bude potrebná ďalšia, trochu zložitejšia konštrukcia:
Na nastavenie neviditeľného spojovníka použijeme funkciu SYMBOL (CHAR), ktorý vypíše znak podľa jeho kódu (10). A potom funkcia NÁHRADA (NÁHRADIE) hľadá naše pomlčky v zdrojových údajoch a nahrádza ich akýmkoľvek iným textom, napríklad medzerou.
Rozdelenie do stĺpcov zalomením riadku
Mnohým známy a veľmi praktický nástroj Text po stĺpcoch z karty dátum (Údaje – text do stĺpcov) môže tiež fungovať skvele so zalomením riadkov a rozdeliť text z jednej bunky na niekoľko, čím ho zalomí ostatné+vstúpiť. Ak to chcete urobiť, v druhom kroku sprievodcu musíte vybrať variant vlastného oddeľovacieho znaku ostatné (Vlastné) a použite klávesovú skratku, ktorú už poznáme ctrl+J ako alternatívu ostatné+vstúpiť:
Ak vaše údaje môžu obsahovať niekoľko zalomení riadkov za sebou, môžete ich „zbaliť“ začiarknutím políčka S po sebe nasledujúcimi oddeľovačmi zaobchádzajte ako s jedným (S po sebe idúcimi oddeľovačmi zaobchádzajte ako s jedným).
Po kliknutí na ďalšie (Ďalšie) a prejdením všetkých troch krokov sprievodcu dostaneme požadovaný výsledok:
Upozorňujeme, že pred vykonaním tejto operácie je potrebné vložiť dostatočný počet prázdnych stĺpcov napravo od rozdeleného stĺpca, aby výsledný text neprepísal hodnoty (ceny), ktoré boli napravo.
Rozdeľte do riadkov pomocou Alt + Enter cez Power Query
Ďalšou zaujímavou úlohou je rozdeliť viacriadkový text z každej bunky nie do stĺpcov, ale do riadkov:
Ručne to trvá dlho, so vzorcami je to náročné, nie každý vie napísať makro. No v praxi sa tento problém vyskytuje častejšie, ako by sme chceli. Najjednoduchším a najjednoduchším riešením je použiť na túto úlohu doplnok Power Query, ktorý je v Exceli zabudovaný od roku 2016 a pre staršie verzie 2010-2013 si ho možno stiahnuť úplne zadarmo z webu Microsoftu.
Ak chcete načítať zdrojové údaje do Power Query, musíte ich najprv previesť na „inteligentnú tabuľku“ pomocou klávesovej skratky ctrl+T alebo tlačidlom Formátovať ako tabuľku pútko Domov (Domov – Formátovať ako tabuľku). Ak z nejakého dôvodu nechcete alebo nemôžete používať „inteligentné tabuľky“, môžete pracovať s „hlúpymi“. V tomto prípade stačí vybrať pôvodný rozsah a dať mu názov na karte Vzorce – Správca mien – Nové (Vzorce — Správca mien — Nové).
Potom na karte dátum (ak máte Excel 2016 alebo novší) alebo na karte mocenský dotaz (ak máte Excel 2010-2013) môžete kliknúť na tlačidlo Z tabuľky/rozsahu (Z tabuľky/rozsahu)na načítanie našej tabuľky do editora Power Query:
Po načítaní vyberte stĺpec s viacriadkovým textom v bunkách a vyberte príkaz na karte Hlavné Rozdeliť stĺpec – podľa oddeľovača (Domov — Rozdeliť stĺpec — Podľa oddeľovača):
S najväčšou pravdepodobnosťou Power Query automaticky rozpozná princíp delenia a nahradí samotný symbol #(lf) neviditeľný znak posunu riadku (lf = posun riadku = posun riadku) vo vstupnom poli oddeľovača. V prípade potreby je možné vybrať ďalšie znaky z rozbaľovacieho zoznamu v spodnej časti okna, ak najprv začiarknete políčko Rozdelenie so špeciálnymi znakmi (Rozdelené podľa špeciálnych znakov).
Aby bolo všetko rozdelené do riadkov a nie do stĺpcov – nezabudnite prepnúť volič riadky (Po riadkoch) v skupine rozšírených možností.
Zostáva už len kliknúť OK a získaj čo chceš:
Hotový stôl možno pomocou príkazu vyložiť späť na list Zavrieť a načítať – Zavrieť a naložiť... pútko Domov (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...).
Je dôležité poznamenať, že pri používaní Power Query musíte pamätať na to, že pri zmene zdrojových údajov sa výsledky automaticky neaktualizujú, pretože. to nie sú vzorce. Ak chcete aktualizovať, musíte kliknúť pravým tlačidlom myši na konečnú tabuľku na hárku a vybrať príkaz Aktualizovať a uložiť (Obnoviť) alebo stlačte tlačidlo aktualizácie Všetko pútko dátum (Údaje – Obnoviť všetko).
Makro na rozdelenie do riadkov pomocou Alt+Enter
Aby bol obraz úplný, spomeňme aj riešenie predošlého problému pomocou makra. Otvorte Editor jazyka Visual Basic pomocou tlačidla s rovnakým názvom na karte Vývojka (vývojár) alebo klávesové skratky ostatné+F11. V zobrazenom okne vložte nový modul cez ponuku Vložiť – Modul a skopírujte tam nasledujúci kód:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'určenie počtu fragmentov cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'vložiť prázdne riadky pod bunku.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'zadať do nich údaje z poľa Set cell = cell.Offset(n + 1, 0) 'posun na nasledujúcu bunku Next i End Sub
Vráťte sa do Excelu a vyberte bunky s viacriadkovým textom, ktorý chcete rozdeliť. Potom použite tlačidlo Makrá pútko vývojka (Vývojár – makrá) alebo klávesová skratka ostatné+F8na spustenie vytvoreného makra, ktoré urobí všetku prácu za vás:
Voila! Programátori sú v skutočnosti len veľmi leniví ľudia, ktorí sa radšej raz porobia a potom nerobia nič 🙂
- Čistenie textu od nevyžiadanej pošty a ďalších znakov
- Nahradenie textu a odstránenie nedeliteľných medzier pomocou funkcie SUBSTITUTE
- Ako rozdeliť lepkavý text na časti v Exceli