Cum să împartă un șir de text în Excel prin virgulă, spațiu, caracter sau masca

tutorial explică cum să împartă celulele din Excel utilizează formule și Split funcția din Text. Veți învăța cum să separați textul prin virgulă, spațiu sau orice alt delimitator și cum să împărțiți șirurile în text și numere.

împărțirea textului dintr-o celulă în mai multe celule este sarcina cu care se confruntă toți utilizatorii Excel din când în când., Într-unul din articolele noastre anterioare, am discutat cum să împărțim celulele în Excel folosind funcția Text în coloană, completarea Flash și împărțirea numelor. Astăzi, vom analiza în profunzime modul în care puteți împărți șiruri folosind formule și caracteristica Split Text.,

• Cum să împartă celulele din Excel utilizează formule
  • Split string prin virgulă, colon, slash, dash sau alte delimitatorul
  • Split șir de linie
  • Formule de a împărți textul și numerele
• Cum de a separa celulele cu Split Text caracteristică
  • Împărți celula cu caracter
  • Split mobil de coarde
  • Split mobil prin masca (model)

Cum să împartă textul în Excel utilizează formule

Pentru a împărți șirul în Excel, puteți folosi, în general, la STÂNGA, la DREAPTA sau la MIJLOCUL funcție în combinație fie cu find sau SEARCH., La prima vedere, unele dintre formule ar putea părea complexe, dar logica este de fapt destul de simplă, iar exemplele următoare vă vor oferi câteva indicii.

împărțiți șirul prin virgulă, colon, slash, dash sau alt delimitator

când împărțiți celulele în Excel, cheia este să localizați poziția delimitatorului în șirul de text. În funcție de sarcina dvs., acest lucru se poate face folosind fie căutarea insensibilă la majuscule, fie găsirea sensibilă la majuscule. După ce aveți poziția delimitatorului, utilizați funcția dreapta, stânga sau mijlocul pentru a extrage partea corespunzătoare a șirului de text., Pentru o mai bună înțelegere, să luăm în considerare următorul exemplu.

să Presupunem că aveți o listă de Sku-uri de Elementul-Color-Dimensiune model, și doriți să împartă în coloana 3 coloane separate:

Cum să împartă șir de linia de pauză în Excel

Pentru a împărți textul de spațiu, folosesc formule similare cu cele demonstrate în exemplul anterior. Singura diferență este că veți avea nevoie de funcția CHAR pentru a furniza caracterul de pauză de linie, deoarece nu îl puteți introduce direct în formulă.,

să Presupunem, celulele pe care doriți să împartă arate similar cu asta:

Ia formule de la exemplul anterior și înlocuiți-o cratimă („-„) cu CHAR(10) unde 10 este codul ASCII pentru Linia de alimentare.

și așa arată rezultatul:

cum se împarte textul și numerele în Excel

Pentru început, nu există o soluție universală care să funcționeze pentru toate șirurile alfanumerice. Ce formulă de utilizat depinde de modelul șir special. Mai jos veți găsi formulele pentru cele două scenarii comune.,

șirul împărțit al modelului „text + număr”

presupunând că aveți o coloană de șiruri cu text și numere combinate, unde un număr urmează întotdeauna textul. Doriți să rupeți șirurile originale, astfel încât textul și numerele să apară în celule separate, astfel:

rezultatul poate fi obținut în două moduri diferite.,metoda 1: numărați cifre și extrageți multe caractere

cel mai simplu mod de a împărți șirul de text unde numărul vine după text este acesta:

pentru a extrage numere, căutați șirul pentru fiecare număr posibil de la 0 la 9, obțineți numerele total și returnați multe caractere de la sfârșitul șirului.,cu șirul original din A2, formula merge după cum urmează:

=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))

pentru a extrage textul, calculați câte caractere de text conține șirul scăzând numărul de cifre extrase (C2) din lungimea totală a șirului original din A2. După aceea, utilizați funcția din stânga pentru a returna multe caractere de la începutul șirului.,

=LEFT(A2,LEN(A2)-LEN(C2))

în cazul în Care A2 este original șir de caractere, iar C2 este numărul extras, după cum se arată în captura de ecran:

Metoda 2: Afla poziția 1 cifre într-un șir

O soluție alternativă ar fi folosind următoarea formulă pentru a determina poziția de prima cifră din șir:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

explicație detaliată a formulei”s logica poate fi găsit aici.,odată ce se găsește poziția primei cifre, puteți împărți textul și numerele folosind formule foarte simple stânga și dreapta.

Pentru a extrage text:

=LEFT(A2, B2-1)

Pentru a extrage numărul:

=RIGHT(A2, LEN(A2)-B2+1)

în cazul în Care A2 este original șir de caractere, iar B2 este poziția primului număr., prima cifră, puteți încorpora MIN formula în STÂNGA și în DREAPTA funcții:

Formula sa extract de text:
=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

Formula sa extract de numere:

=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

Split șir de „numărul + text” model

Dacă divizarea celulelor în cazul în care textul apare după număr, puteți extrage numere cu următoarea formulă:

=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

