Jak extrahovat číslo z řetězce v Excelu

Jak

PodleAdam Davidson

Publikováno7. června 2023

Excel je výkonná tabulka, ale neumí zázraky. Pokud importujete data a tato data jsou nepořádek, Excel s nimi nebude moct dělat. Pokud máte například hodnoty, které jsou připojeny k jiným znakům jako součást řetězce, nebudete s nimi moci provádět výpočty, pokud je nejprve nerozbalíte.

Naštěstí je to možné udělat přesně, i když to může být složitější, než byste si mohli nejprve myslet. Zde je návod, jak extrahovat číslo z řetězce v aplikaci Excel.

Jak extrahovat číslo z konce řetězce pomocí Flash Fill

Rychlý a snadný způsob, jak extrahovat číslo z konce řetězce, je použít Excel Flash Fill Vlastnosti. Tato funkce umožňuje rychle vyplnit rozsah buněk na základě vzoru zjištěného v jedné konkrétní buňce. Jednou nevýhodou této metody je, že vyžaduje, abyste provedli první extrakci sami, a pokud chcete extrahovat pouze číslo z jednoho řetězce, nebude to k ničemu.

Ve většině případů dokáže Flash Fill správně extrahovat číslo z konce řetězce, i když není neomylný, takže byste měli vždy pečlivě zkontrolovat výsledky. Pokud to nefunguje správně, zkuste ručně vyplnit první dvě nebo tři buňky, nikoli jen první.

Postup extrahování čísla z konce řetězce pomocí Flash Fill:

1. Vyberte buňku vedle první buňky obsahující vaše řetězce.
   
2. Zadejte číslo, které se objeví na konci řetězce.
   
3. Pokud jsou všechna vaše data v souvislém sloupci bez mezer, vyberte buňku přímo pod tou, do které jste právě zadali číslo.
   
4. Pokud vaše data obsahují mezery v některých řádcích, vyberte první buňku a všechny buňky pod nimi, ze kterých chcete extrahovat čísla, včetně všech prázdných řádků.
   
5. lis Ctrl+E na Windows nebo Cmd+E na Macu ke spuštění funkce Flash Fill.
6. Zbývající buňky budou automaticky vyplněny na základě vzoru, který Excel zaznamenal. Ve většině případů budou čísla z konce řetězce extrahována správně.
   

Jak extrahovat číslo z konce řetězce pomocí vzorce

Jednou nevýhodou použití metody Flash Fill je, že vyžaduje, abyste sami extrahovali čísla z řetězce pro první řádek. Je možné extrahovat číslo z konce řetězce pomocí vzorce, ačkoli vzorec je poměrně složitý.

Chcete-li extrahovat číslo z konce řetězce pomocí vzorce:

1. Klikněte na buňku, kde se má extrahované číslo zobrazit.
2. Zadejte následující vzorec a nahraďte „B2“ buňkou obsahující váš řetězec:

   =HODNOTA(RIGHT(B2,DÉLKA(B2)-MAX(IF(JE ČÍSLO(STŘED(B2,ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B2))),1)*1)=NEPRAVDA, ŘÁDEK(NEPŘÍMÉ(" 1:"&DÉLKA(B2))),0))))

3. lis Vstupte.
4. Číslo by mělo být extrahováno z řetězce.
5. Chcete-li vzorec použít na zbývající buňky, klepněte na táhlo přetažení v pravém dolním rohu buňky.
   
6. Přetáhněte dolů přes buňky, kde chcete vzorec replikovat.
   
7. Uvolněte myš a vzorec se zkopíruje do vybraných buněk.
8. Tyto buňky by nyní měly obsahovat vaše extrahovaná čísla.
   
9. Všechny buňky bez řetězce k extrakci vrátí chybu. Pokud chcete místo toho vrátit prázdnou buňku, můžete místo toho použít vzorec:

   =IFERROR(HODNOTA(VPRAVO(B2,DÉLKA(B2)-MAX)(IF(JE ČÍSLO(MID(B2,ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B2))),1)*1)=NEPRAVDA, ŘÁDEK(NEPŘÍMÉ ("1:"&DÉLKA(B2))),0)))),"")

10. Všechny prázdné buňky nyní vrátí prázdné místo.
    

Možná se ptáte, co tento vzorec dělá. Stručně řečeno, generuje pole, které má stejnou délku jako řetězec. Umístí každý ze znaků z řetězce do tohoto pole v pořadí. Poté vyhodnotí každý z těchto znaků, aby zjistil, zda je číslo nebo ne. Všechny znaky, které jsou čísly, se převedou na nulu. Jakékoli znaky, které nejsou čísly, jsou převedeny na číslo představující jeho pozici v řetězci.

Například AB5HG23 by se převedl na 1, 2, 0, 4, 5, 0, 0. Poté hledá nejvyšší hodnotu v tomto seznamu, což je pozice posledního znaku, který není číslem. Nakonec vrátí všechny znaky za tímto bodem, což jsou čísla na konci řetězce. Funkce VALUE to pak převede zpět na číslo. Jednoduché, že?

Jak extrahovat číslo ze začátku řetězce pomocí Flash Fill

Pomocí Flash Fill můžete také extrahovat číslo ze začátku řetězce tak, že sami vyplníte první příklad a umožníte Excelu zastavit vzor.

