Kuidas Excelis stringist numbrit eraldada

Kuidas

KõrvalAdam Davidson

Avaldatud7. juuni 2023

Excel on võimas arvutustabel, kuid see ei suuda imesid korda saata. Kui impordite andmeid ja need andmed on segaduses, ei saa Excel nendega palju teha. Näiteks kui teil on väärtusi, mis on stringi osana lisatud teistele tähemärkidele, ei saa te nendega arvutusi teha, kui te neid esmalt ei eralda.

Õnneks on seda võimalik teha, kuigi see võib olla keerulisem, kui esmapilgul arvata võiks. Siit saate teada, kuidas Excelis stringist numbrit ekstraheerida.

Kuidas eraldada stringi lõpust arv Flash Filli abil

Kiire ja lihtne viis stringi lõpust numbri eraldamiseks on Exceli kasutamine Välgu täitmine tunnusjoon. See funktsioon võimaldab teil ühes konkreetses lahtris tuvastatud mustri alusel kiiresti täita lahtrivahemikku. Selle meetodi üks negatiivne külg on see, et see nõuab, et teeksite esimese väljavõtte ise ja kui soovite eraldada ainult numbri ühest stringist, pole sellest kasu.

Enamikul juhtudel suudab Flash Fill numbri stringi lõpust õigesti eraldada, kuigi see pole eksimatu, seega peaksite tulemusi alati hoolikalt kontrollima. Kui see ei tööta õigesti, proovige esimesed kaks või kolm lahtrit käsitsi täita, mitte ainult esimene.

Arvu eraldamiseks stringi lõpust Flash Filli abil tehke järgmist.

1. Valige lahter esimese teie stringe sisaldava lahtri kõrval.
   
2. Sisestage stringi lõpus kuvatav number.
   
3. Kui kõik teie andmed on pidevas veerus ilma lünkadeta, valige lahter otse selle lahtri all, kuhu just numbri sisestasite.
   
4. Kui teie andmetel on mõnel real lünki, valige esimene lahter ja kõik selle all olevad lahtrid, millest soovite numbreid eraldada, sealhulgas kõik tühjad read.
   
5. Vajutage Ctrl+E Windowsis või Cmd+E Macis, et käivitada funktsioon Flash Fill.
6. Teie ülejäänud lahtrid täidetakse automaatselt Exceli tuvastatud mustri alusel. Enamikul juhtudel ekstraheeritakse stringi lõpus olevad numbrid õigesti.
   

Kuidas eraldada valemi abil stringi lõpust arv

Üks Flash Fill meetodi kasutamise negatiivne külg on see, et see nõuab esimese rea stringist numbrite eraldamist ise. Valemi abil on võimalik stringi lõpust arv välja võtta, kuigi valem on üsna keeruline.

Arvu eraldamiseks stringi lõpust valemi abil:

