Ako extrahovať číslo z reťazca v Exceli

Ako

Autor:Adam Davidson

Publikovaný7. júna 2023

Excel je výkonná tabuľka, no nedokáže robiť zázraky. Ak importujete údaje a tieto údaje sú neporiadok, Excel s nimi nebude môcť veľa urobiť. Ak máte napríklad hodnoty, ktoré sú pripojené k iným znakom ako súčasť reťazca, nebudete s nimi môcť vykonávať výpočty, pokiaľ ich najprv nerozbalíte.

Našťastie je to možné urobiť presne tak, aj keď to môže byť komplikovanejšie, ako by ste si na prvý pohľad mysleli. Tu je postup, ako extrahovať číslo z reťazca v Exceli.

Ako extrahovať číslo z konca reťazca pomocou Flash Fill

Rýchlym a jednoduchým spôsobom, ako extrahovať číslo z konca reťazca, je použiť Excel Blesková výplň vlastnosť. Táto funkcia vám umožňuje rýchlo vyplniť rozsah buniek na základe vzoru zisteného v jednej konkrétnej bunke. Jednou nevýhodou tejto metódy je, že vyžaduje, aby ste prvú extrakciu urobili sami a ak chcete extrahovať iba číslo z jedného reťazca, nebude to k ničomu.

Vo väčšine prípadov Flash Fill dokáže správne extrahovať číslo z konca reťazca, aj keď nie je neomylný, preto by ste mali vždy pozorne kontrolovať výsledky. Ak to nefunguje správne, skúste manuálne vyplniť prvé dve alebo tri bunky, nie len prvú.

Ak chcete extrahovať číslo z konca reťazca pomocou Flash Fill:

1. Vyberte bunku vedľa prvej bunky obsahujúcej vaše reťazce.
   
2. Zadajte číslo, ktoré sa zobrazí na konci reťazca.
   
3. Ak sú všetky vaše údaje v súvislom stĺpci bez medzier, vyberte bunku priamo pod tou, do ktorej ste práve zadali číslo.
   
4. Ak vaše údaje obsahujú medzery v niektorých riadkoch, vyberte prvú bunku a všetky bunky pod ňou, z ktorých chcete extrahovať čísla, vrátane všetkých prázdnych riadkov.
   
5. Stlačte tlačidlo Ctrl+E v systéme Windows alebo Cmd + E na Macu, aby ste spustili funkciu Flash Fill.
6. Vaše zostávajúce bunky sa automaticky vyplnia podľa vzoru, ktorý Excel zistil. Vo väčšine prípadov sa čísla z konca reťazca vytiahnu správne.
   

Ako extrahovať číslo z konca reťazca pomocou vzorca

Jednou nevýhodou použitia metódy Flash Fill je, že vyžaduje, aby ste sami extrahovali čísla z reťazca pre prvý riadok. Je možné extrahovať číslo z konca reťazca pomocou vzorca, hoci vzorec je dosť zložitý.

Ak chcete extrahovať číslo z konca reťazca pomocou vzorca:

1. Kliknite na bunku, kde sa má zobraziť extrahované číslo.
2. Zadajte nasledujúci vzorec a nahraďte „B2“ bunkou obsahujúcou váš reťazec:

   =HODNOTA(VPRAVO(B2,DĹŽKA(B2)-MAX(AK)(JE ČÍSLO(STRED(B2,RIADOK(NEPRIAME("1:"&DĹŽKA(B2))),1)*1)=NEPRAVDA, RIADOK(NEPRIAME(" 1:"&LEN(B2))),0))))

3. Stlačte tlačidlo Zadajte.
4. Číslo by sa malo extrahovať z reťazca.
5. Ak chcete použiť vzorec na všetky zostávajúce bunky, kliknite na rukoväť ťahania v pravom dolnom rohu bunky.
   
6. Potiahnite nadol cez bunky, kde chcete, aby sa vzorec replikoval.
   
