Descărcați exemplul de registru de lucru
Acest tutorial va fi vă arată cum să găsiți și să extrageți un număr din interiorul unui șir de text în Excel și Foi de calcul Google.
Găsiți și extrageți numărul din șir
Uneori datele dvs. pot conține numere și text și doriți să extrageți datele numerice.
Dacă partea numerică este în partea dreaptă sau stângă a șirului, este relativ ușor să împărțiți numărul și textul. Cu toate acestea, dacă numerele sunt în interiorul șirului, adică între două șiruri de text, va trebui să utilizați o formulă mult mai complicată (prezentată mai jos).
TEXTJOIN - Extrageți numerele în Excel 2016+
În Excel 2016 a fost introdusă funcția TEXTJOIN, care poate extrage numerele de oriunde din șirul de text. Iată formula:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1) * 1), "")) |
Să vedem cum funcționează această formulă.
Funcțiile ROW, INDIRECT și LEN returnează o serie de numere corespunzătoare fiecărui caracter din șirul alfanumeric. În cazul nostru, „Monday01Day” are 11 caractere, deci funcția ROW va returna apoi matricea {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1) * 1), "")) |
Apoi, funcția MID extrage fiecare caracter din șirul de text, creând un tablou care conține șirul original:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "} * 1)," ")) |
Înmulțind fiecare valoare din matrice cu 1 se va crea o matrice care conține erori Dacă caracterul matricei a fost text:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({# VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; 0; 1; #VALUE!; # VALUE!; # VALUE !}), "")) |
Apoi, funcția IFERROR elimină valorile erorii:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Lăsând doar funcția TEXTJOIN care unește numerele rămase.
Rețineți că formula vă va oferi toate caracterele numerice împreună din șir. De exemplu, dacă șirul dvs. alfanumeric este Monday01Day01, acesta vă va da 0101 ca rezultat.
Extrageți numerele - înainte de Excel 2016
Înainte de Excel 2016, ați putea folosi o metodă mult mai complicată pentru a extrage numerele din text. Puteți utiliza funcția FIND împreună cu funcțiile IFERROR și MIN pentru a determina atât prima poziție a părții numerice, cât și prima poziție a părții text după număr. Apoi extragem pur și simplu partea numerică cu funcția MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a") , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999)) - MIN (IFERROR (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Notă: Aceasta este o formulă Array, trebuie să introduceți formula apăsând CTRL + SHIFT + ENTER, în loc de ENTER.
Să vedem pas cu pas cum funcționează această formulă.
Găsiți numărul pumnului
Putem folosi funcția FIND pentru a localiza poziția inițială a numărului.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Pentru argumentul find_text al funcției FIND, folosim constanta matricei {0,1,2,3,4,5,6,7,8,9}, ceea ce face ca funcția FIND să efectueze o căutare separată pentru fiecare valoare din constanta matricei.
Argumentul within_text al funcției FIND în cazul nostru este Monday01Day, în care 1 poate fi găsit la poziția 8 și 0 la poziția 7, deci matricea noastră de rezultate va fi: {8, # VALUE, # VALUE, # VALUE, # VALUE, # VALUE, # VALUE, #VALUE, # VALUE, 7}.
Folosind funcția IFERROR înlocuim erorile #VALUE cu 999999999. Apoi, căutăm pur și simplu minimul din această matrice și obținem, prin urmare, locul primului număr (7).
Vă rugăm să rețineți că formula de mai sus este o formulă matrice, trebuie să apăsați Ctrl + Shift + Enter pentru a o declanșa.
Găsiți primul caracter text după număr
În mod similar cu localizarea primului număr în cadrul șirului, folosim funcția FIND pentru a determina unde începe textul din nou după ce numărul folosește bine și argumentul start_num al funcției.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", „x”, „y”, „z”}, B3, C3), 999999999)) |
Această formulă funcționează foarte asemănător cu cea precedentă utilizată pentru localizarea primului număr, doar că folosim litere în constanta matricei și, prin urmare, numerele provoacă erori #VALUE. Rețineți că începem să căutăm numai după poziția stabilită pentru primul număr (acesta va fi argumentul start_num) și nu de la începutul șirului.
Nu uitați să apăsați Ctrl + Shift + Enter pentru a utiliza formula de mai sus.
Nu mai rămâne decât să punem toate acestea laolaltă.
Extrageți numărul din două texte
Odată ce avem poziția inițială a părții numerice și începutul părții text după aceea, folosim pur și simplu funcția MID pentru a extrage partea numerică dorită.
| 1 | = MID (B3, C3, D3-C3) |
Altă metodă
Fără a-l explica mai detaliat, puteți utiliza și formula complexă de mai jos pentru a obține numărul din interiorul unui șir.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (- MID (B3, ROW) (INDIRECT ("1:" & LEN (B3))), 1)) * ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3)))) + 1,1) * 10 ROW (INDIRECT ("1:" & LEN (B3)))) / 10) |
Rețineți că această formulă de mai sus vă va da 0 atunci când nu există un număr găsit în șir și că zero-urile din partea de sus vor fi lăsate în afara.
Găsiți și extrageți numărul de la șir la text în Foi de calcul Google
Exemplele de mai sus funcționează în același mod în Foi de calcul Google ca în Excel.
