Jak oddělit čísla od textu v Excelu
V této příručce vám ukážeme, jak oddělit čísla od textu v Excelu na počítači s Windows 11/10.
Při práci s Microsoft Excel se můžete setkat s daty, která obsahují smíšené řetězce v jednom sloupci. Například název produktu a jeho množství mohou být uloženy společně v jedné buňce. Před prováděním výpočtů nebo analýz možná budete muset oddělit čísla a text z těchto řetězců a umístit je do různých sloupců. I když Excel nenabízí speciální možnost pro extrakci čísel nebo textu ze smíšených řetězců, existuje několik způsobů, jak tento úkol provést.
Jak oddělit čísla od textu v Excelu
Chcete-li oddělit čísla od textu v Excelu na počítači s Windows 11/10, použijte tyto metody:
- Použít Rychlé vyplnění
- Použít text do sloupců
- Používejte vzorce
- Použít Power Query
Pojďme si tyto metody podrobně prohlédnout.
1. Použijte Flash Fill
Pokud vaše data následují v celém listu konzistentní vzorec a je nepravděpodobné, že se později změní, můžete použít funkci Flash Fill v Excelu k oddělení čísel od textu. Flash Fill automaticky rozpozná vzorce v dané sadě dat a odpovídajícím způsobem vyplní zbývající buňky.
Otevřete svůj Excel list obsahující smíšené řetězce. Vložte nový sloupec vedle zdrojových dat. Do první buňky nového sloupce ručně napište čísla, která chcete extrahovat z odpovídající buňky. Například, pokud A2 obsahuje ABC123, zadejte 123 do B2.
Vyberte další buňku níže (B3) a začněte psát očekávaný výsledek pro další položku. Excel zobrazí náhled extrahovaných hodnot pro zbývající buňky. Stiskněte Enter pro potvrzení navržených výsledků.
Alternativně, po zadání několika příkladů vyberte sloupec, kde chcete, aby se výsledky zobrazily, a přejděte na data-start="1179" data-end="1200">Data > Bleskové vyplnění, nebo stiskněte data-start="1211" data-end="1223">Ctrl + E. Excel automaticky extrahuje čísla z ostatních buněk na základě vzoru, který jste poskytli.
Poznámka: Chcete-li extrahovat pouze textovou část, opakujte stejný postup, ale zadejte jako vzorovou hodnotu textovou část (například ABC z ABC123). Funkce Flash Fill pak oddělí text od zbývajících položek.
2. Použít text do sloupců
Pokud jsou text a čísla ve vašich datech odděleny konzistentním oddělovačem (například mezerou, pomlčkou, čárkou nebo podtržítkem), můžete použít funkci Excelu Text do sloupců k jejich rozdělení do samostatných sloupců.
Například, pokud vaše data obsahují hodnoty jako Product-123, Laptop-101 nebo Printer-523, může Text do sloupců rychle oddělit názvy produktů od jejich odpovídajících ID.
Chcete-li použít Text do sloupců, vyberte buňky obsahující smíšené řetězce a přejděte na kartu Data. Klikněte na Text do sloupců ve skupině Nástroje pro data.
V průvodci Převést text na sloupce vyberte Oddělené a klikněte na Další. Vyberte oddělovač použitý ve vašich datech a znovu klikněte na Další.
Vyberte cílové umístění pro oddělená data a potom klikněte na Dokončit. Excel rozdělí text a čísla do samostatných sloupců.
3. Používejte vzorce
Pokud se vaše data často mění a chcete, aby se výsledky automaticky aktualizovaly, můžete použít vzorce aplikace Excel k oddělení čísel od textu. V závislosti na tom, jak jsou vaše data strukturována, můžete použít jednu z následujících metod.
A] Pro řetězce, kde se nejdříve objeví text a čísla se objeví na konci
Pro hodnoty jako: Product123, Laptop101 nebo Mouse456, použijte následující vzorec pro extrakci číselné části:
=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)Nahraďte A2 buňkou obsahující váš smíšený řetězec a stiskněte Enter. Excel vrátí číselnou část řetězce.
Chcete-li vzorec aplikovat na zbývající položky, vyberte buňku obsahující vzorec, přesuňte kurzor na malý čtvereček v pravém dolním rohu buňky (tzv. úchyt vyplnění) a táhněte dolů po sloupci. Excel automaticky vyextrahuje čísla z odpovídajících buněk.
Chcete-li extrahovat textovou část, použijte následující vzorec:
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)B] Pro řetězce, kde se mohou čísla objevit kdekoli
Pokud vaše data obsahují čísla na různých pozicích, například ABC123, 123ABC, AB123CD nebo A12B34, použijte následující vzorec:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))Nahraďte A2 buňkou obsahující váš smíšený řetězec a stiskněte Enter. Excel prozkoumá každý znak v řetězci, ponechá pouze číselné hodnoty a sloučí je do jednoho výsledku.
Funkce SEQUENCE je dostupná pouze v Excel 365 a Excel 2021. Pokud používáte Excel 2019, použijte místo toho tento vzorec:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))Poznámka: Ve Excelu 2019 musí být vzorec zadán jako maticový vzorec. Po napsání vzorce stiskněte Ctrl + Shift + Enter místo pouhého Enter. Excel automaticky umístí složené závorky kolem vzorce v řádku vzorců.
Jakmile vzorec vrátí požadovaný výsledek, přetáhněte úchyt pro vyplnění dolů, aby se použil na zbývající buňky ve sloupci.
4. Použijte Power Query
Pokud pracujete s velkými datovými soubory nebo potřebujete pravidelně oddělovat čísla od textu, Power Query může tento proces automatizovat. Umožňuje vám vytvořit transformaci, kterou lze obnovit vždy, když se změní zdrojová data nebo jsou přidána nová data.
Vyberte buňky obsahující vaše data a přejděte na Data > Z tabulky/rozsahu. Po zobrazení výzvy klikněte na OK, aby se vybraný rozsah převedl na tabulku a otevřel se v editoru Power Query.
V editoru Power Query vyberte sloupec, který obsahuje smíšené řetězce. Poté přejděte na Přidat sloupec > Vlastní sloupec.
Chcete-li extrahovat pouze čísla, zadejte následující vzorec do pole Vlastní vzorec sloupce:
Text.Select([Column1], {"0".."9"})Nahraďte Column1 názvem vašeho sloupce, pokud je odlišný.
Klikněte na OK. Power Query vytvoří nový sloupec obsahující pouze číselné znaky z každého záznamu.
Pokud chcete extrahovat pouze textovou část, vytvořte další vlastní sloupec a použijte:
Text.Remove([Column1], {"0".."9"})
Jakmile budete spokojeni s výsledky, klikněte na Domů > Zavřít & Načíst pro načtení transformovaných dat zpět do Excelu.
Doufám, že to pro vás bude užitečné.
Jak oddělit čísla od textu?
V Excelu můžete oddělit čísla od textu pomocí Rychlého vyplnění, Text do sloupců, formulí, Power Query nebo vlastního VBA skriptu. Nejlepší metoda závisí na struktuře vašich dat a na tom, zda potřebujete, aby se výsledky aktualizovaly automaticky. Pro jednoduché jednorázové úkoly je Rychlé vyplnění často nejrychlejší možností.
Jak rozdělit 1 buňku na 4 v Excelu?
Excel nepodporuje rozdělení jediné buňky na více buněk stejným způsobem, jak to dělá Microsoft Word. Obsah buňky však můžete rozdělit do sousedních sloupců pomocí Data > Text do sloupců, Rychlé doplnění nebo funkce TEXTSPLIT (v Excelu pro Microsoft 365 a Excel pro web). Můžete také vložit další řádky nebo sloupce pro uložení oddělených dat.