Kuinka purkaa numero merkkijonosta Excelissä

Miten

Tekijä:Adam Davidson

Julkaistu7. kesäkuuta 2023

Excel on tehokas laskentataulukko, mutta se ei voi tehdä ihmeitä. Jos tuot tietoja ja tiedot ovat sotkuisia, Excel ei voi tehdä niille paljon. Jos sinulla on esimerkiksi arvoja, jotka on liitetty muihin merkkeihin osana merkkijonoa, et voi suorittaa niille laskelmia, ellet pura niitä ensin.

Onneksi se on mahdollista tehdä juuri niin, vaikka se voi olla monimutkaisempaa kuin aluksi luulisi. Näin voit poimia numeron merkkijonosta Excelissä.

Kuinka erottaa numero merkkijonon lopusta Flash Fill -toiminnolla

Nopea ja helppo tapa poimia numero merkkijonon lopusta on käyttää Exceliä Flash Fill ominaisuus. Tämän ominaisuuden avulla voit nopeasti täyttää solualueen tietyssä solussa havaitun kuvion perusteella. Yksi tämän menetelmän haittapuoli on, että se edellyttää, että sinun on tehtävä ensimmäinen purku itse, ja jos haluat poimia vain numeron yhdestä merkkijonosta, siitä ei ole mitään hyötyä.

Useimmissa tapauksissa Flash Fill voi poimia numeron oikein merkkijonon lopusta, vaikka se ei ole erehtymätön, joten sinun tulee aina tarkistaa tulokset huolellisesti. Jos se ei toimi oikein, yritä täyttää kaksi tai kolme ensimmäistä solua manuaalisesti ensimmäisen sijasta.

Numeron erottaminen merkkijonon lopusta Flash Fill -toiminnolla:

1. Valitse ensimmäisen merkkijonosi sisältävän solun vieressä oleva solu.
   
2. Syötä numero, joka näkyy merkkijonon lopussa.
   
3. Jos kaikki tietosi ovat jatkuvassa sarakkeessa ilman aukkoja, valitse solu suoraan sen solun alapuolelta, johon juuri syötit numeron.
   
4. Jos tiedoissasi on aukkoja joillakin riveillä, valitse ensimmäinen solu ja kaikki alla olevat solut, joista haluat poimia numeroita, mukaan lukien tyhjät rivit.
   
5. Lehdistö Ctrl+E Windowsissa tai Cmd+E Macissa Flash Fill -ominaisuuden suorittamiseksi.
6. Loput solut täytetään automaattisesti Excelin havaitseman mallin perusteella. Useimmissa tapauksissa numerot merkkijonon lopusta poimitaan oikein.
   

Kuinka erottaa numero merkkijonon lopusta kaavan avulla

Yksi Flash Fill -menetelmän käytön haittapuoli on, että se edellyttää numeroiden poimimista ensimmäisen rivin merkkijonosta itse. On mahdollista poimia luku merkkijonon lopusta kaavan avulla, vaikka kaava onkin melko monimutkainen.

Numeron erottaminen merkkijonon lopusta kaavan avulla:

