Cum se împarte numerele și textul din șir în Excel

Anonim

De multe ori obțin date mixte de pe câmp și server pentru analiză. Aceste date sunt de obicei murdare, având coloana amestecată cu text și număr. În timp ce fac curățarea datelor înainte de analiză, separ numerele și textul în coloane separate. În acest articol, vă voi spune cum puteți face acest lucru.

Scenariu:
Așadar, unul dintre prietenii noștri de pe Exceltip.com a pus această întrebare în secțiunea de comentarii. „Cum pot separa numerele care vin înaintea unui text și la sfârșitul textului folosind formula Excel. De exemplu 125EvenueStreet și LoveYou3000 etc. ”

Pentru extragerea textului, folosim DREAPTA, STÂNGA, MID și alte funcții de text. Trebuie doar să știm numărul de texte de extras. Și aici vom face același lucru mai întâi.
Extrageți numărul și textul dintr-un șir când Numărul este în sfârșitul șirului
Pentru exemplul de mai sus, am pregătit această foaie. În celula A2, am șirul. În celula B2, vreau partea de text și în C2 partea de număr.

Deci, trebuie doar să știm poziția de unde începe numărul. Apoi vom folosi stânga și alte funcții. Deci, pentru a obține poziția primului număr, utilizăm mai jos formula generică:
Formula generică pentru a obține poziția primului număr în șir:

= MIN (CĂUTARE ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Aceasta va reveni la poziția primului număr.
Pentru exemplul de mai sus scrieți această formulă în orice celulă.

= MIN (CĂUTARE ({0,1,2,3,4,5,6,7,8,9}, A5 și „0123456789”))

Extrageți partea de text

Se va întoarce 15 deoarece primul număr care se găsește este pe poziția 15 în Text. O să explic mai târziu.

Acum, pentru a obține text, de la stânga trebuie doar să obținem 15-1 caractere din șir. Deci vom folosi
Funcția STÂNGA pentru extragerea textului.
Formula pentru a extrage textul din stânga

= STÂNGA (A5, MIN (CAUTARE ({0,1,2,3,4,5,6,7,8,9}, A5 și "0123456789")) - 1)


Aici tocmai am scăzut 1 din orice număr returnat de MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 și "0123456789")).
Extras numărul de parte

Acum, pentru a obține numere, trebuie doar să obținem caractere numerice din primul număr găsit. Deci calculăm lungimea totală a şir și scădeți poziția primului număr găsit și adăugați 1 la ea. Simplu. Da, sună doar complex, este simplu.
Formula pentru a extrage numerele din dreapta

= DREAPTA (A5, LEN (A5) -MIN (CĂUTARE ({0,1,2,3,4,5,6,7,8,9}, A5 și "0123456789")) + 1)

Aici tocmai am obținut lungimea totală a șirului folosind funcția LEN și apoi am scăzut poziția primului număr găsit și apoi i-am adăugat 1. Acest lucru ne oferă un număr total de numere. Aflați mai multe aici despre extragerea textului folosind funcțiile STÂNGA și DREAPTA din Excel.

Deci, partea funcției STÂNGA și DREAPTA este simplă. Partea dificilă este partea MIN și SEARCH care ne oferă poziția primului număr găsit. Să înțelegem asta.
Cum functioneaza
Știm cum funcționează funcțiile STÂNGA și DREAPTA. Vom explora partea principală a acestei formule care obține poziția primului număr găsit și care este: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Funcția SEARCH returnează poziția unui text în șir. Funcția SEARCH („text”, „șir”) acceptă două argumente, mai întâi textul pe care doriți să îl căutați, în al doilea rând șirul în care doriți să căutați.

    • Aici în CĂUTARE, la poziția textului avem o serie de numere de la 0 la 9. Și la poziția șirului avem șir care este concatenat cu „0123456789” folosind & operator. De ce? O să-ți spun.
    • Fiecare element din matrice {0,1,2,3,4,5,6,7,8,9} va fi căutat într-un șir dat și își va întoarce poziția în șir de formă matrice la același index din matrice.
    • Dacă nu se găsește nicio valoare, aceasta va provoca o eroare. Prin urmare, toate formulele vor duce la o eroare. Pentru a evita acest lucru, am concatenat numerele „0123456789” în text. Astfel încât să găsească întotdeauna fiecare număr în șir. Aceste numere sunt în cele din urmă, prin urmare nu vor cauza nicio problemă.
    • Acum, funcția MIN returnează cea mai mică valoare din matrice returnată de funcția SEARCH. Această valoare cea mai mică va fi primul număr din șir. Acum, folosind această funcție NUMĂR și STÂNGA și DREAPTA, putem împărți părțile de text și șir.

