Existuje mnoho způsobů, jak převést data zformátovaná jako text na skutečná data v Excelu a tento tutoriál si klade za cíl je všechny prozkoumat.
Když importujete data do Excelu, mohou mít mnoho různých forem, které Excel nerozpozná. Někdy, když jsou data importována z externího zdroje, přijdou ve formátu textu.
Je také důležité vědět, že aplikace Excel používá formáty data na základě nastavení oblasti systému. Když tedy do Excelu importujete data, která pocházejí z jiné země, Excel je nemusí rozpoznat a uložit je jako textové položky.
Pokud máte tento problém, existuje mnoho způsobů, které můžete použít k převodu textu na datum v Excelu, a my je všechny pokryjeme v tomto tutoriálu.
Metody pro převod textu na datum
Pokud váš list obsahuje data ve formátu textu namísto skutečných dat, není možné je použít v žádných výpočtech. Musíte je tedy převést zpět na skutečná data.
Pokud vidíte svá data zarovnaná doleva, znamená to, že jsou formátována jako text, protože texty jsou ve výchozím nastavení zarovnány doleva. A číslo a data jsou vždy zarovnány doprava.
Existuje několik různých způsobů, jak můžete převést text na datum v aplikaci Excel, jsou to:
- Možnost kontroly chyb
- Funkce Excel Text to Columns
- Najít a nahradit
- Vložit speciální nástroj
- Vzorec a funkce Excelu
Převést text na datum pomocí možnosti kontroly chyb
Excel má vestavěnou funkci kontroly chyb, která ve vašich datech odhalí některé zjevné chyby. Pokud najde nějakou chybu, zobrazí vám malý zelený trojúhelník (indikátor chyby) v levém horním rohu buňky, která obsahuje chybu. Pokud vyberete tuto buňku, objeví se výstražný znak se žlutým vykřičníkem. Když přesunete kurzor na tento znak, Excel vás bude informovat o možném problému s touto buňkou.
Když například do data zadáte rok ve dvoumístném formátu, Excel toto datum předpokládá jako text a uloží jej jako text. A pokud vyberete tuto buňku, objeví se vykřičník s upozorněním: ‚Tato buňka obsahuje řetězec data reprezentovaný pouze 2 číslicemi roku‘.
Pokud se ve vašich buňkách zobrazuje indikátor chyby, klikněte na vykřičník a zobrazí se několik možností, jak převést data ve formátu textu na skutečná data. Excel vám ukáže možnosti převodu na 19XX nebo 20XX (19XX pro 1915, 20XX pro 2015). Vyberte příslušnou možnost.
Poté bude text převeden do správného formátu data.
Jak povolit možnost kontroly chyb v aplikaci Excel
Obvykle je možnost Kontrola chyb v Excelu ve výchozím nastavení zapnutá. Pokud vám funkce kontroly chyb nefunguje, musíte povolit kontrolu chyb v aplikaci Excel.
Chcete-li to provést, klikněte na kartu „Soubor“ a v levém panelu vyberte „Možnosti“.
V okně Možnosti aplikace Excel klikněte na levém panelu na „Vzorce“ a na pravém panelu povolte „Povolit kontrolu chyb na pozadí“ v části Kontrola chyb. A zaškrtněte „Buňky obsahující roky reprezentované jako 2 číslice“ v části Pravidla kontroly chyb.
Převod textu na datum pomocí funkce Excel Text na sloupec
Text to Column je skvělá funkce v Excelu, která vám umožňuje rozdělit data do více sloupců, a také je to výkonný nástroj pro převod textových hodnot na hodnoty data. Tato metoda rozpozná několik různých formátů dat a převede je do správného formátu data.
Převod jednoduchých textových řetězců na data
Řekněme, že vaše data jsou formátována v textových řetězcích takto:
K rychlému přeformátování všech zpět na data můžete použít průvodce Text to Columns.
Nejprve vyberte rozsah textových položek, které chcete převést na data. Poté přejděte na kartu „Data“ na pásu karet a klikněte na možnost „Text do sloupců“ ve skupině Datové nástroje.
Zobrazí se průvodce Text to Columns. V kroku 1 průvodce Text do sloupce vyberte v části Původní typ dat možnost „Oddělený“ a klikněte na „Další“.
V kroku 2 zrušte zaškrtnutí všech polí „Oddělovače“ a klikněte na Další.
V posledním kroku průvodce vyberte ‚Datum‘ v části Formát dat sloupce a vyberte formát data z rozevíracího seznamu vedle ‚Data‘ a klikněte na tlačítko ‚Dokončit‘. V našem případě převádíme textová data reprezentovaná jako „01 02 1995“ (den měsíc rok), takže z rozevíracího seznamu „Datum:“ vybereme „DMY“.
Nyní Excel převede data textu na skutečná data a zobrazí je v buňkách zarovnaná doprava.
Poznámka: Než začnete používat funkci Text to Column, ujistěte se, že všechny vaše textové řetězce jsou ve stejném formátu, jinak nebudou texty převedeny. Pokud jsou například některá data vašeho textu ve formátu měsíc/den/rok (MDY), zatímco jiná jsou ve formátu den/měsíc/rok (DMY), a když v kroku 3 zvolíte „DMY“, dostanete nesprávné výsledky. Jak je znázorněno na obrázku níže.
Převod složitého textového řetězce na datum
Funkce Text to Columns se hodí, když chcete převést složité textové řetězce na data. Umožňuje vám pomocí oddělovačů určit, kde mají být vaše data rozdělena a zobrazena ve 2 nebo více sloupcích. A zkombinujte rozdělené části dat do celého data pomocí funkce DATE.
Pokud jsou například vaše data zobrazena ve vícedílných textových řetězcích, jako je tento:
Středa 1. února 2020
1. února 2020, 16:10
Pomocí průvodce Text do sloupce můžete oddělit informace o dni, datu a čase, které jsou odděleny čárkou, a zobrazit je ve více sloupcích.
Nejprve vyberte všechny textové řetězce, které chcete převést na data. Klikněte na tlačítko „Text do sloupců“ na kartě „Data“. V kroku 1 průvodce Text do sloupce vyberte v části Původní typ dat možnost „Oddělený“ a klikněte na „Další“.
V kroku 2 průvodce vyberte oddělovače, které vaše textové řetězce obsahují, a klikněte na „Další“. Naše vzorové textové řetězce oddělené čárkou a mezerou – „pondělí, 1. února 2015, 13:00“. Pro rozdělení textových řetězců do více sloupců bychom měli jako oddělovače zvolit „čárku“ a „mezery“.
V posledním kroku vyberte formát ‚Obecné‘ pro všechny sloupce v části Náhled dat. V poli ‚Cíl‘ zadejte, kam mají být sloupce vloženy, pokud tak neučiníte, přepíše se původní data. Pokud chcete nějakou část původních dat ignorovat, klikněte na ni v sekci Náhled dat a vyberte možnost ‚Neimportovat sloupec (přeskočit)‘. Poté klikněte na „Dokončit“.
Nyní jsou části dat (dny v týdnu, měsíc, rok, čas) rozděleny do sloupců B, C, D, E, F a G.
Poté zkombinujte části data dohromady pomocí vzorce DATUM, abyste získali celé datum.
Syntaxe funkce Excel DATE:
=DATE(rok,měsíc,den)V našem příkladu jsou části měsíce, dne a roku ve sloupcích C, D a E.
Funkce DATE rozpoznává pouze čísla, nikoli text. Protože naše měsíční hodnoty ve sloupci C jsou všechny textové řetězce, musíme je převést na čísla. K tomu je potřeba pomocí funkce MONTH změnit název měsíce na číslo měsíce.
Chcete-li převést název měsíce na číslo měsíce, použijte tuto funkci MONTH ve funkci DATE:
=MONTH(1&C1)Funkce MONTH přidá 1 do buňky C2, která obsahuje název měsíce, aby se název měsíce převedl na odpovídající číslo měsíce.
Toto je funkce DATE, kterou musíme použít ke kombinaci částí data z různých sloupců:
=DATUM(E1,MĚSÍC(1&C1);D1)
Nyní použijte úchyt v dolním rohu buňky vzorce a aplikujte vzorec na sloupec.
Převeďte text na datum pomocí metody Najít a nahradit
Tato metoda používá oddělovače ke změně formátu textu na data. Pokud jsou den, měsíc a rok v datech odděleny nějakým oddělovačem jiným než pomlčkou (-) nebo lomítkem (/), Excel je nerozpozná jako data a bude pokračovat a uloží je jako text.
Chcete-li tento problém vyřešit, použijte funkci Najít a nahradit. Změnou nestandardních oddělovačů období (.) pomocí lomítek (/) nebo pomlčky (-) Excel automaticky změní hodnoty na data.
Nejprve vyberte všechna textová data, která chcete převést na data. Na kartě ‚Domů‘ klikněte na tlačítko ‚Najít a vybrat‘ v pravém rohu pásu karet a vyberte ‚Nahradit‘. Případně stiskněte Ctrl+H otevřete dialogové okno Najít a nahradit.
V dialogovém okně Najít a nahradit zadejte oddělovač, který váš text obsahuje (v našem případě tečku (.) do pole „Najít co“ a lomítko (/) nebo pomlčku (-) do pole „Nahradit čím“). 2. Klepnutím na tlačítko 'Nahradit vše' nahraďte oddělovače a kliknutím na tlačítko 'Zavřít' zavřete okno.
Nyní Excel rozpozná, že vaše textové řetězce jsou nyní data, a automaticky je zformátuje jako data. Vaše data budou zarovnána vpravo, jak je uvedeno níže.
Převést text na datum pomocí speciálního nástroje pro vložení
Dalším rychlým a snadným způsobem, jak převést textové řetězce na data, je přidání 0 do textového řetězce pomocí speciální možnosti vložit. Přidáním nuly k hodnotě se text převede na sériové číslo data, které můžete formátovat jako datum.
Nejprve vyberte prázdnou buňku a zkopírujte ji (označte ji a stiskněte Ctrl + C kopírovat).
Poté vyberte buňky obsahující textová data, která chcete převést, klikněte pravým tlačítkem a vyberte možnost ‚Vložit jinak‘.
V dialogovém okně Vložit jinak vyberte ‚Vše‘ v části Vložit, v části Operace vyberte ‚Přidat‘ a klikněte na ‚OK‘.
Můžete také provádět další aritmetické operace odečítání/násobení/dělení hodnoty v cílové buňce s vloženou hodnotou (např. násobení buněk 1 nebo dělení 1 nebo odečítání nuly).
Když v Operation vyberete ‚Add‘, přidá ‚nulu‘ ke všem vybraným textovým datům, protože přidání ‚0‘ nezmění hodnoty, získáte sériové číslo pro každé datum. Nyní vše, co musíte udělat, je změnit formát buněk.
Vyberte sériová čísla a na kartě ‚Domů‘ klikněte na rozevírací seznam ‚Formát čísel‘ ve skupině Číslo. V rozevíracím seznamu vyberte možnost „Krátké datum“.
Jak můžete nyní vidět, čísla jsou formátována jako data a zarovnána doprava.
Převod textu na datum pomocí vzorců
K převodu textu na datum se primárně používají dvě funkce: DATEVALUE a VALUE.
Použití funkce Excel DATEVALUE
Funkce Excel DATEVALUE je jedním z nejjednodušších způsobů, jak převést datum reprezentované jako text na pořadové číslo data.
Syntaxe funkce DATEVALUE:
=DATEVALUE(date_text)Argument: text_data určuje textový řetězec, který chcete skrýt nebo odkazovat na buňku obsahující textová data.
vzorec:
=DATUMHODNOTA(A1)Následující obrázek ukazuje, jak funkce DATEVALUE zpracovává několik různých formátů data, které jsou uloženy jako text.
Máte sériová čísla data, nyní musíte na tato čísla použít formát data. Chcete-li to provést, vyberte buňky se sériovými čísly, poté přejděte na kartu ‚Domů‘ a v rozevíracím seznamu ‚Formát čísel‘ vyberte ‚Krátké datum‘.
Nyní máte svá formátovaná data ve sloupci C.
I když ve vašem textovém datu (A8) není žádná část roku, DATEVALUE použije aktuální rok z hodin vašeho počítače.
Funkce DATEVALUE převede pouze textové hodnoty, které vypadají jako datum. Neumí převést text, který se podobá číslu na datum, ani nemůže změnit číselnou hodnotu na datum, k tomu budete potřebovat funkci VALUE v Excelu.
Použití funkce Excel VALUE
Funkce Excel VALUE dokáže převést jakýkoli textový řetězec, který se podobá datu nebo číslu, na číselnou hodnotu, takže je opravdu užitečná, pokud jde o převod jakéhokoli čísla, nejen data.
Funkce VALUE:
=HODNOTA(text)text– textový řetězec, který chceme převést, nebo odkaz na buňku obsahující textový řetězec.
Příklad vzorce pro převod data textu:
=HODNOTA(A1)Níže uvedený vzorec HODNOTA může změnit libovolné textové řetězce, které vypadají jako datum, na číslo, jak je uvedeno níže.
Funkce VALUE však nepodporuje všechny typy hodnot data. Pokud například data používají desetinná místa (A11), vrátí #HODNOTA! chyba.
Jakmile budete mít sériové číslo svého data, budete muset naformátovat buňku se sériovým číslem data, aby vypadala jako datum, jak jsme to udělali pro funkci DATEVALUE. Chcete-li to provést, vyberte sériová čísla a vyberte možnost ‚Datum‘ z rozbalovací nabídky ‚Formát čísel‘ na kartě ‚Domů‘.
To je vše, toto je 5 různých způsobů, jak můžete převést data ve formátu textu na data v aplikaci Excel.