1. Text to Columns in Excel

 

Unde gasiti facilitatea „Text to Columns” in Excel?

Pentru a accesa „Text to Columns„, selectati setul de date si accesati Data → Data Tools → Text to Columns (vedeti animatia).

Se deschide Expertul de conversie a textului in coloane.

Acest „vrajitor” are trei pasi obligatorii de urmat de catre utilizator, veti vedea cum se pot folosi acestia in exemplele de mai jos.

Pentru a accesa „Text to Columns„, puteti utiliza si comanda rapida de la tastatura: ALT + A + E.

Haideti sa vedem cateva lucruri uimitoare pe care le puteti face cu „Text to Columns” in Excel.

Exemplul 1: Despartirea coloanelor cu Nume, Initiala, Prenume

Presupunem ca avem tabelul cu date de mai jos:

Pentru a imparti rapid numele, initiala, prenumele si a le pune in coloane separate, urmati pasii de mai jos:

Selectati setul de date.

Accesati Data → Data Tools → Text to Columns.

La Pasul 1 al wizard-ului, asigurati-va ca este selectat Delimited (care este si selectia implicita). Faceti clic pe Next.

La Pasul 2 al wizard-ului, selectati „Space” ca delimitator. Daca banuiti ca ar putea exista spatii duble / triple consecutive intre nume, selectati, de asemenea, optiunea „Tratati delimitatorii consecutivi ca unul singur„. Faceti clic pe Next.

La Pasul 3, selectati celula de destinatie. Daca nu selectati o celula de destinatie, aceasta va suprascrie setul de date existent (cel selectat de dvs. inainte). Daca doriti sa pastrati datele originale intacte, creati o copie sau alegeti o celula de destinatie diferita.

Faceti clic pe Finish.

Veti primi instantaneu rezultatele cu numele, initiala si prenumele in coloane separate:

Observatie:

Rezultatul obtinut este unul static. Daca apar modificari in datele originale, va trebui sa reluati procesul pentru a obtine rezultate actualizate.

Exemplul 2: Despartirea adreselor de email

Text to Columns permite sa va alegeti propriul dvs. delimitator pentru a imparti textul.

De pilda, poate fi utilizat pentru a imparti adresele de e-mail in nume de utilizator si nume de domeniu (in acest caz spargerea se face dupa semnul @).

Sa presupunem ca aveti un set de date asa cum se arata mai jos:

Acestea sunt cateva adrese de email fictive.

Iata pasii pentru a imparti aceste nume de utilizator si nume de domeniu de Internet folosind functia „Text to Columns„.

Selectati setul de date.

Accesati Data → Data Tools → Text to Columns.

La Pasul 1 al wizard-ului, asigurati-va ca este selectat Delimited. Faceti clic pe Next.

La Pasul 2, selectati Other si introduceti semnul @ in caseta din dreapta acesteia. Asigurati-va ca deselectati orice alta optiune (daca este bifata). Faceti clic pe Next.

Modificati celula de destinatie unde doriti sa apara rezultatul:

Faceti clic pe Finish.

Exemplul 3: Conversia formatelor de date calendaristice nevalide in formate de date valide

Daca aveti date extrase din bazele de date (cum ar fi SAP / Oracle / SQL Server etc) sau le importati dintr-un fisier text (txt sau csv), exista posibilitatea ca formatul datei sa fie incorect (adica, un format pe care Excel nu il considera ca data calendaristica).

Exista doar cateva formate de date calendaristice pe care Excel le poate intelege si orice alt format trebuie convertit intr-un format valid pentru a fi utilizat in Excel.

Sa presupunem ca aveti date in formatul de mai jos (care nu sunt in format valabil pentru data calendaristica Excel):

Selectam datele (A2:A10), apasam „Text to Columns„, la primul pas sa fie selectat Delimted, iar la urmatorul pas sa nu avem nimic selectat:

Apasam Next. Va rog sa urmariti poza de mai jos:

In sectiunea „Column data format„, selectati Date si formatul (YMD in cazul nostru, deoarece datele calendaristice sunt in formatul AN.Luna.Zi). De asemenea, schimbati celula de destinatie cu cea in care doriti rezultatul.

Exemplul 4: Conversia coloanelor de tip text in coloane numerice

Exemplul meu favorit de la Text to Columns (si cel mai des pe care il aplic in viata reala).

Va rog sa priviti tabelul de mai jos si observati ca desi coloana contine numai cifre, ea este interpretata de Excel ca fiind populata cu texte.

Acest lucru se intampla cand coloana a fost una calculata folosind functii de tip text.

Selectam datele (A2:A10), apasam „Text to Columns„, la primul pas sa fie selectat Delimited, iar la urmatorul pas sa nu avem nimic selectat, apoi apasam inca o data Next.

La pasul 3 al wizard-ului lasam selectata in sectiunea Column data format optiunea General, apoi schimbam de la Destination unde dorim sa ne puna rezultatul convertit:

Exemplul 5: Spargerea coloanei CNP

Unul dintre cele mai des intalnite scenarii de spargere, de data asta insa NU dupa un separator, ci dupa numarul de caractere.

Priviti tabelul de mai jos:

Nu-mi permit regulile GDPR sa folosesc CNP-uri reale, insa ati prins ideea. Vreau sa sparg coloana CNP in toate partile sale componente.

Selectam datele (A2:A10), apasam „Text to Columns„, la primul pas sa fie selectat Fixed width (vedeti poza de mai sus). Apasati Next apoi urmariti animatia de mai jos pentru a vedea cum se face impartirea cand coloana are dimensiune fixa:

Apasam Next, apoi stabilim destinatia:

Rezultatul este cel dorit:

2. Alternative la Text to Columns

In toate exemplele de mai sus am folosit instrumentul Text to Columns, insa mai exista trei metode prin care putem in Excel sa facem impartirea unei coloane:

Exemplul 6: Flash Fill

Flash Fill este o noua caracteristica introdusa in Excel 2013.

Ar putea fi foarte util atunci cand aveti un sablon dupa care doriti sa extrageti dintr-o coloana.

De exemplu, sa luam datele de mai jos:

Completarea automata Flash Fill functioneaza prin identificarea unui tipar si replicarea lui pentru toate celelalte celule.

Iata cum puteti extrage primul nume din lista folosind Flash Fill:

In celula B2, introduceti numele primei persoane (de exemplu, Ionescu).

Cu celula B2 selectata, apasati CTRL + E 😊 Asta-i tot!

Selectati celula C2 si scrieti initiala de la prima persoana, de pilda in cazul nostru scrieti V., apoi CTRL + E

Selectati celula D2 si scrieti prenumele, insa cu litere MARI, adica RADU, apoi CTRL + E. Incredibil, Flash Fill tine cont in tipar si de tipul literelor! 😊

Urmariti animatia de mai jos

Dupa fiecare completare de coloana am apasat combinatia de taste CTRL + E

Cum functioneaza Flash Fill?

Flash Fill cauta modelele din setul de date si reproduce modelul pentru toate celulele in jos.

Flash Fill este surprinzator de inteligent in multe cazuri,

Exemplul 7: Despartirea/Spargerea coloanelor folosind formule

Cele doua metode prezentate mai sus (Text to Columns si Flash Fill) au inconvenientul ca trebuie reaplicate iar si iar in cazul in care coloanele de spart se modifica frecvent.

Metoda profesionista pe care v-o prezint in continuare presupune spargerea AUTOMATA, insa si efortul facut va fi unul pe masura (deoarece trebuie sa scriu cate o formula separata pentru fiecare componenta pe care vreau sa o extrag).

Scenariul 1: Spargerea unei coloane care are dimensiune FIXA:

De pilda coloana CNP, din care dorim sa extragem Gen, AN, LUNA, ZI, JUDET, etc.

Pentru coloanele din stanga (GEN) si din dreapta (C) vom folosi functiile Left si Right, iar pentru celelalte din interior o sa folosim functia MID

  

Scenariul 2: Spargerea unei coloane folosind un separator de spargere (in cazul nostru spatiu):

De exemplu, in tabelul de mai jos trebuie sa scriu 3 formule separate daca doresc sa extrag Nume, Initiala, Prenume:

Functia Search cauta spatiul si returneaza un numar ce reprezinta la a cata poztie s-a gasit spatiul. Apoi cu celelalte functii (Left, Right, Mid, etc.) extragem valorile dorite:

Formula pentru extragerea numelui:

=LEFT(A2,SEARCH(” „,A2)-1)

Formula pentru extragerea Initialei:

=MID(A2,SEARCH(” „,A2)+1,SEARCH(” „,A2,SEARCH(” „,A2)+1)-SEARCH(” „,A2))

Formula pentru extragerea Prenumelui:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,” „,””))=1,RIGHT(A2,LEN(A2)- SEARCH(” „,A2)),RIGHT(A2,LEN(A2)-SEARCH(” „,A2,SEARCH(” „,A2)+1)))

Exemplul 8: Text to columns folosind VBA

Automatizam spargerea unei coloane in functie de un separator folosind VBA/MACRO:

Sub SpargereDupaSeparator()
Selection.TextToColumns Destination:=Range(„B2”), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=” „, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End Sub

Codul de mai sus trebuie sa-l modificati putin dvs. in cazul in care aveti un alt separator sau cand doriti sa puna rezultatul in alta celula decat B2

De pilda, presupunem ca dorim sa spargem coloana Adresa din tabelul de mai jos in componentele sale (Orasul, Strada, Numarul si Etajul):

Codul macro de mai sus trebuie modificat astfel:

Sub SpargereDupaSeparator()
Selection.TextToColumns _
Destination:=Range(„C5”), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=”,”, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End Sub

Dupa ce ati modificat codul de mai sus, acesta trebuie adaugat intr-un modul VBA, apoi trebuie sa-I atasam un shortcut de taste:

Copiati codul de mai sus apoi in Excel apasati combinatia de taste ALT + F11 si urmariti animatia de mai jos

Dupa ce ati introdus codul VBA intr-un modul, trebuie sa atasam o combinatie de taste (de ex. CTRL + SHIFT + X) pentru rularea automata a acestui cod (vizualizati animatia):

Atentie, in casuta Shortcut key este deja apasata tasta CTRL, asa incat apasti doar Shift + X

Totul este in regula acum, insa dorim sa testam ce am facut (vedeti animatia de testare MACRO):

Mai intai selectati celulele pe care doriti sa le impartiti, apoi apasati CTRL + SHIFT + X, confirmati cu OK popularea rezultatului.

Atat despre spargerea celulelor/coloanelor in Excel. Daca mai stiti si alte metode, nu ezitati sa-mi spuneti folosind zona de comentarii de mai jos

Sper ca v-am fost de folos.