1. Napsauta solua, johon haluat puretun luvun näkyvän.
2. Kirjoita seuraava kaava ja korvaa "B2" solulla, joka sisältää merkkijonosi:

   =ARVO(OIKEA(B2,PITOA(B2)-MAX(JOS(ISNUMERO(KESKUS(B2,RIVI(PIDÄ() 1:"&PITKÄ(B2))),0)))

3. Lehdistö Tulla sisään.
4. Numero tulee poimia merkkijonosta.
5. Voit käyttää kaavaa kaikkiin jäljellä oleviin soluihin napsauttamalla solun oikeassa alakulmassa olevaa vetokahvaa.
   
6. Vedä alas niiden solujen yli, joihin haluat replikoida kaavan.
   
7. Vapauta hiiri, niin kaava kopioidaan valittuihin soluihin.
8. Näiden solujen pitäisi nyt sisältää poimitut numerot.
   
9. Kaikki solut, joissa ei ole poimittavaa merkkijonoa, palauttavat virheilmoituksen. Jos haluat sen sijaan palauttaa tyhjän solun, voit sen sijaan käyttää kaavaa:

   =IFORROR(ARVO(OIKEA(B2,PITOA ("1:"&PITK.(B2))),0)))),"")

10. Kaikki tyhjät solut palauttavat nyt tyhjän tilan.
    

Saatat ihmetellä, mitä tämä kaava tekee. Lyhyesti sanottuna se luo taulukon, joka on samanpituinen kuin merkkijono. Se sijoittaa kaikki merkkijonon merkit tähän taulukkoon järjestyksessä. Sitten se arvioi jokaisen näistä merkeistä nähdäkseen, ovatko ne numeroita vai eivät. Kaikki merkit, jotka ovat numeroita, muunnetaan nollaksi. Kaikki merkit, jotka eivät ole numeroita, muunnetaan numeroiksi, jotka edustavat sen sijaintia merkkijonossa.

Esimerkiksi AB5HG23 muuttaisi 1, 2, 0, 4, 5, 0, 0. Sitten se etsii tämän luettelon suurinta arvoa, joka on viimeisen merkin sijainti, joka ei ole numero. Lopuksi se palauttaa kaikki tämän pisteen jälkeiset merkit, jotka ovat merkkijonon lopussa olevia numeroita. ARVO-funktio muuntaa tämän sitten takaisin luvuksi. Yksinkertaista, eikö?

Kuinka purkaa numero merkkijonon alusta Flash Fill -toiminnolla

Voit myös käyttää Flash Fill -toimintoa poimimaan numeron merkkijonon alusta täyttämällä ensimmäisen esimerkin itse ja antamalla Excelin pysäyttää kuvion.

Numeron poimiminen merkkijonon alusta Flash Fill -toiminnolla:

1. Valitse solu ensimmäisen merkkijonon oikealla puolella, jossa on numero, jonka haluat poimia.
2. Syötä numero, joka näkyy merkkijonon alussa.
   
3. Jos kaikki tietosi ovat jatkuvassa sarakkeessa ilman aukkoja, valitse solu suoraan alla.
   
4. Jos tiedoissasi on aukkoja, valitse ensimmäinen solu ja kaikki alla olevat solut, joista haluat poimia numeroita, mukaan lukien tyhjät rivit.
   
5. Lehdistö Ctrl+E Windowsissa tai Cmd+E Macissa käynnistääksesi Flash Fillin.
6. Valitsemiesi solujen pitäisi nyt näyttää poimitut luvut.
   

Kuinka erottaa numero merkkijonon alusta kaavan avulla

Voit myös käyttää kaavaa poimimaan luvun merkkijonon alusta. Tämä toimii samalla tavalla kuin edellisen osan kaava, mutta löytää ensimmäisen ei-numeerisen merkin sijainnin ja palauttaa kaikki sitä edeltävät merkit.

Numeron erottaminen merkkijonon alusta kaavan avulla:

1. Valitse solu, johon haluat puretun luvun näkyvän.
2. Kirjoita seuraava kaava ja korvaa "B18" solulla, joka sisältää merkkijonosi:

   =ARVO(VASEN(B18,OSUMA(EPÄTOSI, ISNUMERO(KESKUS(B18,RIVI(PUOLA("1:"&PITO(B18)+1)),1)*1),0)-1))

3. Lehdistö Tulla sisään ja numero poimitaan.
   
4. Sinä pystyt käytä automaattista täyttöä Excelissä kopioidaksesi kaavan muihin tarvitsemiisi soluihin.
5. Jos haluat, että tyhjät rivit palauttavat tyhjän tuloksen, käytä sen sijaan seuraavaa kaavaa:

   =IFORROR(ARVO(LEFT(B18,MATCH(EPÄTOSI, ISNUMERO(KESKI(B18,RIVI(PUOLA("1:"&PITO(B18)+1)),1)*1),0)-1)" ")

Numeroiden poimiminen mistä tahansa merkkijonosta Flash Fillillä

Kuten aiemmin mainittiin, Flash Fill ei välttämättä toimi, jos ensimmäinen solu ei vastaa joidenkin muiden solujen muotoa. Tässä tapauksessa saatat joutua laskemaan manuaalisesti kaksi tai kolme solua, ennen kuin Flash Fill antaa tarvitsemasi tuloksen.

Numeroiden poimiminen mistä tahansa merkkijonosta Flash Fill -toiminnolla:

1. Kirjoita ensimmäisen merkkijonon viereiseen soluun manuaalisesti merkkijonossa olevat numerot.
   
2. Jos kaikki tietosi ovat jatkuvassa sarakkeessa, valitse solu suoraan alla.
   
3. Jos tiedoissasi on aukkoja, valitse ensimmäinen solu ja kaikki alla olevat solut, joista haluat poimia numeroita, mukaan lukien tyhjät rivit.
   
4. Lehdistö Ctrl+E Windowsissa tai Cmd+E Macissa käynnistääksesi Flash Fillin.
5. Solujen pitäisi nyt täyttyä poimituilla numeroilla.
   

Kuinka poimia numeroita mistä tahansa merkkijonosta kaavan avulla

Voit käyttää kaavaa poimimaan kaikki luvut merkkijonosta riippumatta siitä, missä ne esiintyvät.

Numeroiden poimiminen mistä tahansa merkkijonosta kaavan avulla:

1. Valitse solu, johon haluat puretun luvun näkyvän.
2. Kirjoita seuraava kaava ja korvaa "B9" solulla, joka sisältää merkkijonosi:

   =SUMMA(keski )),0),RIVI(PUOLA("1:"&PITOA(B9))))+1,1)*10^RIVI(PUOLA("1:"&LEN(B9)))/10)

3. Kaavasi pitäisi poimia kaikki numerot merkkijonosta.
   
4. Jos haluat tyhjien solujen palauttavan tyhjän solun nollan sijaan, käytä tätä kaavaa:

   =IFERROR(SUMMA(keski B9))),0),RIVI(PUOLA("1:"&PITOA(B9)))+1,1)*10^RIVI(PUOLA("1:"&LEN(B9)))/10)," ")

5. Sinä pystyt käytä automaattista täyttöä soveltaaksesi kaavaa muihin laskentataulukon soluihin.
6. Kaikki merkkijonot, jotka eivät sisällä numeroita, palauttavat nollan. Voit oppia kuinka muuntaa nolla viivaksi Excelissä jos haluat muuttaa tätä.

Laajenna Excel-taitojasi

Kun opettelet poimimaan numeron merkkijonosta Excelissä, voit nopeasti poimia tarvitsemasi tiedot merkkijonoluettelosta. Vaikka vaaditut kaavat voivat olla monimutkaisia, sinun ei tarvitse ymmärtää tarkasti, miten ne toimivat. juuri niin he tekevät!

Voit oppia lisää hyödyllisiä Excel-temppuja viedäksesi taitosi uudelle tasolle. Voit oppia kuinka laskea soluja tekstillä niissä tai miten laskea jonkun ikä hänen syntymäpäivästään. Voit jopa luo satunnaislukugeneraattori Excelissä.