Inhoud
Regeleinden binnen dezelfde cel, toegevoegd met een sneltoets anders+Enter is een veel voorkomend en veel voorkomend iets. Soms worden ze door gebruikers zelf gemaakt om schoonheid aan lange tekst toe te voegen. Soms worden dergelijke overdrachten automatisch toegevoegd bij het uitladen van gegevens uit werkende programma's (hallo 1C, SAP, enz.) Het probleem is dat je dan niet alleen dergelijke tabellen moet bewonderen, maar ermee moet werken - en dan kunnen deze onzichtbare karakteroverdrachten een probleem. En ze worden misschien niet - als je weet hoe je ze correct moet behandelen.
Laten we dit probleem nader bekijken.
Regeleinden verwijderen door te vervangen
Als we koppeltekens moeten verwijderen, dan is het eerste waar we meestal aan denken de klassieke "vind en vervang"-techniek. Selecteer de tekst en roep vervolgens het vervangende venster op met een sneltoets Ctrl+H of via Home – Zoeken en selecteren – Vervangen (Home — Zoek&Selecteer — Vervang). Eén inconsistentie - het is niet erg duidelijk hoe je in het bovenste veld moet invoeren Vinden (Vind wat) ons onzichtbare regeleindekarakter. anders+Enter hier werkt het helaas niet meer, het rechtstreeks kopiëren van dit symbool uit de cel en het hier plakken lukt ook niet.
Een combinatie helpt Ctrl+J – dat is het alternatief anders+Enter in Excel-dialoogvensters of invoervelden:
Houd er rekening mee dat nadat u de knipperende cursor in het bovenste veld heeft geplaatst en op . heeft gedrukt Ctrl+J – er verschijnt niets in het veld zelf. Wees niet bang - dit is normaal, het symbool is onzichtbaar 🙂
Naar het onderste veld Plaatsvervanger (Vervangen door) ofwel niets invoeren, ofwel een spatie invoeren (als we niet alleen koppeltekens willen verwijderen, maar ze willen vervangen door een spatie zodat de lijnen niet aan elkaar plakken tot één geheel). Druk gewoon op de knop Alles vervangen (Vervang alles) en onze koppeltekens zullen verdwijnen:
Shade: na het uitvoeren van de vervanging ingevoerd met Ctrl+J onzichtbaar personage blijft in het veld Vinden en kan in de toekomst interfereren - vergeet niet om het te verwijderen door de cursor in dit veld te plaatsen en meerdere keren (voor de betrouwbaarheid) op de toetsen te drukken Verwijder и Backspace.
Regeleinden verwijderen met een formule
Als u het probleem met formules moet oplossen, kunt u de ingebouwde functie gebruiken BEDRUK (SCHOON), die de tekst van alle niet-afdrukbare tekens kan wissen, inclusief onze noodlottige regeleinden:
Deze optie is echter niet altijd handig, omdat lijnen na deze bewerking aan elkaar kunnen worden gelijmd. Om dit te voorkomen, moet u niet alleen het koppelteken verwijderen, maar vervangen door een spatie (zie de volgende paragraaf).
Regeleinden vervangen door een formule
En als u niet alleen wilt verwijderen, maar ook wilt vervangen anders+Enter op bijvoorbeeld een ruimte, dan is een andere, iets complexere constructie nodig:
Om een onzichtbaar koppelteken in te stellen gebruiken we de functie SYMBOOL (TEKEN), die een teken uitvoert door zijn code (10). En dan de functie PLAATSVERVANGER (VERVANGING) zoekt naar onze koppeltekens in de brongegevens en vervangt ze door elke andere tekst, bijvoorbeeld door een spatie.
Verdeling in kolommen door regeleinde
Bekend bij velen en zeer handig hulpmiddel Tekst per kolom van het tabblad Data (Gegevens — Tekst naar kolommen) kan ook prima werken met regeleinden en de tekst van de ene cel in meerdere splitsen, waarbij de tekst wordt onderbroken door anders+Enter. Om dit te doen, moet u bij de tweede stap van de wizard een variant van het aangepaste scheidingsteken selecteren Overige (Aangepast) en gebruik de sneltoets die we al kennen Ctrl+J als een alternatief anders+Enter:
Als uw gegevens meerdere regeleinden achter elkaar kunnen bevatten, kunt u deze "samenvouwen" door het selectievakje in te schakelen Behandel opeenvolgende scheidingstekens als één (Behandel opeenvolgende scheidingstekens als één).
Na het klikken op Volgende (Next) en als we alle drie de stappen van de wizard doorlopen, krijgen we het gewenste resultaat:
Houd er rekening mee dat voordat u deze bewerking uitvoert, het noodzakelijk is om een voldoende aantal lege kolommen rechts van de gesplitste kolom in te voegen, zodat de resulterende tekst de waarden (prijzen) die aan de rechterkant stonden niet overschrijft.
Verdeel in regels met Alt + Enter via Power Query
Een andere interessante taak is om de tekst met meerdere regels van elke cel niet in kolommen, maar in regels te verdelen:
Het kost veel tijd om dit handmatig te doen, het is lastig met formules, niet iedereen kan een macro schrijven. Maar in de praktijk komt dit probleem vaker voor dan we zouden willen. De eenvoudigste en gemakkelijkste oplossing is om voor deze taak de Power Query-invoegtoepassing te gebruiken, die sinds 2016 in Excel is ingebouwd en voor eerdere versies 2010-2013 volledig gratis kan worden gedownload van de Microsoft-website.
Om de brongegevens in Power Query te laden, moet u deze eerst converteren naar een "slimme tabel" met een sneltoets Ctrl+T of met de knop Opmaken als een tabel tab Home (Home - Opmaken als tabel). Als je om wat voor reden dan ook geen “slimme” tabellen wilt of kunt gebruiken, dan kun je met “domme” tafels werken. Selecteer in dit geval gewoon het originele bereik en geef het een naam op het tabblad Formules – Naam Manager – Nieuw (Formules — Naam Manager — Nieuw).
Daarna op het tabblad Data (als je Excel 2016 of later hebt) of op het tabblad Power Query (als je Excel 2010-2013 hebt) klik je op de knop Van tafel/reeks (Van tafel/bereik)om onze tabel in de Power Query-editor te laden:
Selecteer na het laden de kolom met tekst met meerdere regels in de cellen en selecteer de opdracht op het tabblad Hoofd Kolom splitsen - op scheidingsteken (Home — Kolom splitsen — Per scheidingsteken):
Hoogstwaarschijnlijk zal Power Query automatisch het principe van deling herkennen en het symbool zelf vervangen #(lf) onzichtbaar line feed-teken (lf = line feed = line feed) in het invoerveld voor scheidingsteken. Indien nodig kunnen andere tekens worden geselecteerd uit de vervolgkeuzelijst onderaan het venster, als u eerst het vakje aanvinkt Splitsen met speciale tekens (Gesplitst door speciale tekens).
Zodat alles is verdeeld in rijen en niet in kolommen - vergeet niet om de selector om te schakelen Rijen (Per rijen) in de groep met geavanceerde opties.
Het enige dat overblijft is klikken op OK en krijg wat je wilt:
De afgewerkte tafel kan weer op het blad worden gelost met behulp van de opdracht Sluiten en laden – Sluiten en laden in… tab Home (Home — Sluiten&Laden — Sluiten&Laden naar…).
Het is belangrijk op te merken dat wanneer u Power Query gebruikt, u moet onthouden dat wanneer de brongegevens veranderen, de resultaten niet automatisch worden bijgewerkt, omdat. dit zijn geen formules. Om bij te werken, moet u met de rechtermuisknop op de finaletafel op het blad klikken en de opdracht selecteren: Bijwerken en opslaan (Vernieuwen) of druk op de knop Alles bijwerken tab Data (Gegevens — Alles vernieuwen).
Macro voor verdeling in lijnen door Alt+Enter
Om het plaatje compleet te maken, vermelden we ook nog de oplossing van het vorige probleem met behulp van een macro. Open de Visual Basic Editor met de knop met dezelfde naam op het tabblad Ontwikkelaar (Ontwikkelaar) of sneltoetsen anders+F11. In het venster dat verschijnt, voeg je een nieuwe module in via het menu Invoegen – Module en kopieer daar de volgende code:
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)) 'bepaal het aantal fragmenten cel.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'lege rijen onder cel invoegen.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'voer daarin gegevens uit de array in Set cell = cell.Offset(n + 1, 0) 'verschuiven naar de volgende cel Volgende i Einde Sub
Keer terug naar Excel en selecteer de cellen met de tekst met meerdere regels die u wilt splitsen. Gebruik dan de knop Macro's tab ontwikkelaar (Ontwikkelaar — Macro's) of sneltoets anders+F8om de gemaakte macro uit te voeren, die al het werk voor u zal doen:
Voila! Programmeurs zijn eigenlijk gewoon hele luie mensen die liever een keer hard werken en dan niets doen 🙂
- Tekst opruimen van rommel en extra tekens
- Tekst vervangen en vaste spaties verwijderen met de SUBSTITUTE-functie
- Hoe plakkerige tekst in delen te splitsen in Excel