Cum să extrageți un număr dintr-un șir în Excel

Cum se face

DeAdam Davidson

Publicat7 iunie 2023

Excel este o foaie de calcul puternică, dar nu poate face minuni. Dacă importați date și aceste date sunt o mizerie, atunci Excel nu va putea face mare lucru cu ele. De exemplu, dacă aveți valori care sunt atașate altor caractere ca parte a unui șir, nu veți putea efectua calcule pe ele decât dacă le extrageți mai întâi.

Din fericire, este posibil să faci exact asta, deși poate fi mai complicat decât ai putea crede la început. Iată cum să extrageți un număr dintr-un șir în Excel.

Cum să extrageți un număr de la sfârșitul unui șir folosind Flash Fill

O modalitate rapidă și ușoară de a extrage un număr de la sfârșitul unui șir este de a folosi Excel Umplere flash caracteristică. Această caracteristică vă permite să umpleți rapid o serie de celule pe baza modelului detectat într-o anumită celulă. Un dezavantaj al acestei metode este că vă cere să faceți singur prima extracție și dacă doriți să extrageți doar un număr dintr-un singur șir, nu va fi de niciun folos.

În cele mai multe cazuri, Flash Fill poate extrage corect numărul de la sfârșitul unui șir, deși nu este infailibil, așa că ar trebui să verificați întotdeauna cu atenție rezultatele. Dacă nu funcționează corect, încercați să completați manual primele două sau trei celule, nu doar pe prima.

Pentru a extrage un număr de la sfârșitul unui șir folosind Flash Fill:

1. Selectați celula de lângă prima celulă care conține șirurile dvs.
   
2. Introduceți numărul care apare la sfârșitul șirului.
   
3. Dacă toate datele dvs. sunt într-o coloană continuă, fără spații, selectați celula direct sub cea în care tocmai ați introdus numărul.
   
4. Dacă datele dvs. au lacune în unele rânduri, selectați prima celulă și toate celulele de sub care doriți să extrageți numere, inclusiv orice rânduri goale.
   
5. presa Ctrl+E pe Windows sau Cmd+E pe Mac pentru a rula funcția Flash Fill.
6. Celulele rămase vor fi umplute automat în funcție de modelul observat de Excel. În cele mai multe cazuri, numerele de la sfârșitul șirului vor fi extrase corect.
   

Cum se extrage un număr de la sfârșitul unui șir folosind o formulă

Un dezavantaj al utilizării metodei Flash Fill este că vă cere să extrageți singur numerele din șirul pentru primul rând. Este posibil să extrageți un număr de la sfârșitul unui șir folosind o formulă, deși formula este destul de complexă.

Pentru a extrage un număr de la sfârșitul unui șir folosind o formulă:

