Cuprins
Spărturi de linie în cadrul aceleiași celule, adăugate folosind o comandă rapidă de la tastatură Alt+Intrați este un lucru foarte comun și comun. Uneori, acestea sunt făcute de utilizatori înșiși pentru a adăuga frumusețe textului lung. Uneori, astfel de transferuri sunt adăugate automat la descărcarea datelor din orice program de lucru (bună ziua 1C, SAP, etc.) Problema este că atunci trebuie nu numai să admirați astfel de tabele, ci să lucrați cu ele - și atunci aceste transferuri de caractere invizibile pot fi un problemă. Și s-ar putea să nu devină – dacă știi să le gestionezi corect.
Să aruncăm o privire la această problemă mai detaliat.
Eliminarea întreruperilor de linie prin înlocuire
Dacă trebuie să scăpăm de cratime, atunci primul lucru care ne vine de obicei în minte este tehnica clasică „găsește și înlocuiește”. Selectați textul și apoi apelați fereastra de înlocuire cu o comandă rapidă de la tastatură Ctrl+H sau prin Acasă – Găsiți și selectați – Înlocuiți (Acasă — Găsiți&Selectați — Înlocuiți). O inconsecvență – nu este foarte clar cum să intri în câmpul de sus A găsi (Gaseste ce) personajul nostru invizibil de ruptură de linie. Alt+Intrați aici, din păcate, nu mai funcționează, copierea acestui simbol direct din celulă și lipirea lui aici eșuează.
O combinație va ajuta Ctrl+J – asta e alternativa Alt+Intrați în casetele de dialog Excel sau câmpurile de introducere:
Vă rugăm să rețineți că după ce ați plasat cursorul intermitent în câmpul de sus și apăsați Ctrl+J – nu va apărea nimic în câmpul în sine. Nu vă speriați – acest lucru este normal, simbolul este invizibil 🙂
Spre câmpul de jos Substitui (Înlocui cu) fie nu introduceți nimic, fie introduceți un spațiu (dacă dorim nu numai să eliminăm cratimele, ci să le înlocuim cu un spațiu, astfel încât liniile să nu se lipească într-un singur întreg). Doar apăsați butonul Înlocuiește totul (Înlocuiește-le pe toate) iar cratimele noastre vor dispărea:
Nuanță: după efectuarea înlocuirii introduse cu Ctrl+J caracterul invizibil rămâne în câmp A găsi și poate interfera în viitor – nu uitați să îl ștergeți plasând cursorul în acest câmp și de mai multe ori (pentru fiabilitate) apăsând tastele Șterge и backspace.
Eliminarea întreruperilor de linie cu o formulă
Dacă trebuie să rezolvați problema cu formule, atunci puteți utiliza funcția încorporată PRINT (CURAT), care poate șterge textul tuturor caracterelor care nu pot fi imprimate, inclusiv ale nefericitelor noastre rupturi de rând:
Această opțiune, însă, nu este întotdeauna convenabilă, deoarece liniile după această operație pot fi lipite împreună. Pentru a preveni acest lucru, trebuie nu numai să eliminați cratima, ci și să o înlocuiți cu un spațiu (vezi paragraful următor).
Înlocuirea întreruperilor de linie cu o formulă
Și dacă doriți nu doar să ștergeți, ci să înlocuiți Alt+Intrați pe, de exemplu, un spațiu, apoi va fi necesară o altă construcție puțin mai complexă:
Pentru a seta o cratima invizibilă folosim funcția SIMBOL (CHAR), care scoate un caracter prin codul său (10). Și apoi funcția SUBSTITUI (SUBSTITUI) caută cratimele noastre în datele sursă și le înlocuiește cu orice alt text, de exemplu, cu un spațiu.
Împărțirea în coloane prin întrerupere de linie
Un instrument familiar pentru mulți și foarte la îndemână Text pe coloane din filă Date (Date — Text în coloane) poate funcționa grozav cu rupturi de rând și poate împărți textul dintr-o celulă în mai multe, rupându-l Alt+Intrați. Pentru a face acest lucru, la al doilea pas al expertului, trebuie să selectați o variantă a caracterului delimitator personalizat Altele (Personalizat) și folosiți comanda rapidă de la tastatură pe care o știm deja Ctrl+J Ca o alternativa Alt+Intrați:
Dacă datele dvs. pot conține mai multe întreruperi de rând la rând, atunci le puteți „restrânge” activând caseta de selectare Tratați delimitatorii consecutivi ca unul singur (Tratați delimitatorii consecutivi ca unul singur).
După ce faceți clic pe Pagina Următoare → (Următor →) și parcurgând toți cei trei pași ai vrăjitorului, obținem rezultatul dorit:
Vă rugăm să rețineți că înainte de a efectua această operațiune, este necesar să introduceți un număr suficient de coloane goale în dreapta coloanei împărțite, astfel încât textul rezultat să nu suprascrie valorile (prețurile) care erau în dreapta.
Împărțiți în linii prin Alt + Enter prin Power Query
O altă sarcină interesantă este să împărțiți textul cu mai multe linii din fiecare celulă nu în coloane, ci în linii:
Este nevoie de mult timp pentru a face acest lucru manual, este dificil cu formulele, nu oricine poate scrie o macro. Dar, în practică, această problemă apare mai des decât ne-am dori. Cea mai simplă și mai ușoară soluție este să utilizați add-in-ul Power Query pentru această sarcină, care este integrat în Excel din 2016, iar pentru versiunile anterioare 2010-2013 poate fi descărcat complet gratuit de pe site-ul Microsoft.
Pentru a încărca datele sursă în Power Query, trebuie mai întâi să le convertiți într-un „tabel inteligent” cu o comandă rapidă de la tastatură Ctrl+T sau prin buton Formatați ca tabel fila Acasă (Acasă — Format ca tabel). Dacă dintr-un motiv oarecare nu doriți sau nu puteți utiliza „mesele inteligente”, atunci puteți lucra cu cele „prostice”. În acest caz, selectați intervalul inițial și dați-i un nume în filă Formule – Manager de nume – Nou (Formule — Manager de nume — Nou).
După aceea, pe filă Date (dacă aveți Excel 2016 sau o versiune ulterioară) sau pe filă Interogare de alimentare (daca ai Excel 2010-2013) poti da click pe butonul De la masă/gamă (De la masă/gamă)pentru a încărca tabelul nostru în editorul Power Query:
După încărcare, selectați coloana cu text cu mai multe linii în celule și selectați comanda din fila Principal Coloana împărțită – După delimitare (Acasă — Împărțire coloană — După delimitare):
Cel mai probabil, Power Query va recunoaște automat principiul diviziunii și va înlocui simbolul în sine #(lf) caracter invizibil de avans de linie (lf = line feed = line feed) în câmpul de introducere a separatorului. Dacă este necesar, alte caractere pot fi selectate din lista derulantă din partea de jos a ferestrei, dacă bifați mai întâi caseta Împărțit cu caractere speciale (Impartit prin caractere speciale).
Pentru ca totul să fie împărțit în rânduri și nu coloane - nu uitați să comutați selectorul Rânduri (Pe rânduri) în grupul de opțiuni avansate.
Tot ce rămâne este să dai clic pe OK și obțineți ceea ce doriți:
Masa finită poate fi descărcată înapoi pe foaie folosind comanda Închideți și încărcați - Închideți și încărcați în... fila Acasă (Acasă — Închide&Încărcare — Închide&Încărcare la...).
Este important de reținut că atunci când utilizați Power Query, trebuie să vă amintiți că atunci când datele sursă se modifică, rezultatele nu sunt actualizate automat, deoarece. acestea nu sunt formule. Pentru a actualiza, trebuie să faceți clic dreapta pe tabelul final de pe foaie și să selectați comanda Actualizați și salvați (Reîmprospăta) sau apăsați butonul Actualizați toate fila Date (Date — Actualizează tot).
Macro pentru împărțirea în linii prin Alt+Enter
Pentru a completa imaginea, să menționăm și soluția problemei anterioare cu ajutorul unui macro. Deschideți Editorul Visual Basic folosind butonul cu același nume din filă Dezvoltator (Dezvoltator) sau comenzi rapide de la tastatură Alt+F11. În fereastra care apare, introduceți un nou modul prin meniu Inserare – Modul și copiați următorul cod acolo:
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)) 'determină numărul de fragmente cell.Offset(1, 0) ).Resize(n, 1).EntireRow.Insert 'inserați rânduri goale sub celulă.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'introduceți în ele datele din tabloul Set cell = cell.Offset(n) + 1, 0) 'trece la următoarea celulă Next i End Sub
Reveniți la Excel și selectați celulele cu textul pe mai multe linii pe care doriți să îl împărțiți. Apoi folosiți butonul Macrocomenzi fila dezvoltator (Dezvoltator – Macrocomenzi) sau scurtătură de la tastatură Alt+F8pentru a rula macrocomanda creată, care va face toată munca pentru tine:
Voila! Programatorii sunt, de fapt, oameni foarte lenesi care preferă să muncească din greu o dată și apoi să nu facă nimic 🙂
- Curățarea textului de nedorit și caractere suplimentare
- Înlocuirea textului și eliminarea spațiilor care nu se rup cu funcția SUBSTITUTE
- Cum să împărțiți textul lipicios în părți în Excel