Să examinăm exemplul nostru. În A5 avem șirul care are numele străzii și numărul casei. Trebuie să le separăm în diferite celule.
Mai întâi să vedem cum am obținut poziția noastră de prim număr în șir.

    • MIN (CĂUTARE ({0,1,2,3,4,5,6,7,8,9}, A5 și "0123456789")): aceasta se va traduce în MIN (CĂUTARE ({0,1,2,3, 4,5,6,7,8,9} ”Monta270123456789”))

Acum, după cum am explicat, căutarea va căuta fiecare număr din matrice {0,1,2,3,4,5,6,7,8,9} în Monta270123456789 și își va întoarce poziția într-o formă matrice. Matricea returnată va fi {8,9,6,11,12,13,14,7,16,17}. Cum?
0 va fi căutat în șir. Se găsește în poziția 8. Prin urmare, primul nostru element este 8. Rețineți că textul nostru original are doar 7 caractere. Ia-l. 0 nu face parte din Monta27.
Următorul 1 va fi căutat în șir și, de asemenea, nu face parte din șirul original și obținem poziția 9.
Următoarele 2 vor fi căutate. Deoarece este partea șirului original, obținem indexul său ca 6.
În mod similar, fiecare element se găsește într-o anumită poziție.

    • Acum această matrice este trecută la funcția MIN ca MIN ({8,9,6,11,12,13,14,7,16,17}). MIN returnează 6, care este poziția primului număr găsit în textul original.
      Și povestea de după aceasta este destul de simplă. Folosim acest număr pentru a extrage text și numere folosind funcțiile STÂNGA și DREAPTA.

Extrageți numărul și textul dintr-un șir când Numărul este la începutul șirului
În exemplul de mai sus, Number se afla la sfârșitul șirului. Cum extragem numărul și textul când numărul este la început.

Am pregătit un tabel similar cu cel de mai sus. Doar are număr la început.

Aici vom folosi o tehnică diferită. Vom număra lungimea numerelor (care este 2 aici) și vom extrage acel număr de caractere din stânga șirului.
Deci metoda este = STÂNGA (șir, număr de numere)
Pentru a număra numărul de caractere aceasta este formula.
Formula generică pentru a număra numărul de numere:

= SUM (LEN (șir) -LEN (SUBSTITUT (șir, {"0", "1", "2", "3", "4", "5", "6", "7", "8" , "9"}, ""))

Aici,

      • Funcția SUBSTITUTE va înlocui fiecare număr găsit cu „” (necompletat). Dacă un număr este găsit înlocuit și un șir nou va fi adăugat la matrice, alt șir original înțelept va fi adăugat la matrice. În acest fel, vom avea o serie de 10 șiruri.
      • Acum funcția LEN va returna lungimea caracterelor dintr-o serie de șiruri.
      • Apoi, din lungimea șirurilor originale, vom scădea lungimea fiecărui șir returnat de funcția SUBSTITUTE. Aceasta va returna din nou o matrice.
      • Acum SUM va adăuga toate aceste numere. Acesta este numărul de numere în șir.

Extrageți partea de număr din șir

Acum, deoarece știm lungimea numerelor din şir, vom înlocui această funcție în STÂNGA.
Deoarece avem șirul nostru un A11 nostru:

Formula pentru a extrage numerele din STÂNGA

= STÂNGA (A11, SUM (LEN (A11) -LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, "")))))


