✅ Pura numerot jonosta Excelissä (kaavojen tai VBA: n avulla)

Sisällysluettelo

Pura numeroita merkkijonosta Excelissä (Excel -kaava 2016)
Pura numerot merkkijonosta Excelissä (Excel 2013/2010/2007)
Erota teksti ja numerot Excelissä VBA: n avulla

Katso video - Kuinka poimia numeroita tekstijonoista Excelissä (kaavan ja VBA: n avulla)

Excelissä ei ole sisäänrakennettua toimintoa numeroiden erottamiseksi solun merkkijonosta (tai päinvastoin - poista numeerinen osa ja poista tekstiosa aakkosnumeerisesta merkkijonosta).

Tämä voidaan kuitenkin tehdä käyttämällä Excel -toimintoja tai jotain yksinkertaista VBA -koodia.

Näytän ensin mistä puhun.

Oletetaan, että sinulla on alla olevan kuvan mukainen tietojoukko ja haluat poimia numerot merkkijonosta (kuten alla):

Valitsemasi menetelmä riippuu myös käyttämästäsi Excel -versiosta:

Jos käytät versiota ennen Excel 2016: ta, sinun on käytettävä hieman pidempiä kaavoja
Excel 2016: ssa voit käyttää uutta TEXTJOIN -toimintoa
VBA -menetelmää voidaan käyttää kaikissa Excel -versioissa

Lataa esimerkkitiedosto napsauttamalla tätä

Pura numeroita merkkijonosta Excelissä (Excel -kaava 2016)

Tämä kaava toimii vain Excel 2016: ssa, koska se käyttää uutta TEXTJOIN -funktiota.

Tämä kaava voi myös poimia tekstimerkkijonon alussa, lopussa tai keskellä olevat numerot.

Huomaa, että tässä osassa käsitelty TEXTJOIN -kaava antaisi sinulle kaikki numeeriset merkit yhdessä. Jos esimerkiksi teksti on ”10 lipun hinta on 200 USD”, tuloksena on 10200.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat poimia numerot kunkin solun merkkijonoista:

Alla on kaava, joka antaa sinulle numeerisen osan Excel -merkkijonosta.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, Rivi (Epäsuora ("1:" & LEN (A2))), 1)*1), ""))

Tämä on taulukkokaava, joten sinun on käytettäväControl + Vaihto + Enter'Enterin käytön sijasta.

Jos tekstimerkkijonossa ei ole numeroita, tämä kaava palauttaa tyhjän (tyhjä merkkijono).

Kuinka tämä kaava toimii?

Haluan rikkoa tämän kaavan ja yrittää selittää, miten se toimii:

RIVI (Epäsuora ("1:" & LEN (A2))) - tämä kaavan osa antaisi numerosarjan, joka alkaa yhdestä. Kaavan LEN -funktio palauttaa merkkijonon merkkien kokonaismäärän. Jos hinta on 100 dollaria, se palauttaa 19. Kaavoista tulee näin ollen RIVI (Epäsuora ("1:19"). RIVI -funktio palauttaa sitten numerosarjan - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, RIVI (Epäsuora (“1:” & LEN (A2)))), 1)*1) - Tämä kaavan osa palauttaisi taulukon #VALUE! virheitä tai numeroita merkkijonon perusteella. Kaikista merkkijonon tekstimerkeistä tulee #ARVO! virheet ja kaikki numeeriset arvot pysyvät ennallaan. Tämä tapahtuu, kun olemme kertoneet MID -toiminnon yhdellä.
IFERROR ((MID (A2, RIVI (Epäsuora (“1:” & LEN (A2)))))), 1)*1), ””) - Kun IFERROR -toimintoa käytetään, se poistaisi kaikki #ARVO! virheitä ja vain numerot jäävät. Tämän osan tulos näyttäisi tältä: "" ";" ";" ";" ""; "" ";" ";" ";" ";" " ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, Rivi (Epäsuora (”1:” & LEN (A2))), 1)*1), ””)) - TEXTJOIN -funktio yhdistää nyt yksinkertaisesti merkkijonomerkit joka jää (jotka ovat vain numeroita) ja jättää tyhjän merkkijonon huomiotta.

Pro -vinkki: Jos haluat tarkistaa kaavan osan tuloksen, valitse solu, siirry muokkaustilaan painamalla F2, valitse kaavan osa, jolle haluat tuloksen, ja paina F9. Näet heti tuloksen. Muista sitten painaa Control + Z tai paina Escape -näppäintä. ÄLÄ paina Enter -näppäintä.

Lataa esimerkkitiedosto

Voit myös käyttää samaa logiikkaa poimiaksesi tekstiosan aakkosnumeerisesta merkkijonosta. Alla on kaava, joka saisi tekstiosan merkkijonosta:

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (Epäsuora ("1:" & LEN (A2)))), 1)*1), KESKI (A2, Rivi (Epäsuora ("1:" & LEN) (A2))), 1), ""))

Pieni muutos tähän kaavaan on, että IF -funktiota käytetään tarkistamaan, ovatko MID -funktiosta saamamme taulukko virheitä vai eivät. Jos se on virhe, se säilyttää arvon, se korvaa sen tyhjällä.

Sitten TEXTJOIN käytetään kaikkien tekstimerkkien yhdistämiseen.

Varoitus: Vaikka tämä kaava toimii hyvin, se käyttää haihtuvaa funktiota (INDIRECT -funktiota). Tämä tarkoittaa sitä, että jos käytät tätä valtavan tietojoukon kanssa, tulosten saaminen voi kestää jonkin aikaa. On parasta luoda varmuuskopio ennen kuin käytät tätä kaavaa Excelissä.

