Obecný vzorec
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
souhrn
Chcete-li převést text v nerozpoznaném formátu data na správné datum aplikace Excel, můžete text analyzovat a sestavit správné datum pomocí vzorce založeného na několika funkcích: DATE, LEFT, MID a RIGHT. V zobrazeném příkladu je vzorec v C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Tento vzorec extrahuje hodnoty roku, měsíce a dne samostatně a k jejich sestavení do data 24. října 2000 používá funkci DATE.
Pozadí
Když pracujete s daty z jiného systému, můžete narazit na situaci, kdy data nejsou správně rozpoznána Excelem, který místo toho zachází s daty jako s textem. Můžete mít například takové textové hodnoty:
| Text | Zastoupené datum |
| 20001024 | 24. října 2000 |
| 20050701 | 1. července 2011 |
| 19980424 | 24.dubna 1998 |
| 28.02.2014 | 28. února 2014 |
Když Excel vyhodnotil hodnotu data jako text, jednou z možností je použít vzorec k analýze textu na jeho součásti (rok, měsíc, den) a pomocí nich vytvořit datum pomocí funkce DATE. Jak je uvedeno výše, doporučuji vám nejprve vyzkoušet níže uvedená řešení (přidání nuly a použití textu do sloupců), než použijete vzorec. Obě řešení jsou rychlejší a vyžadují menší úsilí.
Vysvětlení
Funkce DATE vytvoří platné datum pomocí tří argumentů: rok, měsíc a den:
=DATE(year,month,day)
V buňce C6 používáme funkce LEFT, MID a RIGHT k extrakci každé z těchto komponent z textového řetězce a výsledky vkládáme do funkce DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkce LEFT extrahuje nejvíce 4 znaky za rok, funkce MID extrahuje znaky na pozicích 5–6 za měsíc a funkce RIGHT extrahuje 2 dny zcela vpravo jako den. Každý výsledek je vrácen přímo do funkce DATE. Konečným výsledkem je správné datum aplikace Excel, které lze formátovat libovolným způsobem.
Tento přístup lze podle potřeby upravit. Například nerozpoznaný formát data v řádku 8 je dd.mm.rrrr a vzorec v C8 je:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Dlouhý text
Někdy můžete mít data v delší formě, jako je „11. dubna 2020 08:43:13“, která Excel nerozpozná správně. V tomto případě můžete být schopni upravit řetězec způsobem, který umožňuje aplikaci Excel správně rozpoznat datum pomocí funkce SUBSTITUTE. Následující vzorec nahradí druhou instanci mezery („“) čárkou a mezerou („,“):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Jakmile za název měsíce přidáme čárku, Excel porozumí datu, ale stále potřebuje trochu „nakopnout“. Proto na konci přidáme nulu. Matematická operace způsobí, že se Excel pokusí převést řetězec na číslo. Pokud bude úspěšný, bude mít za následek platné datum aplikace Excel. Pro správné zobrazení data možná budete muset použít formátování čísla data.
Bez vzorců
Než použijete vzorec k ruční analýze a sestavení data z textu, vyzkoušejte některou z níže uvedených oprav. První možnost pomocí matematické operace trochu „postrčí“ Excel a donutí ji, aby se pokusila vyhodnotit text jako číslo. Protože data aplikace Excel jsou ve skutečnosti čísla, často to může stačit. Pokud operace proběhne úspěšně, budete možná muset použít formát data.
Chcete-li opravit data, přidejte nulu
Někdy se setkáte s daty v textovém formátu, který by měl Excel rozpoznat. V takovém případě budete moci vynutit Excel, aby převedl textové hodnoty na data přidáním nuly k hodnotě. Když přidáte nulu, Excel se pokusí vynutit textové hodnoty na čísla. Jelikož jsou data jen čísla, je tento trik skvělým způsobem, jak převést data v textovém formátu, kterému by Excel měl opravdu rozumět.
Chcete-li převést data na místě přidáním nuly, zkuste Vložit jinak:
- Do nepoužité buňky zadejte nulu (0) a zkopírujte ji do schránky
- Vyberte problematická data
- Vložit jinak> Hodnoty> Přidat
- Použít formát data (v případě potřeby)
Do vzorce, jako je tento, můžete také přidat nulu:
=A1+0
kde A1 obsahuje nerozpoznané datum.
Chcete-li opravit data, odešlete text do sloupců
Další způsob, jak přimět Excel k rozpoznávání dat, je použití funkce převodu textu na sloupce:
Vyberte sloupec s daty a poté zkuste Data> Text na sloupce> Opraveno> Dokončit
Pokud Excel data rozpozná, opraví je všechny v jednom kroku.
