Vizionați video - Cum să extrageți numerele din șirul de text în Excel (folosind Formula și VBA)
Nu există nicio funcție încorporată în Excel pentru a extrage numerele dintr-un șir dintr-o celulă (sau invers - eliminați partea numerică și extrageți partea de text dintr-un șir alfanumeric).
Cu toate acestea, acest lucru se poate face folosind un cocktail de funcții Excel sau un cod simplu VBA.
Permiteți-mi să vă arăt mai întâi despre ce vorbesc.
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să extrageți numerele din șir (așa cum se arată mai jos):
Metoda pe care o alegeți va depinde și de versiunea de Excel pe care o utilizați:
- Pentru versiunile anterioare Excel 2016, trebuie să utilizați formule puțin mai lungi
- Pentru Excel 2016, puteți utiliza funcția TEXTJOIN nou introdusă
- Metoda VBA poate fi utilizată în toate versiunile de Excel
Faceți clic aici pentru a descărca fișierul de exemplu
Extrageți numerele din șir în Excel (Formula pentru Excel 2016)
Această formulă va funcționa numai în Excel 2016, deoarece folosește funcția TEXTJOIN nou introdusă.
De asemenea, această formulă poate extrage numerele care se află la începutul, sfârșitul sau mijlocul șirului de text.
Rețineți că formula TEXTJOIN acoperită în această secțiune vă va oferi toate caracterele numerice împreună. De exemplu, dacă textul este „Prețul a 10 bilete este de 200 USD”, vă va oferi 10200 ca rezultat.Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să extrageți numerele din șirurile din fiecare celulă:
Mai jos este formula care vă va oferi o parte numerică dintr-un șir în Excel.
= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), ""))
Aceasta este o formulă matrice, deci trebuie să utilizați „Control + Shift + Enter‘În loc să folosiți Enter.
În cazul în care nu există numere în șirul de text, această formulă ar returna un gol (șir gol).
Cum funcționează această formulă?
Permiteți-mi să rup această formulă și să încerc să explic cum funcționează:
- ROW (INDIRECT („1:” & LEN (A2))) - această parte a formulei ar da o serie de numere începând de la unul. Funcția LEN din formulă returnează numărul total de caractere din șir. În cazul „Costul este de 100 USD”, acesta va returna 19. Formulele vor deveni astfel ROW (INDIRECT („1:19”). Funcția ROW va returna apoi o serie de numere - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, ROW (INDIRECT („1:” & LEN (A2))), 1) * 1) - Această parte a formulei va returna o matrice de #VALUE! erori sau numere bazate pe șir. Toate caracterele text din șir devin #VALUE! erorile și toate valorile numerice rămân așa cum sunt. Acest lucru se întâmplă deoarece am înmulțit funcția MID cu 1.
- IFERROR ((MID (A2, ROW (INDIRECT („1:” & LEN (A2))), 1) * 1), ””) - Când se utilizează funcția IFERROR, ar elimina tot #VALUE! erori și ar rămâne doar numerele. Rezultatul acestei părți ar arăta astfel - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; 1; 0; 0}
- = TEXTJOIN („”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT („1:” & LEN (A2))), 1) * 1), ””)) - Funcția TEXTJOIN combină acum caracterele șirului care rămâne (care sunt doar numerele) și ignoră șirul gol.
Descărcați fișierul de exemplu
De asemenea, puteți utiliza aceeași logică pentru a extrage partea de text dintr-un șir alfanumeric. Mai jos este formula care ar obține partea de text din șir:
= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1) * 1), MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1), ""))
O modificare minoră în această formulă este că funcția IF este utilizată pentru a verifica dacă matricea pe care o obținem din funcția MID sunt sau nu erori. Dacă este o eroare, păstrează valoarea pe care o înlocuiește cu un gol.
Apoi TEXTJOIN este folosit pentru a combina toate caracterele textului.
Prudență: Deși această formulă funcționează excelent, folosește o funcție volatilă (funcția INDIRECTĂ). Aceasta înseamnă că, în cazul în care utilizați acest lucru cu un set de date imens, poate dura ceva timp pentru a vă oferi rezultatele. Cel mai bine este să creați o copie de rezervă înainte de a utiliza această formulă în Excel.Extrageți numerele din șir în Excel (pentru Excel 2013/2010/2007)
Dacă aveți Excel 2013. 2010. sau 2007, nu puteți utiliza formula TEXTJOIN, deci va trebui să utilizați o formulă complicată pentru a face acest lucru.
Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să extrageți toate numerele din șir în fiecare celulă.
Formula de mai jos va face acest lucru:
= IF (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW) (INDIRECT (" $ 1: $ "& LEN (A2))), 1)) * ROW (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2)))) + 1,1) * 10 ROW (INDIRECT („$ 1: $” & LEN (A2))) / 10), „”)
În cazul în care nu există un număr în șirul de text, această formulă va reveni necompletată (șir gol).
Deși aceasta este o formulă matrice, tu nu au nevoie pentru a utiliza „Control-Shift-Enter” pentru a utiliza acest lucru. O simplă introducere funcționează pentru această formulă.
Meritul pentru această formulă revine uimitorului forum Mr. Excel.
Din nou, această formulă va extrage toate numerele din șir indiferent de poziție. De exemplu, dacă textul este „Prețul a 10 bilete este de 200 USD”, vă va oferi 10200 ca rezultat.
Prudență: Deși această formulă funcționează excelent, folosește o funcție volatilă (funcția INDIRECTĂ). Aceasta înseamnă că, în cazul în care utilizați acest lucru cu un set de date imens, poate dura ceva timp pentru a vă oferi rezultatele. Cel mai bine este să creați o copie de rezervă înainte de a utiliza această formulă în Excel.Separați textul și numerele în Excel folosind VBA
Dacă separarea textului și a numerelor (sau extragerea numerelor din text) este ceva ce trebuie să faceți des, puteți utiliza și metoda VBA.
Tot ce trebuie să faceți este să utilizați un cod VBA simplu pentru a crea o funcție personalizată definită de utilizator (UDF) în Excel și apoi, în loc să utilizați formule lungi și complicate, utilizați acea formulă VBA.
Permiteți-mi să vă arăt cum să creați două formule în VBA - una pentru extragerea numerelor și una pentru extragerea textului dintr-un șir.
Extrageți numerele din șir în Excel (folosind VBA)
În această parte, vă voi arăta cum să creați funcția personalizată pentru a obține doar partea numerică dintr-un șir.
Mai jos este codul VBA pe care îl vom folosi pentru a crea această funcție personalizată:
Funcția GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Funcția de finalizare a rezultatului
Iată pașii pentru a crea această funcție și apoi utilizați-o în foaia de lucru:
- Accesați fila Dezvoltator.
- Faceți clic pe Visual Basic (Puteți utiliza și comanda rapidă de la tastatură ALT + F11)
- În backend-ul VB Editor care se deschide, faceți clic dreapta pe oricare dintre obiectele din registrul de lucru.
- Accesați Insert și faceți clic pe Module. Aceasta va insera obiectul modulului pentru registrul de lucru.
- În fereastra Codului modulului, copiați și lipiți codul VBA menționat mai sus.
- Închideți Editorul VB.
Acum, veți putea utiliza funcția GetText din foaia de lucru. Întrucât am făcut toate greutățile din cod în sine, tot ce trebuie să faceți este să folosiți formula = GetNumeric (A2).
Acest lucru vă va oferi instantaneu doar partea numerică a șirului.
Rețineți că, din moment ce registrul de lucru conține acum cod VBA, trebuie să îl salvați cu extensia .xls sau .xlsm.
Descărcați fișierul de exemplu
În cazul în care trebuie să utilizați frecvent această formulă, puteți să o salvați și în registrul personal de lucru Macro. Acest lucru vă va permite să utilizați această formulă personalizată în oricare dintre registrele de lucru Excel cu care lucrați.
Extrageți text dintr-un șir în Excel (folosind VBA)
În această parte, vă voi arăta cum să creați funcția personalizată pentru a obține doar partea de text dintr-un șir.
Mai jos este codul VBA pe care îl vom folosi pentru a crea această funcție personalizată:
Funcția GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) În continuare i GetText = Funcția de finalizare a rezultatului
Iată pașii pentru a crea această funcție și apoi utilizați-o în foaia de lucru:
- Accesați fila Dezvoltator.
- Faceți clic pe Visual Basic (Puteți utiliza și comanda rapidă de la tastatură ALT + F11)
- În backend-ul VB Editor care se deschide, faceți clic dreapta pe oricare dintre obiectele din registrul de lucru.
- Accesați Insert și faceți clic pe Module. Aceasta va insera obiectul modulului pentru registrul de lucru.
- Dacă aveți deja un modul, faceți dublu clic pe el (nu este nevoie să introduceți unul nou dacă îl aveți deja).
- Dacă aveți deja un modul, faceți dublu clic pe el (nu este nevoie să introduceți unul nou dacă îl aveți deja).
- În fereastra Codului modulului, copiați și lipiți codul VBA menționat mai sus.
- Închideți Editorul VB.
Acum, veți putea utiliza funcția GetNumeric din foaia de lucru. Deoarece am făcut toate lucrurile grele din cod în sine, tot ce trebuie să faceți este să folosiți formula = GetText (A2).
Acest lucru vă va oferi instantaneu doar partea numerică a șirului.
Rețineți că, din moment ce registrul de lucru conține acum cod VBA, trebuie să îl salvați cu extensia .xls sau .xlsm.
În cazul în care trebuie să utilizați frecvent această formulă, puteți, de asemenea, să o salvați în registrul personal de lucru Macro. Acest lucru vă va permite să utilizați această formulă personalizată în oricare dintre registrele de lucru Excel cu care lucrați.
În cazul în care utilizați Excel 2013 sau versiuni anterioare și nu aveți