formula este similar cu cel discutat în exemplul anterior, cu excepția faptului că puteți utiliza funcția de STÂNGA în loc de DREAPTA pentru a obține numărul din partea stângă a șirului.,

Odată ce au numere, text extras prin scăderea numărului de cifre din lungimea totală de șirul inițial:

=RIGHT(A2,LEN(A2)-LEN(B2))

în cazul în Care A2 este original șir și B2 este numărul extras, după cum se arată în captura de ecran de mai jos:

acesta este modul în care puteți împărți șiruri în Excel folosind diferite combinații de funcții diferite., După cum vedeți, formulele sunt departe de a fi evidente, deci este posibil să doriți să descărcați registrul de lucru Excel pentru a le examina mai aproape.

dacă imaginind răsucirile arcane ale formulelor Excel nu este ocupația ta preferată, s-ar putea să vă placă metoda vizuală de a împărți celulele în Excel, care este demonstrată în următoarea parte a acestui tutorial.,o modalitate alternativă de a împărți o coloană în Excel este utilizarea funcției Split Text inclusă în suita noastră finală pentru Excel, care oferă următoarele opțiuni:

• Split cell by character
• Split cell by string
• Split cell by mask (pattern)

pentru a face lucrurile mai clare, să aruncăm o privire mai atentă la fiecare opțiune, una câte una.

împărțiți celulele după caracter

alegeți această opțiune ori de câte ori doriți să împărțiți conținutul celulei la fiecare apariție a caracterului specificat.,

pentru acest exemplu, să luăm șirurile modelului Item-Color-Size pe care l-am folosit în prima parte a acestui tutorial. După cum vă amintiți, le-am separat în 3 coloane diferite folosind 3 formule diferite. Și iată cum puteți obține același rezultat în 2 pași rapizi:

1. presupunând că ați instalat Ultimate Suite, selectați celulele de împărțit și faceți clic pe pictograma Split Text din fila Ablebits Data.,
   
2. Panoul Split Text se va deschide în partea dreaptă a ferestrei Excel și faceți următoarele:
   • extindeți grupul Split după caractere și selectați unul dintre delimitatorii predefiniți sau tastați orice alt caracter în caseta personalizată.
   • alegeți dacă să împărțiți celulele în coloane sau rânduri.
   • examinați rezultatul în secțiunea Previzualizare și faceți clic pe butonul Split.

gata! Sarcina care a necesitat 3 formule și 5 funcții diferite durează acum doar câteva secunde și un clic pe buton.

împărțiți celulele după șir

această opțiune vă permite să împărțiți șiruri folosind orice combinație de caractere ca delimitator. Din punct de vedere tehnic, împărțiți un șir în părți folosind unul sau mai multe substringuri diferite ca limite ale fiecărei părți.,

De exemplu, să împartă o propoziție prin conjuncțiile „și” și „sau”, extinde Split de siruri de caractere de grup, și introduceți delimitator siruri de caractere, câte unul pe linie:

Ca urmare, sursa expresie este separat la fiecare apariție a fiecărui delimitator:

și aici un alt exemplu din viața reală., Să presupunem că te”am importat-o coloană de date dintr-o sursă externă, care arata ca si cum urmează:

5.1.2016 12:20
5.2.2016 14:50

Acest format nu este convenționale pentru Excel, și, prin urmare, nici unul dintre Data de funcții ar recunoaște orice dată sau elemente. Pentru a împărți ziua, luna, anul, orele și minutele în celule separate, introduceți următoarele caractere în caseta Split by strings:

• punct (.,) pentru a separa zi, lună, și an
• două puncte (:) pentru a separa ore și minute
• Spațiu pentru a separa data și ora

a Lovit butonul Split, și veți obține imediat rezultatul:

Split celule de masca (model)

Separarea unei celule de masca înseamnă divizarea unui șir bazat pe un model.această opțiune este foarte utilă atunci când trebuie să împărțiți o listă de șiruri omogene în unele elemente sau substringuri., Complicația constă în faptul că textul sursă nu poate fi divizat la fiecare apariție a unui delimitator dat, doar la anumite evenimente specifice. Următorul exemplu va face lucrurile mai ușor de înțeles.

presupunând că aveți o listă de șiruri extrase dintr-un fișier jurnal:

ceea ce doriți este să aveți data și ora, Dacă există, codul de eroare și detaliile excepției în 3 coloane separate., Nu puteți utiliza un spațiu ca delimitator, deoarece există spații între dată și oră, care ar trebui să apară într-o coloană și există spații în textul de excepție, care ar trebui să apară și într-o coloană.soluția împarte un șir cu următoarea mască: * eroare:*Excepție: *

unde asteriscul ( * ) reprezintă orice număr de caractere.

punctele (:) sunt incluse în delimitatori, deoarece nu dorim ca acestea să apară în celulele rezultate.,

Și acum, extinde Split de masca secțiune pe Split Text, panou, tip masca în Introduceți delimitatori cutie, și faceți clic pe Scindare:

rezultatul va arata similar cu acesta:

descărcări disponibile

Excel Split Cells formule (.fișier xlsx)
Ultimate Suite 14 zile versiune complet funcțională (.,cum să împărțiți celulele în Excel