7. Uvoľnite myš a vzorec sa skopíruje do vybratých buniek.
8. Tieto bunky by teraz mali obsahovať vaše extrahované čísla.
   
9. Všetky bunky bez reťazca na extrahovanie vrátia chybu. Ak chcete namiesto toho vrátiť prázdnu bunku, môžete namiesto toho použiť vzorec:

   =IFERROR(HODNOTA(VPRAVO(B2,DĹŽKA(B2)-MAX(AK)(JE ČÍSLO(MID(B2,RIADOK(NEPRIAME("1:"&DĹŽKA(B2))),1)*1)=NEPRAVDA, RIADOK(NEPRIAME ("1:"&LEN(B2))),0)))),"")

10. Všetky prázdne bunky teraz vrátia prázdne miesto.
    

Možno sa pýtate, čo tento vzorec robí. Stručne povedané, vygeneruje pole, ktoré má rovnakú dĺžku ako reťazec. Umiestňuje každý zo znakov z reťazca do tohto poľa v poradí. Potom vyhodnotí každý z týchto znakov, aby zistil, či je číslo alebo nie. Všetky znaky, ktoré sú číslami, sa skonvertujú na nulu. Všetky znaky, ktoré nie sú číslami, sa skonvertujú na číslo predstavujúce jeho pozíciu v reťazci.

Napríklad AB5HG23 sa prevedie na 1, 2, 0, 4, 5, 0, 0. Potom hľadá najvyššiu hodnotu v tomto zozname, čo je pozícia posledného znaku, ktorý nie je číslom. Nakoniec vráti všetky znaky za týmto bodom, čo sú čísla na konci reťazca. Funkcia VALUE to potom skonvertuje späť na číslo. Jednoduché, však?

Ako extrahovať číslo zo začiatku reťazca pomocou Flash Fill

Flash Fill môžete použiť aj na extrahovanie čísla zo začiatku reťazca tak, že sami vyplníte prvý príklad a umožníte Excelu zastaviť vzor.

Ak chcete extrahovať číslo zo začiatku reťazca pomocou Flash Fill:

1. Vyberte bunku napravo od prvého reťazca s číslom, ktoré chcete extrahovať.
2. Zadajte číslo, ktoré sa zobrazí na začiatku reťazca.
   
3. Ak sú všetky vaše údaje v súvislom stĺpci bez medzier, vyberte bunku priamo pod ním.
   
4. Ak vaše údaje obsahujú medzery, vyberte prvú bunku a všetky bunky pod ňou, z ktorých chcete extrahovať čísla, vrátane všetkých prázdnych riadkov.
   
5. Stlačte tlačidlo Ctrl+E v systéme Windows alebo Cmd + E na Macu a spustite Flash Fill.
6. Bunky, ktoré ste vybrali, by teraz mali zobrazovať extrahované čísla.
   

Ako extrahovať číslo zo začiatku reťazca pomocou vzorca

Na extrahovanie čísla zo začiatku reťazca môžete použiť aj vzorec. Funguje to podobne ako vzorec v predchádzajúcej časti, ale nájde pozíciu prvého nečíselného znaku a vráti všetky predchádzajúce znaky.

Ak chcete extrahovať číslo zo začiatku reťazca pomocou vzorca:

1. Vyberte bunku, v ktorej sa má zobraziť extrahované číslo.
2. Zadajte nasledujúci vzorec a nahraďte „B18“ bunkou obsahujúcou váš reťazec:

   =HODNOTA(LEFT(B18,MATCH(FALSE, ISNUMBER)(MID(B18,RAD(NEPRIAME("1:"&DĹŽKA(B18)+1)),1)*1),0)-1))

3. Stlačte tlačidlo Zadajte a číslo sa vytiahne.
   
