Cum să împărțiți celulele în Excel (separate în mai multe coloane)

Pot exista situații când trebuie să împărțiți celulele în Excel. Acestea ar putea fi atunci când obțineți datele dintr-o bază de date sau le copiați de pe internet sau le primiți de la un coleg.

Un exemplu simplu în care trebuie să împărțiți celulele în Excel este atunci când aveți nume complete și doriți să le împărțiți în prenume și prenume.

Sau primiți adresa ”și doriți să împărțiți adresa, astfel încât să puteți analiza orașele sau codul PIN separat.

Cum să împărțiți celulele în Excel

În acest tutorial, veți afla cum să împărțiți celulele în Excel folosind următoarele tehnici:

• Folosind caracteristica Text în coloană.
• Utilizarea funcțiilor de text Excel.
• Folosind Flash Fill (disponibil în 2013 și 2016).

Sa incepem!

Împărțiți celulele în Excel folosind textul în coloană

Mai jos am o listă cu numele unora dintre personajele mele fictive preferate și vreau să împart aceste nume în celule separate:

Iată pașii pentru împărțirea acestor nume în prenume și nume:

• Selectați celulele în care aveți textul pe care doriți să îl împărțiți (în acest caz A2: A7).
• Faceți clic pe fila Date
• În grupul „Instrumente de date”, faceți clic pe „Text în coloane”.
• În Expertul Conversie text în coloane:
  • Pasul 1 din 3 din Expertul text către coloane: Asigurați-vă că este selectat Delimitat (este selecția implicită). Acest lucru vă va permite să separați prenumele și prenumele pe baza unui separator specificat (bara de spațiu în acest caz).
  • Faceți clic pe Următorul.
  • Pasul 2 din 3 Expertul text către coloane: Selectați Spațiu ca delimitator și deselectați orice altceva. Puteți vedea cum ar arăta rezultatul dvs. în secțiunea Previzualizare date din caseta de dialog.
  • Faceți clic pe Următorul.
  • Pasul 3 din 3 Expertul text către coloane: În acest pas, puteți specifica formatul de date și unde doriți rezultatul. Voi păstra formatul de date la fel de general, pe măsură ce am date de text de împărțit. Destinația implicită este A2 și dacă continuați cu aceasta, va înlocui setul de date original. Dacă doriți să păstrați datele originale intacte, selectați o altă celulă ca destinație. În acest caz, este selectat B2.
  • Faceți clic pe Finalizare.

Aceasta va împărți instantaneu textul celulei în două coloane diferite.

Notă:

• Caracteristica Text către coloană împarte conținutul celulelor pe baza delimitatorului. Deși acest lucru funcționează bine dacă doriți să separați prenumele și prenumele, în cazul prenumelui, mijlocului și prenumelui, acesta îl va împărți în trei părți.
• Rezultatul obținut din utilizarea funcției Text în coloană este static. Aceasta înseamnă că, dacă există modificări în datele originale, va trebui să repetați procesul pentru a obține rezultate actualizate.

Împărțiți celulele în Excel folosind funcțiile de text

Funcțiile Excel Text sunt excelente atunci când doriți să tăiați și să tăiați șiruri de text.

În timp ce caracteristica Text în coloană oferă un rezultat static, rezultatul obținut din utilizarea funcțiilor este dinamic și se va actualiza automat atunci când modificați datele originale.

Împărțirea numelor care au prenume și prenume

Să presupunem că aveți aceleași date ca cele prezentate mai jos:

Extragerea prenumelui

Pentru a obține prenumele din această listă, utilizați următoarea formulă:

= STÂNGA (A2, CĂUTARE ("", A2) -1)

Această formulă ar detecta primul caracter spațial și apoi va returna tot textul dinaintea acelui caracter spațial:

Această formulă folosește funcția CĂUTARE pentru a obține poziția caracterului spațial. În cazul lui Bruce Wayne, personajul spațial se află pe poziția 6. Apoi extrage toate caracterele din stânga acestuia folosind funcția STÂNGA.

Extragerea numelui de familie

În mod similar, pentru a obține numele de familie, utilizați următoarea formulă:

= DREAPTA (A2, LEN (A2) -CĂUTARE ("", A2))

Această formulă utilizează funcția de căutare pentru a găsi poziția barei de spațiu utilizând funcția CĂUTARE. Apoi scade acel număr din lungimea totală a numelui (care este dat de funcția LEN). Aceasta dă numărul de caractere din numele de familie.

Acest nume de familie este apoi extras folosind funcția DREAPTA.

Notă: Este posibil ca aceste funcții să nu funcționeze bine dacă aveți spații principale, finale sau duble în nume. Faceți clic aici pentru a afla cum să eliminați spațiile principale / finale / duble în Excel.