Postup extrahování čísla ze začátku řetězce pomocí Flash Fill:

1. Vyberte buňku napravo od prvního řetězce s číslem, které chcete extrahovat.
2. Zadejte číslo, které se objeví na začátku řetězce.
   
3. Pokud jsou všechna vaše data v souvislém sloupci bez mezer, vyberte buňku přímo pod ním.
   
4. Pokud vaše data obsahují mezery, vyberte první buňku a všechny buňky pod ní, ze kterých chcete extrahovat čísla, včetně všech prázdných řádků.
   
5. lis Ctrl+E na Windows nebo Cmd+E na Macu pro spuštění Flash Fill.
6. Vybrané buňky by nyní měly zobrazovat extrahovaná čísla.
   

Jak extrahovat číslo ze začátku řetězce pomocí vzorce

Můžete také použít vzorec k extrahování čísla ze začátku řetězce. Funguje to podobně jako vzorec v předchozí části, ale najde pozici prvního nečíselného znaku a vrátí všechny předchozí znaky.

Chcete-li extrahovat číslo ze začátku řetězce pomocí vzorce:

1. Vyberte buňku, kde se má extrahované číslo zobrazit.
2. Zadejte následující vzorec a nahraďte „B18“ buňkou obsahující váš řetězec:

   =HODNOTA(LEVÁ(B18,SHODA(NEPRAVDA, JEČÍSLO(MID(B18,ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B18)+1)),1)*1),0)-1))

3. lis Vstupte a číslo bude extrahováno.
   
4. Můžeš použijte automatické vyplňování v Excelu pro zkopírování vzorce do dalších požadovaných buněk.
5. Pokud chcete, aby všechny prázdné řádky vrátily prázdný výsledek, použijte místo toho následující vzorec:

   =IFERROR(HODNOTA(LEFT(B18,SHODA(NEPRAVDA, JEČÍSLO(MID(B18,ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B18)+1)),1)*1),0)-1))," ")

Jak extrahovat čísla odkudkoli v řetězci pomocí Flash Fill

Jak již bylo zmíněno dříve, Flash Fill nemusí fungovat, pokud první buňka neodpovídá formátu některé z ostatních buněk. V tomto případě možná budete muset ručně vypočítat dvě nebo tři buňky, než vám Flash Fill poskytne výsledek, který potřebujete.

Chcete-li extrahovat čísla odkudkoli v řetězci pomocí Flash Fill:

1. Do buňky vedle prvního řetězce ručně zadejte čísla, která jsou v řetězci.
   
2. Pokud jsou všechna vaše data v souvislém sloupci, vyberte buňku přímo pod ním.
   
3. Pokud vaše data obsahují nějaké mezery, vyberte první buňku a všechny buňky pod nimi, ze kterých chcete extrahovat čísla, včetně všech prázdných řádků.
   
4. lis Ctrl+E na Windows nebo Cmd+E na Macu pro spuštění Flash Fill.
5. Buňky by se nyní měly vyplnit extrahovanými čísly.
   

Jak extrahovat čísla odkudkoli v řetězci pomocí vzorce

Pomocí vzorce můžete extrahovat všechna čísla z řetězce bez ohledu na to, kde se vyskytují.

Chcete-li extrahovat čísla odkudkoli v řetězci pomocí vzorce:

1. Vyberte buňku, kde se má extrahované číslo zobrazit.
2. Zadejte následující vzorec a nahraďte „B9“ buňkou obsahující váš řetězec:

   =SOUČETOVÝ SOUČIN(STŘED(0&B9,VELKÝ(INDEX(ISNUMBER(--MID) )),0),ROW(NEPŘÍMÉ("1:"&DÉLKA(B9))))+1,1)*10^ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B9)))/10)

3. Váš vzorec by měl extrahovat všechna čísla z řetězce.
   
4. Pokud chcete, aby všechny prázdné buňky vracely prázdnou buňku, nikoli nulu, použijte tento vzorec:

   =IFERROR(SOUČETNÝ PRODUKT(STŘED(0&B9,LARGE(INDEX(ISNUMBER)(--MID(B9,ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B9))),1))*ŘÁDEK(NEPŘÍMÝ("1:"&DÉLKA( B9))),0),ROW(NEPŘÍMÉ("1:"&DÉLKA(B9))))+1,1)*10^ŘÁDEK(NEPŘÍMÉ("1:"&DÉLKA(B9)))/10)," ")

5. Můžeš použijte Automatické vyplňování aplikujte vzorec na jakékoli jiné buňky v tabulce.
6. Všechny řetězce, které neobsahují žádná čísla, vrátí nulu. Můžete se naučit, jak na to převést nulu na pomlčku v Excelu pokud to chcete změnit.

Rozšiřte své excelentní dovednosti

Naučte se, jak extrahovat číslo z řetězce v Excelu, vám pomůže rychle extrahovat potřebná data ze seznamu řetězců. I když požadované vzorce mohou být složité, nemusíte přesně rozumět tomu, jak fungují; právě že to dělají!

Existují další užitečné triky s Excelem, které se můžete naučit a posunout své dovednosti na další úroveň. Můžeš se učit jak počítat buňky s textem v nich, nebo jak na to vypočítat věk někoho z jeho data narození. Můžete dokonce vytvořit generátor náhodných čísel v Excelu.