Extrageți partea de text din șir

Deoarece cunoaștem numărul de numere, îl putem scădea din lungimea totală a șirului pentru a obține alfabete numerice în șir și apoi folosim funcția dreaptă pentru a extrage acel număr de caractere din dreapta șirului.

Formula pentru a extrage textul din dreapta

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


Cum functioneaza
Partea principală din ambele formule este SUM (LEN (A11) -LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) care calculează prima ocurență a unui număr. Doar după ce am găsit acest lucru, putem diviza textul și numărul folosind funcția STÂNGA. Deci, să înțelegem acest lucru.

      • SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Această parte returnează o serie de șiruri în A11 după înlocuirea acestor numere cu nimic / gol („”). Pentru 27Monta se va returna {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Acum partea SUBSTITUTE este înfășurată de funcția LEN. Această lungime de returnare a textelor în matrice returnată de funcția SUBSTITUTE. În consecință, vom avea {7,7,6,7,7,7,7,6,7,7,7}.
      • LEN (A11) -LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," ")): Aici scădem fiecare număr returnat de partea de mai sus din lungimea șirului real. Lungimea textului original este 7. Prin urmare, vom avea {7-7,7-7,7-6,….}. În cele din urmă vom avea {0,0,1,0,0,0,0,1,0,0}.
      • SUM (LEN (A11) -LEN (SUBSTITUT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Aici am folosit SUM pentru a însuma matricea returnată de partea de mai sus a funcției. Aceasta va da 2. Care este numărul de numere din șir.

Acum folosind acest lucru putem extrage textele și numărul și le putem împărți în diferite celule. Această metodă va funcționa cu ambele tipuri de text, când numărul este la început și când este la sfârșit. Trebuie doar să utilizați funcțiile STÂNGA și DREAPTA în mod corespunzător.
Utilizați funcția SplitNumText Pentru a împărți numerele și textele dintr-un șir
Metodele de mai sus sunt puțin complexe și nu sunt utile atunci când textul și numerele sunt amestecate. Pentru a împărți textul și numerele, utilizați această funcție definită de utilizator.

Sintaxă:

= SplitNumText (șir, op)

Şir: Șirul pe care doriți să-l împărțiți.
Op: acesta este boolean. Treceți 0 sau fals pentru a obține partea de text. Pentru partea numerică, treceți Adevărat sau orice număr mai mare decât 0.
De exemplu, dacă șirul este în A20 atunci,
Formula pentru extragerea numerelor din șir este:

= SplitNumText (A20,1)

Și
Formula pentru extragerea textului din șir este:

= SplitNumText (A20,0)

Copiați codul de mai jos în modulul VBA pentru a face ca formula de mai sus să funcționeze.

Funcția SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Then num = num & Mid (str, i , 1) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function

Acest cod verifică pur și simplu fiecare caracter din șir, dacă este un număr sau nu. Dacă este un număr, atunci este stocat în variabila num, altfel în variabila txt. Dacă utilizatorul trece true pentru op atunci num este returnat altfel se returnează txt.

Acesta este cel mai bun mod de a împărți numărul și textul dintr-un șir, după părerea mea.
Puteți descărca registrul de lucru aici, dacă doriți.

Deci, băieți, acestea sunt modalitățile de a împărți textul și numerele în celule diferite. Spuneți-mi dacă aveți îndoieli sau soluții mai bune în secțiunea de comentarii de mai jos. Este întotdeauna distractiv să interacționezi cu băieți.

Faceți clic pe linkul de mai jos pentru a descărca fișierul de lucru:

Împărțiți numărul și textul dintr-o celulă

Articole populare:
50 Comenzi rapide Excel pentru a vă crește productivitatea
Funcția VLOOKUP în Excel
COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel