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 cu jucării noi, mă refer la funcții.
Excel 2019 și 365 introduc câteva funcții noi (TEXTJOIN și SEQUENCE) care pot ușura sarcina de a elimina caractere numerice și de a extrage numai valori nenumerice într-o nouă celulă. Vom folosi formule care ne pot ajuta în acest sens, mai convenabil.
Formula generică
| =TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR(MID(jumbled_text,SECVENŢĂ(NumChars), 1) +0),MID(jumbled_text,SECVENŢĂ(NumChars), 1), ""))) |
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 caracterele numerice și extrageți toate alfabetele și caracterele non numerice.
Deci, aici avem un text alfanumeric. Acest text conține câteva numere și câteva caractere nenumerice. Trebuie să scap de caractere numerice și să obțin numai alfabete și alte valori ale caracterelor, în coloana D.
Nu mă aștept ca numărul total de caractere din textul amestecat să fie mai mare de 100. Deci, valoarea NumChars este 100 aici. Puteți crește sau micșora acest număr, dacă aveți nevoie.
Aplicați aici formula generică de mai sus pentru a elimina caracterele numerice.
| =TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR(MID(B3,SECVENŢĂ(100),1)+0),MID(B3,SECVENŢĂ(100),1),""))) |
Când apăsați butonul Enter, veți elimina toate caracterele numerice și rămân doar valorile alfabetice. Trageți în jos această formulă pentru a elimina numerele din șir din toate celulele din coloana B.
Cum functioneazã?
Mai întâi să vedem cum se rezolvă această formulă pas cu pas.
| 1-> TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR(MID(B3,SECVENŢĂ(100),1)+0),MID(B3,SECVENŢĂ(100),1),""))) |
| 2-> TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR(MID(„This1 is… site”,{1,2,3,… 100},1)+0),MID(B3,SECVENŢĂ(100),1),""))) |
| 3-> TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MID(B3,SECVENŢĂ(100),1),""))) |
| 4-> TEXTJOIN("",ADEVĂRAT,DACĂ(ISERROR({#VALUE!; # VALUE!; # VALUE!; # VALUE!; 1 …; #VALUE!}),MID(B3,SECVENŢĂ(100),1),""))) |
| 5-> TEXTJOIN("",ADEVĂRAT,DACĂ({TRUE; TRUE; TRUE; TRUE; FALSE …; TRUE},MID(B3,SECVENŢĂ(100),1),""))) |
| 6-> TEXTJOIN("",ADEVĂRAT,{"Acest";""… .;""}) |
| 7-> „Acesta este site-ul web numărul unu” |
Pentru ușurința citirii, nu am scris întreaga matrice. Am folosit puncte (…) pentru a indica matrice lungi.
După cum puteți vedea, formula începe să se rezolve din interior. La început funcția SEQUENCE este rezolvată. Deoarece am trecut de 100 ca număr de caractere. Returnează o serie de numere începând de la 1 la 100.
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 într-o matrice. Puteți vedea asta la pasul 3. Nu am arătat întreaga matrice, deoarece va ocupa prea mult spațiu. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
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. {#VALUE!; # VALUE!; # VALUE!; # VALUE!; 1 …; #VALUE!}
Această matrice este servită către ISERROR funcţie. După cum știți, funcția ISERROR returnează TRUE pentru erori și FALSE pentru valori care nu sunt de eroare. Prin urmare, obținem o serie de ADEVĂRAT și FALS. ADEVĂRAT pentru caractere nenumerice și FALS de numere. {ADEVĂRAT; ADEVĂRAT; ADEVĂRAT; ADEVĂRAT; FALS …; ADEVĂRAT}.
Același lucru se întâmplă cu declarațiile de secțiune TRUE ale IF (MID(B3,SECVENŢĂ(100),1)). Returnează o serie de toate caracterele șirului alfanumeric din B3.
Acum, pentru fiecare instrucțiune TRUE, funcția IF returnează caracterul corespunzător din matricea secțiunii adevărate. Pentru FALSE, IF returnează necompletat (""). Acum veți avea o matrice care nu conține niciun caracter numeric. {"Acest";""… .;""}.
În cele din urmă, această matrice este deservită de TEXTJOIN funcţie. Această funcție unește textele date între ele, ignorând valorile necompletate, cu un delimitator dat. Prin urmare, obținem un șir care nu conține caractere numerice. Acesta este site-ul web numărul unu.
Îmbunătățirea formulei
Formula de mai sus folosește un număr codat tare pentru procesarea numărului de caractere (am luat 100). Dar s-ar putea să doriți ca acesta să fie dinamic. În acest caz, ați ghicit bine, puteți utiliza funcția LEN. Va fi preluat numărul exact de caractere. Deci formula va fi.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE)LEN(jumbled_text), 1) +0), MID (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Aici, funcția LEN va detecta automat numărul exact de caractere din șirul alfanumeric. Acest lucru va scuti de sarcina 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, IF (ISERROR (MID (jumbled_text,RÂND(INDIRECT("1:"&LEN(NumChars))), 1) +0), MID (jumbled_text,RÂND(INDIRECT("1:"&LEN(NumChars))),1),""))) |
INDIRECT va converti textul („1: 100”) în intervalul real și apoi funcția ROW va afișa toate numerele rândurilor de la 1 la 100. (100 este doar un 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.
Cum se elimină caractere nenumerice din celule în Excel 2019: Pentru a elimina caractere nenumerice dintr-un șir alfanumeric din Excel, folosim noua funcție TEXTJOIN. Fâșia de caractere nenumerice dintr-un șir ne poate ajuta să ne curățăm datele pentru o analiză mai bună a datelor. Deci, iată cum o faci
Î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.