1. Klõpsake lahtril, kuhu soovite eraldatud numbri kuvada.
2. Sisestage järgmine valem, asendades "B2" teie stringi sisaldava lahtriga:

   =VÄÄRTUS(PAREM(B2,PIIKUS(B2)-MAX(IF(ISNUMBER(MID(B2,RIDA)(INDIRECT("1:"&PIN(B2))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0)))

3. Vajutage Sisenema.
4. Arv tuleks stringist eraldada.
5. Valemi rakendamiseks ülejäänud lahtritele klõpsake lahtri paremas alanurgas olevat lohistamispidet.
   
6. Lohistage alla lahtrite kohal, kus soovite valemit kopeerida.
   
7. Vabastage hiir ja valem kopeeritakse valitud lahtritesse.
8. Need lahtrid peaksid nüüd sisaldama teie eraldatud numbreid.
   
9. Kõik lahtrid, millel pole väljavõtmist stringi, tagastavad veateate. Kui soovite selle asemel tagastada tühja lahtri, võite selle asemel kasutada valemit:

   =IFERROR(VÄÄRTUS(PAREM(B2,PIIKUS(B2)-MAX(IF(ISNUMBER(MID(B2,RIDA)(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Kõik tühjad lahtrid tagastavad nüüd tühja ruumi.
    

Võite küsida, mida see valem teeb. Lühidalt, see genereerib massiivi, mis on stringiga sama pikk. See asetab kõik stringi märgid sellesse massiivi järjekorras. Seejärel hindab see kõiki neid märke, et näha, kas need on arv või mitte. Kõik numbriteks olevad märgid teisendatakse nulliks. Kõik märgid, mis ei ole numbrid, teisendatakse numbriteks, mis tähistavad selle asukohta stringis.

Näiteks AB5HG23 teisendaks 1, 2, 0, 4, 5, 0, 0. Seejärel otsib see loendist suurimat väärtust, mis on viimase märgi asukoht, mis ei ole arv. Lõpuks tagastab see kõik märgid pärast seda punkti, mis on stringi lõpus olevad numbrid. Funktsioon VALUE teisendab selle seejärel tagasi arvuks. Lihtne, eks?

Kuidas eraldada arv stringi algusest Flash Filli abil

Võite kasutada ka Flash-täitmist, et eraldada arv stringi algusest, täites esimese näite ise ja lubades Excelil mustri peatada.

Arvu eraldamiseks stringi algusest Flash Filli abil tehke järgmist.

1. Valige esimesest stringist paremal olev lahter, millel on number, mida soovite ekstraktida.
2. Sisestage stringi alguses kuvatav number.
   
3. Kui kõik teie andmed on pidevas veerus ilma lünkadeta, valige otse allolev lahter.
   
4. Kui teie andmetes on lünki, valige esimene lahter ja kõik selle all olevad lahtrid, millest soovite numbreid eraldada, sealhulgas kõik tühjad read.
   
5. Vajutage Ctrl+E Windowsis või Cmd+E Macis, et käivitada Flash Fill.
6. Valitud lahtrid peaksid nüüd näitama eraldatud numbreid.
   

Kuidas eraldada arv stringi algusest valemi abil

Võite kasutada ka valemit, et eraldada arv stringi algusest. See toimib sarnaselt eelmise jaotise valemiga, kuid leiab esimese mittenumbrilise märgi asukoha ja tagastab kõik märgid enne seda.

Arvu eraldamiseks stringi algusest valemi abil:

1. Valige lahter, kuhu soovite eraldatud numbri kuvada.
2. Sisestage järgmine valem, asendades "B18" teie stringi sisaldava lahtriga:

   =VÄÄRTUS(VASAKU(B18,VASTANE(VÄÄR, ISNUMBER(KESK(B18,RIDA(KADUNE("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Vajutage Sisenema ja number ekstraheeritakse.
   
4. Sa saad kasutage Excelis automaattäitmist valemi kopeerimiseks muudesse vajalikesse lahtritesse.
5. Kui soovite, et tühjad read tagastaksid tühja tulemuse, kasutage selle asemel järgmist valemit:

   =VIRG(VÄÄRTUS(VASAKU(B18,VASTANE(VÄÄR, ISARV(KESK(B18,RIDA(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1)" ")

Kuidas eraldada numbreid ükskõik kust stringist, kasutades välktäidet

Nagu varem mainitud, ei pruugi Flash Fill töötada, kui esimene lahter ei ühti mõne teise lahtri vorminguga. Sel juhul peate võib-olla käsitsi arvutama kaks või kolm lahtrit, enne kui Flash Fill annab teile vajaliku tulemuse.

Numbrite eraldamiseks suvalisest stringist Flash Filli abil tehke järgmist.

1. Sisestage stringis olevad numbrid käsitsi esimese stringi kõrval olevasse lahtrisse.
   
2. Kui kõik teie andmed on pidevas veerus, valige otse allolev lahter.
   
3. Kui teie andmetes on lünki, valige esimene lahter ja kõik selle all olevad lahtrid, millest soovite numbreid eraldada, sealhulgas kõik tühjad read.
   
4. Vajutage Ctrl+E Windowsis või Cmd+E Macis, et käivitada Flash Fill.
5. Lahtrid peaksid nüüd täituma eraldatud numbritega.
   

Kuidas ekstraheerida numbreid ükskõik millisest stringist valemi abil

Saate kasutada valemit, et eraldada stringist kõik numbrid, olenemata nende asukohast.

Numbrite eraldamiseks ükskõik millisest stringist valemi abil tehke järgmist.

1. Valige lahter, kuhu soovite eraldatud numbri kuvada.
2. Sisestage järgmine valem, asendades "B9" teie stringi sisaldava lahtriga:

   =SUMMA(KESK(0&B9,SUUR(INDEKS(ISNUMBER(--MID(B9,RIDA)INDIRECT("1:"&LEN(B9))),1))*RIDA(INDIRECT("1:"&LEN(B9) )),0),RIDA(INDIRECT("1:"&LEN(B9))))+1,1)*10^RIDA(INDIRECT("1:"&LEN(B9)))/10)

3. Teie valem peaks stringist eraldama kõik numbrid.
   
4. Kui soovite, et tühjad lahtrid tagastaksid tühja lahtri, mitte nulli, kasutage selle asemel järgmist valemit:

   =IFERROR(SUMPRODUCT(MID(0&B9,SUR B9))),0),RIDA(INDIRECT("1:"&LEN(B9))))+1,1)*10^RIDA(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Sa saad kasutage automaattäitmist valemi rakendamiseks arvutustabeli muudele lahtritele.
6. Kõik stringid, mis ei sisalda numbreid, tagastavad nulli. Saate õppida, kuidas teisendada Excelis null kriipsuks kui soovite seda muuta.

Laiendage oma Exceli oskusi

Kui õppige Excelis stringist arvu ekstraheerima, saate kiiresti stringide loendist vajalikud andmed eraldada. Kuigi nõutavad valemid võivad olla keerulised, ei pea te täpselt aru saama, kuidas need töötavad; just seda nad teevad!

On veel kasulikke Exceli nippe, mida saate õppida, et viia oma oskused järgmisele tasemele. Sa võid õppida kuidas lugeda lahtreid tekstiga nendes või kuidas arvutada kellegi vanust tema sünnikuupäeva järgi. Saate isegi luua juhuslike arvude generaator Excelis.