Am învățat cum să scoatem valorile numerice dintr-o celulă în Excel 2016 și mai vechi. Formulele pe care le-am folosit au fost puțin complexe, dar acum Excel 2019 și 365 sunt în joc.
Excel 2019 și 365 introduc câteva funcții noi care ușurează sarcina de a elimina caractere nenumerice și de a extrage numai valori numerice într-o celulă nouă. Vom folosi formule care ne pot ajuta în acest sens, mai convenabil.
Formula generică
| =TEXTJOIN("",ADEVĂRAT,IFERROR(MID(jumbled_text,SECVENŢĂ(NumChars), 1) +0, "")) |
Jumbled_text: Acesta este textul sursă din care doriți să extrageți toate valorile numerice.
NumChars: Acesta este numărul total de caractere pe care doriți să le procesați. Textul jumbled_text nu trebuie să aibă mai multe caractere decât acest număr (caractere și numerice combinate).
Să vedem un exemplu pentru a clarifica lucrurile.
Exemplu: Eliminați caractere nenumerice și extrageți toate numerele
Deci, aici avem un text confuz. Acest text conține câteva numere și câteva caractere nenumerice. Trebuie să scap de caractere nenumerice și să obțin valori numerice numai în coloana D.
Nu mă aștept ca numărul total de caractere din textul amestecat să fie mai mare de 20. Deci, valoarea NumChars este 20 aici. Puteți crește acest număr dacă aveți nevoie.
Aplicați aici formula generică de mai sus pentru a elimina caracterele nenumerice.
| =TEXTJOIN("",ADEVĂRAT,IFERROR(MID(C3,SECVENŢĂ(20),1)+0,"")) |
Și când apăsați butonul Enter. Veți elimina toate caracterele nenumerice. Trageți în jos această formulă pentru a elimina caracterele din șir din toate celulele din coloana C3.
Cum functioneazã?
Mai întâi să vedem cum se rezolvă această formulă pas cu pas.
| 1-> TEXTJOIN("",ADEVĂRAT,IFERROR(MID(C3,SECVENŢĂ(20),1)+0,"")) |
| 2-> TEXTJOIN("",ADEVĂRAT,IFERROR(MID(„12asw12w123”,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",ADEVĂRAT,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",ADEVĂRAT,IFERROR({1; 2; #VALUE!; # VALUE!; # VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VALUE!; # VALUE!; …; #VALUE!}+0,"")) |
| 5-> TEXTJOIN("",ADEVĂRAT,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
După cum puteți vedea, formula începe să se rezolve din interior. La început funcția SEQUENCE este rezolvată. Din moment ce am trecut de 20. Acesta returnează o serie de numere începând de la 1 la 20.
Această matrice este servită funcției MID ca număr de pornire. Funcția mid merge la fiecare index din șir și împarte fiecare caracter. Puteți vedea asta la pasul 3.
Apoi adăugăm 0 la fiecare caracter. În Excel, dacă încercați să adăugați un număr la caractere nenumerice, rezultă în #VALUE! Eroare. Deci, obținem o serie de numere și #VALUE! Erori. Caracterele nenumerice au dispărut acum.
Următoarea funcție IFERROR înlocuiește toate erorile #VALUE cu "" (necompletat). Acum am rămas cu valori numerice și spații.
În cele din urmă, această matrice este servită funcției TEXTJOIN. Funcția TEXTJOIN le concatenează și obținem un șir care conține doar numere în ea.
Îmbunătățirea formulei
Formula de mai sus a folosit un număr codat tare pentru procesarea numărului de caractere (am luat douăzeci). Dar s-ar putea să doriți ca acesta să fie dinamic. În acest caz, ați ghicit bine, vom folosi funcția LEN. Va fi nevoie de un număr exact de caractere pentru procesare. Deci formula va fi.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Aici, funcția LEN va detecta automat numărul exact de caractere din șirul alfanumeric. Acest lucru va scuti povara determinării numărului maxim de caractere.
Alternativă a funcției SEQUENCE
Dacă nu doriți să utilizați funcția SEQUENCE, puteți utiliza o combinație de funcții ROW și INDIRECT pentru a genera numere secvențiale.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,RÂND(INDIRECT("1:"&LEN(jumbled_text))),1)+0,"")) |
INDIRECT va converti textul („1:20”) în intervalul real și apoi funcția ROW va afișa toate numerele rândurilor de la 1 la 20. (20 este doar de exemplu. Poate fi orice număr).
Deci, băieți, așa puteți să smulgeți caracterele nenumerice dintr-un șir alfanumeric în Excel. Sper că am fost suficient de explicativ și acest articol te-a ajutat. Dacă aveți întrebări cu privire la acest subiect sau la orice alt subiect Excel / VBA. Până atunci continuă să excelezi.
Împărțiți numerele și textul din șir în Excel 2016 și mai vechi: Când nu aveam funcția TEXTJOIN am folosit funcțiile STÂNGA și DREAPTA cu alte funcții pentru a împărți caractere numerice și nenumerere dintr-un șir.
Extrageți textul dintr-un șir în Excel folosind funcția STÂNGA ȘI DREAPTA a Excel: Pentru a elimina textul din excel din șir putem folosi funcțiile STÂNGA și DREAPTA ale excel. Aceste funcții ne ajută să tăiem șirurile dinamic.
Eliminați spațiile inițiale și finale din text în Excel: Spațiile de conducere și de urmărire sunt greu de recunoscut vizual și vă pot încurca datele. Îndepărtarea acestor caractere din șir este o sarcină de bază și cea mai importantă în curățarea datelor. Iată cum o puteți face cu ușurință în Excel.
Eliminați caracterele din dreapta: Pentru a elimina caractere din dreapta unui șir în Excel, folosim funcția STÂNGA. Da, funcția STÂNGA. Funcția STÂNGA păstrează numărul dat de caractere din STÂNGA și elimină totul din dreapta sa.
Eliminați caracterele nedorite din Excel: Pentru a elimina caracterele nedorite dintr-un șir din Excel, folosim funcția SUBSTITUTE. Funcția SUBSTITUTE înlocuiește caracterele date cu un alt caracter dat și produce un nou șir modificat.
Cum se elimină textul în Excel pornind de la o poziție în Excel: Pentru a elimina textul dintr-o poziție de pornire într-un șir, folosim funcția REPLACE din Excel. Această funcție ne ajută să determinăm poziția inițială și numărul de caractere de dezbrăcat.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se utilizează funcția Excel VLOOKUP| Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se folosește Excel Funcția COUNTIF| Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.