7 lucruri uimitoare Excel poate face textul în coloane pentru dvs.

Textul către coloane este o caracteristică uimitoare în Excel care merită mult mai mult credit decât obține de obicei.

După cum sugerează și numele, este folosit pentru a împărți textul în mai multe coloane. De exemplu, dacă aveți un prenume și un nume în aceeași celulă, puteți utiliza acest lucru pentru a le împărți rapid în două celule diferite.

Acest lucru poate fi foarte util atunci când obțineți datele dvs. din baze de date sau le importați din alte formate de fișiere, cum ar fi Text sau CSV.

În acest tutorial, veți afla despre multe lucruri utile care pot fi realizate folosind Textul în coloane în Excel.

Unde să găsiți text în coloane în Excel

Pentru a accesa Textul în coloane, selectați setul de date și accesați Date → Instrumente de date → Text în coloane.

Aceasta ar deschide Expertul Conversie text în coloane.

Acest vrăjitor are trei pași și ia câteva date de utilizator înainte de a împărți textul în coloane (veți vedea cum pot fi folosite aceste opțiuni diferite în exemplele de mai jos).

Pentru a accesa Textul în coloane, puteți utiliza și comanda rapidă de la tastatură - ALT + A + E.

Acum să ne scufundăm și să vedem câteva lucruri uimitoare pe care le puteți face cu Textul în coloane în Excel.

Exemplul 1 - Împărțiți numele în prenume și prenume

Să presupunem că aveți un set de date așa cum se arată mai jos:

Pentru a împărți rapid prenumele și prenumele și a le obține în celule separate, urmați pașii de mai jos:

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectat Delimitat (care este și selecția implicită). Faceți clic pe Următorul.
• La pasul 2, selectați „Spațiu” ca delimitator. Dacă bănuiți că ar putea exista duble / triple spații consecutive între nume, selectați și opțiunea „Tratați delimitatorii consecutivi ca unul”. Faceți clic pe Următorul.
• La pasul 3, selectați celula de destinație. Dacă nu selectați o celulă de destinație, aceasta va suprascrie setul de date existent cu prenumele din prima coloană și numele de familie din coloana alăturată. Dacă doriți să păstrați datele originale intacte, fie creați o copie, fie alegeți o celulă de destinație diferită.
• Faceți clic pe Finalizare.

Acest lucru vă va oferi instantaneu rezultatele cu prenumele într-o coloană și numele de familie într-o altă coloană.

Notă:

• Această tehnică funcționează bine atunci când numele dvs. reprezintă doar prenumele și numele de familie. În cazul în care există inițiale sau nume de mijloc, este posibil să nu funcționeze. Faceți clic aici pentru un ghid detaliat despre cum să abordați cazurile cu diferite combinații de nume.
• Rezultatul obținut din utilizarea caracteristicii Text în coloane este static. Aceasta înseamnă că, dacă există modificări în datele originale, va trebui să repetați procesul pentru a obține rezultate actualizate.

De asemenea, citiți: Cum să sortați după numele de familie în Excel

Exemplul 2 - Împărțiți ID-urile de e-mail în numele de utilizator și numele de domeniu

Textul către coloane vă permite să alegeți propriul delimitator pentru a împărți textul.

Aceasta poate fi utilizată pentru a împărți adresele de e-mail în nume de utilizator și nume de domeniu, deoarece acestea sunt separate prin semnul @.

Să presupunem că aveți un set de date așa cum se arată mai jos:

Acestea sunt câteva id-uri de e-mail fictive ale unor supereroi grozavi (cu excepția mea, sunt doar un tip obișnuit de pierdere a timpului pe Netflix).

Iată pașii pentru împărțirea acestor nume de utilizator și nume de domeniu utilizând caracteristica Text în coloane.

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectat Delimitat (care este și selecția implicită). Faceți clic pe Următorul.
• La pasul 2, selectați Altele și introduceți @ în caseta din dreapta acestuia. Asigurați-vă că deselectați orice altă opțiune (dacă este bifată). Faceți clic pe Următorul.
• Schimbați celula de destinație cu cea în care doriți rezultatul.
• Faceți clic pe Finalizare.

Aceasta ar împărți adresa de e-mail și vă va oferi prenumele și prenumele în celule separate.

Exemplul 3 - Obțineți domeniul rădăcină de la adresa URL

Dacă lucrați cu adrese URL web, este posibil să trebuiască uneori să cunoașteți numărul total de domenii rădăcină unice.

De exemplu, în cazul http://www.google.com/example1 și http://google.com/example2, domeniul rădăcină este același, care este www.google.com

Să presupunem că aveți un set de date așa cum se arată mai jos:

Iată pașii pentru a obține domeniul rădăcină de la aceste adrese URL:

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectat Delimitat (care este și selecția implicită). Faceți clic pe Următorul.
• La pasul 2, selectați Altele și introduceți / (bară înainte) în caseta din dreapta acestuia. Asigurați-vă că deselectați orice altă opțiune (dacă este bifată). Faceți clic pe Următorul.
• Schimbați celula de destinație cu cea în care doriți rezultatul.
• Faceți clic pe Finalizare.

Acest lucru ar împărți adresa URL și vă va oferi domeniul rădăcină (în a treia coloană, deoarece erau două bare oblice înainte).