4. Môžeš použite automatické dopĺňanie v Exceli na skopírovanie vzorca do iných buniek, ktoré požadujete.
5. Ak chcete, aby všetky prázdne riadky vrátili prázdny výsledok, použite namiesto toho nasledujúci vzorec:

   =IFERROR(HODNOTA(LEFT(B18,ZHODA(NEPRAVDA, JE ČÍSLO(MID(B18,RIADOK(NEPRIAME("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Ako extrahovať čísla odkiaľkoľvek v reťazci pomocou Flash Fill

Ako už bolo spomenuté, Flash Fill nemusí fungovať, ak prvá bunka nezodpovedá formátu niektorých ostatných buniek. V tomto prípade možno budete musieť manuálne vypočítať dve alebo tri bunky, kým funkcia Flash Fill poskytne požadovaný výsledok.

Ak chcete extrahovať čísla odkiaľkoľvek v reťazci pomocou Flash Fill:

1. Do bunky vedľa prvého reťazca ručne zadajte čísla, ktoré sú v reťazci.
   
2. Ak sú všetky vaše údaje v súvislom stĺpci, vyberte bunku priamo pod ním.
   
3. Ak vaše údaje obsahujú nejaké medzery, vyberte prvú bunku a všetky bunky pod ňou, z ktorých chcete extrahovať čísla, vrátane všetkých prázdnych riadkov.
   
4. Stlačte tlačidlo Ctrl+E v systéme Windows alebo Cmd + E na Macu a spustite Flash Fill.
5. Bunky by sa teraz mali vyplniť extrahovanými číslami.
   

Ako extrahovať čísla odkiaľkoľvek v reťazci pomocou vzorca

Pomocou vzorca môžete extrahovať všetky čísla z reťazca bez ohľadu na to, kde sa nachádzajú.

Ak chcete extrahovať čísla odkiaľkoľvek v reťazci pomocou vzorca:

1. Vyberte bunku, v ktorej sa má zobraziť extrahované číslo.
2. Zadajte nasledujúci vzorec a nahraďte „B9“ bunkou obsahujúcou váš reťazec:

   =SÚČETNÝ PRODUKT(STRED(0&B9,LARGE(INDEX(ISNUMBER(--MID) )),0),RIADOK(NEPRIAME("1:"&DĹŽKA(B9))))+1,1)*10^ROW(NEPRIAME("1:"&DĹŽKA(B9)))/10)

3. Váš vzorec by mal extrahovať všetky čísla z reťazca.
   
4. Ak chcete, aby všetky prázdne bunky vrátili prázdnu bunku namiesto nuly, použite tento vzorec:

   =IFERROR(SÚČETNÝ PRODUKT(STRED(0&B9,LARGE(INDEX(ISNUMBER)(--MID(B9,RIADOK(NEPRIAME("1:"&DĹŽKA(B9))),1))*RIADOK(NEPRIAME("1:"&DĹŽKA( B9))),0),ROW(NEPRIAME("1:"&DĹŽKA(B9))))+1,1)*10^ROW(NEPRIAME("1:"&LEN(B9)))/10)," ")

5. Môžeš použite Automatické dopĺňanie aplikujte vzorec na všetky ostatné bunky v tabuľke.
6. Všetky reťazce, ktoré neobsahujú žiadne čísla, vrátia nulu. Môžete sa naučiť, ako na to previesť nulu na pomlčku v Exceli ak to chcete zmeniť.

Rozšírte svoje excelové zručnosti

Naučiť sa extrahovať číslo z reťazca v Exceli vám môže pomôcť rýchlo extrahovať potrebné údaje zo zoznamu reťazcov. Hoci požadované vzorce môžu byť zložité, nemusíte presne rozumieť tomu, ako fungujú; práve to robia!

Existujú ďalšie užitočné triky programu Excel, ktoré sa môžete naučiť a posunúť svoje zručnosti na ďalšiu úroveň. Môžeš sa naučiť ako počítať bunky s textom v nich, alebo ako na to vypočítať vek niekoho z dátumu narodenia. Môžete dokonca vytvoriť generátor náhodných čísel v Exceli.