1. Faceți clic în celula în care doriți să apară numărul extras.
2. Introduceți următoarea formulă, înlocuind „B2” cu celula care conține șirul dvs.:

   =VALOARE(DREAPTA(B2, LEN(B2)-MAX(IF(ISNUMĂR(MID(B2,RÂND(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, RÂND(INDIRECT(" 1:"&LEN(B2))),0))))

3. presa introduce.
4. Numărul trebuie extras din șir.
5. Pentru a aplica formula oricăror celule rămase, faceți clic pe mânerul de glisare din colțul din dreapta jos al celulei.
   
6. Trageți în jos peste celulele în care doriți să fie replicată formula.
   
7. Eliberați mouse-ul și formula va fi copiată în celulele selectate.
8. Aceste celule ar trebui să conțină acum numerele extrase.
   
9. Orice celulă fără un șir din care să fie extras va returna o eroare. Dacă doriți să returnați o celulă goală, puteți utiliza formula:

   =DACĂ EROARE(VALOARE(DREPTA(B2, LEN(B2))-MAX(DACĂ(SE NUMĂR(MIJUNEA(B2, RÂND(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, RÂND(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Orice celulă goală va returna acum un spațiu liber.
    

Poate vă întrebați ce face această formulă. Pe scurt, generează o matrice care are aceeași lungime cu șirul. Plasează fiecare dintre caracterele din șir în această matrice în ordine. Apoi evaluează fiecare dintre acele personaje pentru a vedea dacă sunt un număr sau nu. Orice caractere care sunt numere sunt convertite la zero. Orice caractere care nu sunt numere sunt convertite într-un număr reprezentând poziția sa în șir.

De exemplu, AB5HG23 s-ar converti la 1, 2, 0, 4, 5, 0, 0. Apoi caută cea mai mare valoare din această listă, care este poziția ultimului caracter care nu este un număr. În cele din urmă, returnează toate caracterele după acest punct, care sunt numerele de la sfârșitul șirului. Funcția VALUE îl convertește apoi înapoi într-un număr. Simplu, nu?

Cum se extrage un număr de la începutul unui șir folosind Flash Fill

De asemenea, puteți utiliza Flash Fill pentru a extrage un număr de la începutul unui șir completând singur primul exemplu și permițând Excel să oprească modelul.

Pentru a extrage un număr de la începutul unui șir folosind Flash Fill:

1. Selectați celula din dreapta primului șir care are un număr pe care doriți să îl extrageți.
2. Introduceți numărul care apare la începutul șirului.
   
3. Dacă toate datele dvs. sunt într-o coloană continuă fără spații, selectați celula direct de mai jos.
   
4. Dacă datele dvs. au lacune, selectați prima celulă și toate celulele de sub care doriți să extrageți numere, inclusiv orice rânduri goale.
   
5. presa Ctrl+E pe Windows sau Cmd+E pe Mac pentru a porni Flash Fill.
6. Celulele pe care le-ați selectat ar trebui să arate acum numerele extrase.
   

Cum se extrage un număr de la începutul unui șir folosind o formulă

De asemenea, puteți utiliza o formulă pentru a extrage un număr de la începutul unui șir. Aceasta funcționează într-un mod similar cu formula din secțiunea anterioară, dar găsește poziția primului caracter nenumeric și returnează toate caracterele de dinainte.

Pentru a extrage un număr de la începutul unui șir folosind o formulă:

1. Selectați celula în care doriți să apară numărul extras.
2. Introduceți următoarea formulă, înlocuind „B18” cu celula care conține șirul dvs.:

   =VALOARE(STÂNGA(B18,POTRIVIRE(FALSE, ESTE NUMĂR)(MIJUNEA(B18,RÂND(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. presa introduce iar numărul va fi extras.
   
4. Puteți utilizați completarea automată în Excel pentru a copia formula în orice alte celule de care aveți nevoie.
5. Dacă doriți ca orice rânduri goale să returneze un rezultat gol, utilizați în schimb următoarea formulă:

   =DACĂ EROARE(VALOARE(STÂNGA(B18,POTRIVIRE(FALSE, ESTE NUMĂR(MIJUNEA(B18,RÂND(INDIRECT(„1:”&LEN(B18)+1)),1)*1),0)-1)),” ")

Cum să extrageți numere de oriunde dintr-un șir folosind Flash Fill

După cum sa menționat anterior, Flash Fill poate să nu funcționeze dacă prima celulă nu se potrivește cu formatul unora dintre celelalte celule. În acest caz, poate fi necesar să calculați manual două sau trei celule înainte ca Flash Fill să vă ofere rezultatul de care aveți nevoie.

Pentru a extrage numere de oriunde dintr-un șir folosind Flash Fill:

1. În celula de lângă primul șir, introduceți manual numerele care se află în șir.
   
2. Dacă toate datele dvs. sunt într-o coloană continuă, selectați celula direct de mai jos.
   
3. Dacă datele dvs. au anumite lacune, selectați prima celulă și toate celulele de sub care doriți să extrageți numere, inclusiv orice rânduri goale.
   
4. presa Ctrl+E pe Windows sau Cmd+E pe Mac pentru a porni Flash Fill.
5. Celulele ar trebui să se umple acum cu numerele extrase.
   

Cum să extrageți numere de oriunde dintr-un șir folosind o formulă

Puteți folosi o formulă pentru a extrage toate numerele dintr-un șir, indiferent unde apar.

Pentru a extrage numere de oriunde dintr-un șir folosind o formulă:

1. Selectați celula în care doriți să apară numărul extras.
2. Introduceți următoarea formulă, înlocuind „B9” cu celula care conține șirul dvs.:

   =SUMĂPRODUS(MIJUNE(0&B9, MARE(INDEX(ISNUMĂR(--MIJUNEA(B9,RÂND(INDIRECT(„1:”&LEN(B9))),1))*RÂND(INDIRECT(„1:”&LEN(B9)) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Formula ta ar trebui să extragă toate numerele din șir.
   
4. Dacă doriți ca orice celule goale să returneze o celulă goală, mai degrabă decât zero, utilizați această formulă:

   =DACA EROARE(SUMAPRODUS(MIJUNEA(0&B9,MARE(INDEX(ISNUMBER(--MIJUNEA(B9,RÂND(INDIRECT("1:"&LEN(B9))),1))*RÂND(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Puteți utilizați completarea automată pentru a aplica formula oricăror alte celule din foaia de calcul.
6. Orice șir care nu conține niciun număr va returna zero. Puteți învăța cum convertiți un zero într-o liniuță în Excel dacă vrei să schimbi asta.

Extindeți-vă abilitățile Excel

Învățarea cum să extrageți un număr dintr-un șir în Excel vă poate ajuta să extrageți rapid datele de care aveți nevoie dintr-o listă de șiruri. Deși formulele necesare pot fi complexe, nu trebuie să înțelegeți exact cum funcționează; doar că ei fac!

Există trucuri Excel mai utile pe care le puteți învăța pentru a vă duce abilitățile la nivelul următor. Poti sa inveti cum se numără celulele cu text în ele, sau cum să calculați vârsta cuiva de la data nașterii. Poți chiar creați un generator de numere aleatorii în Excel.