Acum, dacă doriți să găsiți numărul de domenii unice, eliminați duplicatele.

Notă: Acest lucru funcționează bine atunci când aveți toate adresele URL care au http: // la început. Dacă nu, atunci veți obține domeniul rădăcină în prima coloană. O bună practică este de a face aceste adrese URL coerente înainte de a utiliza textul în coloane.

Exemplul 4 - Convertiți formatele de date nevalide în formate de date valide

Dacă obțineți datele dvs. din baze de date, cum ar fi SAP / Oracle / Capital IQ, sau le importați dintr-un fișier text, există posibilitatea ca formatul de dată să fie incorect (de exemplu, un format pe care Excel nu îl consideră dată).

Există doar câteva formate pe care Excel le poate înțelege și orice alt format trebuie convertit într-un format valid pentru a fi utilizat în Excel.

Să presupunem că aveți date în formatul de mai jos (care nu sunt în formatul de dată Excel valid).

Iată pașii pentru a le converti în formate de date valide:

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectat Delimitat (care este și selecția implicită). Faceți clic pe Următorul.
• La pasul 2, asigurați-vă că este selectată opțiunea NO delimiter. Faceți clic pe Următorul.
• În formatul de date Coloană, selectați Data și selectați formatul dorit (DMY ar însemna data lună și an). De asemenea, schimbați celula de destinație cu cea în care doriți rezultatul.
• Faceți clic pe Finalizare.

Acest lucru ar converti instantaneu aceste formate de date nevalide în formate de date valide pe care le puteți utiliza în Excel.

Exemplul 5 - Convertiți textul în numere

Uneori, când importați date din baze de date sau alte formate de fișiere, numerele sunt convertite în format text.

Există mai multe moduri în care acest lucru se poate întâmpla:

• Având un apostrof înaintea numărului. Acest lucru duce la tratarea numărului ca text.
• Obținerea numerelor ca rezultat al funcțiilor de text, cum ar fi STÂNGA, DREAPTA sau MID.

Problema cu acest lucru este că aceste numere (care sunt în format text) sunt ignorate de funcțiile Excel, cum ar fi SUMĂ și MEDIE.

Să presupunem că aveți un set de date așa cum se arată mai jos, unde numerele sunt în format text (rețineți că acestea sunt aliniate la stânga).

Iată pașii pentru a utiliza textul în coloane pentru a converti textul în numere

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectat Delimitat (care este și selecția implicită). Faceți clic pe Următorul.
• La pasul 2, asigurați-vă că este selectată opțiunea NO delimiter. Faceți clic pe Următorul.
• În formatul de date Coloană, selectați General. De asemenea, schimbați celula de destinație cu cea în care doriți rezultatul.
• Faceți clic pe Finalizare.

Acest lucru ar converti aceste numere înapoi în format general care poate fi folosit acum în formule.

Exemplul 6 - Extrageți primele cinci caractere ale unui șir

Uneori poate fi necesar să extrageți primele caractere ale unui șir. Acest lucru ar putea fi cazul când aveți date tranzacționale și primele cinci caractere (sau orice alt număr de caractere) reprezintă un identificator unic.

De exemplu, în setul de date prezentat mai jos, primele cinci caractere sunt unice pentru o linie de produse.

Iată pașii pentru a extrage rapid primele cinci caractere din aceste date folosind Textul în coloane:

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, asigurați-vă că este selectată Lățimea fixă. Faceți clic pe Următorul.
• La Pasul 2, în secțiunea Previzualizare date, trageți linia verticală și plasați-o după 5 caractere în text. Faceți clic pe Următorul.
• Schimbați celula de destinație cu cea în care doriți rezultatul.
• Faceți clic pe Finalizare.

Acest lucru ar împărți setul de date și vă va oferi primele cinci caractere ale fiecărui ID de tranzacție într-o singură coloană și se va odihni pe toate în a doua coloană.

Notă: Puteți seta și mai multe linii verticale pentru a împărți datele în mai mult de 2 coloane. Doar faceți clic oriunde în zona Previzualizare date și trageți cursorul pentru a seta divizorul.

Exemplul 7 - Conversia numerelor cu semn minus final în numere negative

Deși acest lucru nu este ceva care poate întâlni adesea, dar uneori, s-ar putea să vă regăsiți numerele cu semne minus finale și să le faceți negative.

Textul către coloane este modalitatea perfectă de a rezolva acest lucru.

Să presupunem că aveți un set de date așa cum se arată mai jos:

Iată pașii pentru a converti aceste minusuri finale în numere negative:

• Selectați setul de date.
• Accesați Date → Instrumente de date → Text în coloane. Aceasta va deschide Expertul Conversie text în coloane.
• La pasul 1, faceți clic pe „Următorul”.
• La pasul 2, faceți clic pe „Următorul”.
• La Pasul 3, faceți clic pe butonul Avansat.
• În caseta de dialog Setări avansate de import text, selectați opțiunea „Minus final pentru număr negativ”. Faceți clic pe OK.
• Setați celula de destinație.
• Faceți clic pe Finalizare.

Acest lucru ar plasa instantaneu semnul minus de la sfârșitul numărului de la începutul acestuia. Acum puteți utiliza cu ușurință aceste numere în formule și calcule.