Împărțirea numelor care au un prenume, prenume și prenume

Pot exista cazuri când primiți o combinație de nume în care unele nume au și un prenume.

Formula în astfel de cazuri este puțin complexă.

Extragerea prenumelui

Pentru a obține prenumele:

= STÂNGA (A2, CĂUTARE ("", A2) -1)

Aceasta este aceeași formulă pe care am folosit-o atunci când nu exista un nume de mijloc. Pur și simplu caută primul caracter spațial și returnează toate caracterele înainte de spațiu.

Extragerea numelui de mijloc

Pentru a obține numele de mijloc:

= IFERROR (MID (A2, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")

Funcția MID începe de la primul caracter spațial și extrage numele de mijloc folosind diferența de poziție a primului și celui de-al doilea caracter spațial.

În cazurile în care nu există un nume de mijloc, funcția MID returnează o eroare. Pentru a evita eroarea, aceasta este înfășurată în funcția IFERROR.

Extragerea numelui de familie

Pentru a obține numele de familie, utilizați formula de mai jos:

= IF (LEN (A2) -LEN (SUBSTITUT (A2, "", "")) = 1, DREAPTA (A2, LEN (A2) -CĂUTARE ("", A2)), DREAPTA (A2, LEN (A2) -CĂUTARE ("", A2, CĂUTARE ("", A2) +1)))

Această formulă verifică dacă există sau nu un prenume (numărând numărul de caractere spațiale). Dacă există doar 1 caracter spațial, acesta returnează pur și simplu tot textul la dreapta caracterului spațial.

Dar dacă sunt 2, atunci se observă al doilea caracter spațial și returnează numărul de caractere după al doilea spațiu.

Notă: Aceste formule funcționează bine atunci când aveți nume care au fie numele și prenumele, fie numele, prenumele și prenumele. Cu toate acestea, dacă aveți un amestec în care aveți sufixe sau salutări, va trebui să modificați formulele în continuare.

Împărțiți celulele în Excel folosind Flash Fill

Flash Fill este o nouă funcție introdusă în Excel 2013.

Ar putea fi foarte util atunci când aveți un model și doriți să extrageți rapid o parte din acesta.

De exemplu, să luăm numele și prenumele:

Umplerea blițului funcționează prin identificarea modelelor și reproducerea acesteia pentru toate celelalte celule.

Iată cum puteți extrage prenumele din listă folosind Flash Fill:

• În celula B2, introduceți prenumele pentru Bruce Wayne (adică Bruce).
• Cu celula selectată, veți observa un pătrat mic la capătul din dreapta selecției celulei. Faceți dublu clic pe el. Aceasta va umple același nume în toate celulele.
• Când celulele sunt umplute, în partea dreaptă jos veți vedea pictograma Opțiuni de completare automată. Apasa pe el.
• Selectați Flash Fill din listă.
• De îndată ce selectați Flash Fill, veți observa că toate celulele se actualizează singure și acum afișează prenumele pentru fiecare nume.

Cum funcționează Flash Fill?

Flash Fill caută modelele din setul de date și reproduce modelul.

Flash Fill este o caracteristică surprinzător de inteligentă și funcționează așa cum era de așteptat în majoritatea cazurilor. Dar eșuează și în unele cazuri.

De exemplu, dacă am o listă de nume care are o combinație de nume cu unele care au un prenume și altele nu.

Dacă extrag numele de mijloc într-un astfel de caz, Flash Fill va returna în mod eronat numele de familie în cazul în care nu există un prenume.

Pentru a fi sincer, aceasta este încă o bună aproximare a tendinței. Cu toate acestea, nu este ceea ce am vrut.

Dar este totuși un instrument suficient de bun pentru a păstra în arsenalul tău și pentru a-l folosi ori de câte ori apare nevoia.

Iată un alt exemplu în care Flash Fill funcționează excelent.

Am un set de adrese din care vreau să extrag rapid orașul.

Pentru a obține rapid orașul, introduceți numele orașului pentru prima adresă (introduceți Londra în celula B2 din acest exemplu) și utilizați completarea automată pentru a umple toate celulele. Acum utilizați Flash Fill și vă va oferi instantaneu numele orașului de la fiecare adresă.

În mod similar, puteți împărți adresa și extrage orice parte a adresei.

Rețineți că aceasta ar avea nevoie ca adresa să fie un set de date omogen cu același delimitator (virgulă în acest caz).

În cazul în care încercați să utilizați Flash Fill atunci când nu există un model, acesta vă va arăta o eroare așa cum se arată mai jos:

În acest tutorial, am acoperit trei moduri diferite de a împărți celulele din Excel în mai multe coloane (folosind text în coloane, formule și Flash Fill)

Sper că ați găsit util acest tutorial Excel.