Obsah
Zalomení řádků ve stejné buňce, přidané pomocí klávesové zkratky Další+vstoupit je velmi běžná a běžná věc. Někdy je vytvářejí sami uživatelé, aby dodali na kráse dlouhému textu. Někdy se takové přenosy přidávají automaticky při načítání dat z jakýchkoliv pracovních programů (ahoj 1C, SAP atd.) Problém je v tom, že pak takové tabulky musíte nejen obdivovat, ale také s nimi pracovat – a tyto přenosy neviditelných znaků pak mohou být problém. A nemusí se stát – pokud víte, jak s nimi správně zacházet.
Pojďme se na tuto problematiku podívat podrobněji.
Odstranění zalomení řádků nahrazením
Pokud se potřebujeme zbavit pomlček, pak první věc, která nás obvykle napadne, je klasická technika „najdi a nahraď“. Vyberte text a poté pomocí klávesové zkratky vyvolejte okno pro nahrazení Ctrl+H nebo přes Domů – Najít a vybrat – Nahradit (Domů — Najít a vybrat — Nahradit). Jedna nesrovnalost – není moc jasné, jak se zadává do horního pole Najít (Najít co) náš neviditelný znak zalomení řádku. Další+vstoupit zde už to bohužel nefunguje, zkopírování tohoto symbolu přímo z buňky a jeho vložení sem také selhává.
Pomůže kombinace Ctrl+J – to je alternativa Další+vstoupit v dialogových oknech nebo vstupních polích aplikace Excel:
Vezměte prosím na vědomí, že poté, co umístíte blikající kurzor do horního pole a stisknete Ctrl+J – v samotném poli se nic neobjeví. Nebojte se – to je normální, symbol je neviditelný 🙂
Do spodního pole Náhradní (Nahradit za) buď nezadávejte nic, nebo zadejte mezeru (pokud chceme pomlčky nejen odstranit, ale nahradit mezerou, aby se řádky neslepovaly v jeden celek). Stačí stisknout tlačítko Vyměňte vše (Vyměnit vše) a naše pomlčky zmizí:
odstín: po provedení výměny zadané pomocí Ctrl+J neviditelný charakter zůstává v poli Najít a může v budoucnu rušit – nezapomeňte jej smazat umístěním kurzoru do tohoto pole a několikanásobným (pro spolehlivost) stisknutím kláves Vymazat и Backspace.
Odstranění zalomení řádků pomocí vzorce
Pokud potřebujete vyřešit problém se vzorci, můžete použít vestavěnou funkci TISK (ČISTÝ), která dokáže odstranit z textu všechny netisknutelné znaky, včetně našich nešťastných zalomení řádků:
Tato možnost však není vždy vhodná, protože čáry po této operaci lze slepit. Abyste tomu zabránili, musíte pomlčku nejen odstranit, ale nahradit ji mezerou (viz další odstavec).
Nahrazení zalomení řádků vzorcem
A pokud chcete nejen smazat, ale nahradit Další+vstoupit například na prostoru, pak bude zapotřebí další, o něco složitější konstrukce:
K nastavení neviditelné pomlčky použijeme funkci SYMBOL (CHAR), který vypíše znak svým kódem (10). A pak funkce NÁHRADNÍ (NÁHRADNÍ) vyhledá ve zdrojových datech naše pomlčky a nahradí je jakýmkoli jiným textem, například mezerou.
Rozdělení do sloupců zalomením řádku
Mnoha známý a velmi šikovný nástroj Text po sloupcích ze záložky Data (Data — Text do sloupců) umí také skvěle pracovat s odřádkováním a rozdělit text z jedné buňky na několik a rozdělit jej o Další+vstoupit. Chcete-li to provést, ve druhém kroku průvodce musíte vybrat variantu vlastního oddělovacího znaku Ostatní (Zvyk) a použijte klávesovou zkratku, kterou již známe Ctrl+J jako alternativa Další+vstoupit:
Pokud vaše data mohou obsahovat několik zalomení řádků za sebou, můžete je „sbalit“ zaškrtnutím políčka Zacházejte s po sobě jdoucími oddělovači jako s jedním (Zacházejte s po sobě jdoucími oddělovači jako s jedním).
Po kliknutí na tlačítko další (Další) a procházením všech tří kroků průvodce získáme požadovaný výsledek:
Upozorňujeme, že před provedením této operace je nutné vpravo od rozděleného sloupce vložit dostatečný počet prázdných sloupců, aby výsledný text nepřepsal hodnoty (ceny), které byly vpravo.
Rozdělte na řádky pomocí Alt + Enter prostřednictvím Power Query
Dalším zajímavým úkolem je rozdělit víceřádkový text z každé buňky nikoli do sloupců, ale do řádků:
Ručně to trvá dlouho, se vzorci je to složité, ne každý umí napsat makro. V praxi se ale tento problém vyskytuje častěji, než bychom si přáli. Nejjednodušším a nejsnadnějším řešením je pro tento úkol použít doplněk Power Query, který je v Excelu zabudován od roku 2016 a pro starší verze 2010-2013 je ke stažení zcela zdarma z webu Microsoftu.
Chcete-li načíst zdrojová data do Power Query, musíte je nejprve převést na „chytrou tabulku“ pomocí klávesové zkratky Ctrl+T nebo tlačítkem Formátovat jako tabulku Karta Domů (Domů — Formátovat jako tabulku). Pokud z nějakého důvodu nechcete nebo nemůžete používat „chytré tabulky“, můžete pracovat s „hloupými“. V tomto případě stačí vybrat původní rozsah a dát mu název na kartě Vzorce – Správce názvů – Novinka (Vzorce — Správce jmen — Nové).
Poté na kartě Data (pokud máte Excel 2016 nebo novější) nebo na záložce Dotaz na napájení (pokud máte Excel 2010-2013) můžete kliknout na tlačítko Z tabulky/rozsahu (Z tabulky/rozsahu)k načtení naší tabulky do editoru Power Query:
Po načtení vyberte sloupec s víceřádkovým textem v buňkách a vyberte příkaz na kartě Hlavní Rozdělit sloupec – podle oddělovače (Domů – Rozdělit sloupec – Podle oddělovače):
S největší pravděpodobností Power Query automaticky rozpozná princip dělení a nahradí samotný symbol #(lf) neviditelný znak posunu řádku (lf = posun řádku = posun řádku) ve vstupním poli oddělovače. V případě potřeby lze z rozevíracího seznamu ve spodní části okna vybrat další znaky, pokud nejprve zaškrtnete políčko Rozdělit se speciálními znaky (Rozdělení podle speciálních znaků).
Aby bylo vše rozděleno do řádků a ne do sloupců – nezapomeňte přepnout volič Řádky (po řádcích) ve skupině pokročilých možností.
Nezbývá než kliknout OK a získejte, co chcete:
Hotovou tabulku lze pomocí příkazu vyložit zpět na list Zavřít a načíst – Zavřít a načíst… Karta Domů (Domů — Zavřít&Načíst — Zavřít&Načíst do…).
Je důležité si uvědomit, že při používání Power Query si musíte pamatovat, že když se zdrojová data změní, výsledky se automaticky neaktualizují, protože. to nejsou vzorce. Chcete-li aktualizovat, musíte kliknout pravým tlačítkem na konečnou tabulku na listu a vybrat příkaz Aktualizovat a uložit (Obnovit) nebo stiskněte tlačítko Aktualizovat vše Karta Data (Data – Obnovit vše).
Makro pro rozdělení na řádky pomocí Alt+Enter
Pro dokreslení zmiňme ještě řešení předchozího problému pomocí makra. Otevřete Editor jazyka Visual Basic pomocí stejnojmenného tlačítka na kartě Vývojka (Vývojář) nebo klávesové zkratky Další+F11. V zobrazeném okně vložte nový modul přes nabídku Vložit – Modul a zkopírujte tam následující 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čení počtu fragmentů cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'vložit prázdné řádky pod buňku.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'zadat do nich data z pole Set cell = cell.Offset(n + 1, 0) 'posun na další buňku Next i End Sub
Vraťte se do Excelu a vyberte buňky s víceřádkovým textem, který chcete rozdělit. Poté použijte tlačítko makra Karta vývojka (Vývojář — Makra) nebo klávesovou zkratkou Další+F8ke spuštění vytvořeného makra, které udělá veškerou práci za vás:
Voila! Programátoři jsou ve skutečnosti jen velmi líní lidé, kteří raději jednou tvrdě pracují a pak nedělají nic 🙂
- Vyčištění textu od nevyžádané pošty a dalších znaků
- Nahrazení textu a odstranění pevných mezer pomocí funkce SUBSTITUTE
- Jak rozdělit lepící text na části v Excelu