Pura numerot merkkijonosta Excelissä (Excel 2013/2010/2007)

Jos sinulla on Excel 2013. 2010. tai 2007, et voi käyttää TEXTJOIN -kaavaa, joten sinun on käytettävä monimutkaista kaavaa tämän suorittamiseksi.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat poimia kaikki numerot jokaisen solun merkkijonosta.

Alla oleva kaava tekee tämän:

= JOS (SUM (LEN (A2) -LEN (KORVAUS (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)) * RIVI (Epäsuora ("$ 1: $" & LEN (A2))), 0), RIVI (Epäsuora ("$ 1: $" & LEN (A2)))))+1,1) * 10^RIVI (Epäsuora ("$ 1: $" & LEN (A2)))/10), "")

Jos tekstimerkkijonossa ei ole numeroa, tämä kaava palauttaa tyhjän (tyhjä merkkijono).

Vaikka tämä on taulukkokaava, sinä ei tarvitse käyttääksesi "Control-Shift-Enter" käyttääksesi tätä. Yksinkertainen enter toimii tällä kaavalla.

Kiitos tästä kaavasta menee hämmästyttävään Mr. Excel -foorumiin.

Jälleen tämä kaava purkaa kaikki merkkijonon numerot sijainnista riippumatta. Jos esimerkiksi teksti "10 lipun hinta on 200 USD", saat tuloksena 10 200.

Erota teksti ja numerot Excelissä VBA: n avulla

Jos tekstin ja numeroiden erottaminen (tai numeroiden poimiminen tekstistä) on usein pakollista, voit käyttää myös VBA -menetelmää.

Sinun tarvitsee vain luoda yksinkertainen VBA -koodi luodaksesi mukautetun käyttäjän määrittämän funktion (UDF) Excelissä ja käyttää sitten pitkää ja monimutkaista kaavaa sen VBA -kaavan sijaan.

Näytän sinulle kuinka luoda kaksi kaavaa VBA: ssa - yksi numeroiden poimimiseksi ja toinen tekstin poimimiseksi merkkijonosta.

Pura numerot merkkijonosta Excelissä (VBA: n avulla)

Tässä osassa näytän sinulle, miten voit luoda mukautetun funktion saadaksesi vain numeerisen osan merkkijonosta.

Alla on VBA -koodi, jota käytämme tämän mukautetun toiminnon luomiseen:

Funktio GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) Sitten Result = Result & Mid (CellRef, i, 1) Seuraava i GetNumeric = Tuloksen lopetusfunktio

Voit luoda tämän toiminnon ja käyttää sitä laskentataulukossa seuraavasti:

Siirry Kehittäjä -välilehdelle.
Napsauta Visual Basic (Voit käyttää myös pikanäppäintä ALT + F11)
Napsauta avautuvassa VB Editor-taustaohjelmassa mitä tahansa työkirjan objektia hiiren kakkospainikkeella.
Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää työkirjan moduuliobjektin.
Kopioi ja liitä yllä mainittu VBA -koodi Moduulikoodi -ikkunassa.
Sulje VB Editor.

Nyt voit käyttää laskentataulukon GetText -toimintoa. Koska olemme tehneet kaikki raskaat nostot itse koodissa, sinun tarvitsee vain käyttää kaavaa = GetNumeric (A2).

Tämä antaa sinulle heti vain merkkijonon numeerisen osan.

Huomaa, että koska työkirjassa on nyt VBA -koodi, sinun on tallennettava se .xls- tai .xlsm -laajennuksella.

Lataa esimerkkitiedosto

Jos sinun on käytettävä tätä kaavaa usein, voit myös tallentaa sen henkilökohtaiseen makro -työkirjaasi. Tämän avulla voit käyttää tätä mukautettua kaavaa kaikissa Excel -työkirjoissa, joiden kanssa työskentelet.

Pura teksti merkkijonosta Excelissä (VBA: n avulla)

Tässä osassa näytän sinulle, miten voit luoda mukautetun toiminnon, jotta vain tekstiosa saadaan merkkijonosta.

Alla on VBA -koodi, jota käytämme tämän mukautetun toiminnon luomiseen:

Funktio GetText (CellRef as String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength if Not (IsNumeric (Mid (CellRef, i, 1))) Sitten Result = Result & Mid (CellRef, i, 1 ) Seuraava i GetText = Tuloksen lopetusfunktio

Voit luoda tämän toiminnon ja käyttää sitä laskentataulukossa seuraavasti:

Siirry Kehittäjä -välilehdelle.
Napsauta Visual Basic (Voit käyttää myös pikanäppäintä ALT + F11)
Napsauta avautuvassa VB Editor-taustaohjelmassa mitä tahansa työkirjan objektia hiiren kakkospainikkeella.
Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää työkirjan moduuliobjektin.
- Jos sinulla on jo moduuli, kaksoisnapsauta sitä (sinun ei tarvitse lisätä uutta, jos sinulla on jo se).
Kopioi ja liitä yllä mainittu VBA -koodi Moduulikoodi -ikkunassa.
Sulje VB Editor.

Nyt voit käyttää laskentataulukon GetNumeric -toimintoa. Koska olemme tehneet kaikki raskaat nostot itse koodissa, sinun tarvitsee vain käyttää kaavaa = GetText (A2).

Tämä antaa sinulle heti vain merkkijonon numeerisen osan.