Számos módja van a szövegként formázott dátumok tényleges dátumokká való konvertálására az Excelben, és ez az oktatóanyag ezek mindegyikét feltárja.
Amikor adatokat importál az Excelbe, azok sokféle formában jelenhetnek meg, amelyeket az Excel nem ismer fel. Néha, amikor a dátumokat külső forrásból importálják, akkor szövegként formátumban jelennek meg.
Azt is fontos tudni, hogy az Excel dátumformátumokat alkalmaz a rendszerrégió beállításai alapján. Így amikor más országból származó adatokat importál az Excelbe, előfordulhat, hogy az Excel nem ismeri fel és szöveges bejegyzésként tárolja azokat.
Ha ezt a problémát tapasztalja, sokféleképpen konvertálhatja szöveget dátummá az Excelben, és ebben az oktatóanyagban mindegyikre kitérünk.
Szöveg dátummá konvertálásának módszerei
Ha a munkalap szövegként formázott dátumokat tartalmaz, nem tényleges dátumokat, akkor nem használható fel semmilyen számításban. Tehát vissza kell konvertálnia őket tényleges dátumokra.
Ha a dátumokat balra igazítva látja, az azt jelenti, hogy szövegként vannak formázva, mert a szövegek alapértelmezés szerint balra vannak igazítva. És a számok és a dátumok mindig jobbra vannak igazítva.
Néhány különböző módon konvertálhat szöveget dátummá az Excelben, ezek a következők:
- Hibaellenőrzés opció
- Az Excel szövegből oszlopokba funkció
- Keresés és csere
- Beillesztés Speciális eszköz
- Excel képlet és függvények
Konvertálja a szöveget dátummá a Hibaellenőrző opció segítségével
Az Excel beépített hibaellenőrző funkcióval rendelkezik, amely néhány nyilvánvaló hibát észlel az adatokban. Ha hibát talál, egy kis zöld háromszöget (hibajelzőt) jelenít meg a hibát tartalmazó cella bal felső sarkában. Ha ezt a cellát választja, egy figyelmeztető jel jelenik meg sárga felkiáltójellel. Ha a kurzort a jel fölé viszi, az Excel tájékoztatja Önt az adott cellával kapcsolatos lehetséges problémáról.
Ha például az évet kétjegyű formátumban adja meg a dátumban, az Excel ezt a dátumot szövegként veszi fel, és szövegként tárolja. És ha kiválasztja azt a cellát, egy felkiáltójel jelenik meg figyelmeztetéssel: "Ez a cella dátum karakterláncot tartalmaz, amely az évből csak 2 számjegyből áll".
Ha a celláiban ez a hibajelzés látható, kattintson a felkiáltójelre, és megjelenik néhány lehetőség a szövegként formázott dátumok tényleges dátumokká való konvertálására. Az Excel megmutatja a 19XX vagy 20XX (19XX 1915, 20XX 2015) konvertálási lehetőségeket. Válassza ki a megfelelő opciót.
Ezután a szöveget a megfelelő dátumformátumba konvertálja.
A hibaellenőrzési opció engedélyezése az Excelben
Általában a Hibaellenőrzés opció alapértelmezés szerint be van kapcsolva az Excelben. Ha a hibaellenőrzési funkció nem működik az Ön számára, engedélyeznie kell a Hibaellenőrzést az Excelben.
Ehhez kattintson a „Fájl” fülre, és a bal oldali panelen válassza az „Opciók” lehetőséget.
Az Excel Beállítások ablakában kattintson a „Képletek” elemre a bal oldali panelen, és a jobb oldali panelen engedélyezze a „Háttérhiba-ellenőrzés engedélyezése” lehetőséget a Hibaellenőrzés részben. És jelölje be a „2 számjegyű éveket tartalmazó cellák” lehetőséget a Hibaellenőrzési szabályok részben.
Szöveg konvertálása dátummá az Excel Szöveg oszlop funkciójával
A Text to Column egy nagyszerű funkció az Excelben, amely lehetővé teszi az adatok több oszlopra történő felosztását, valamint hatékony eszköz a szöveges értékek dátumértékekké alakításához. Ez a módszer számos különböző adatformátumot felismer, és megfelelő dátumformátumra konvertálja azokat.
Egyszerű szöveges karakterláncok konvertálása dátumokká
Tegyük fel, hogy a dátumok a következő szöveges karakterláncokban vannak formázva:
A Szöveg oszlopokba varázsló segítségével gyorsan újraformázhatja őket a dátumokra.
Először válassza ki a dátummá konvertálni kívánt szöveges bejegyzések tartományát. Ezután lépjen az „Adatok” fülre a szalagon, és kattintson a „Szöveg az oszlopokba” lehetőségre az Adateszközök csoportban.
Megjelenik a Szöveg oszlopokba varázsló. A Szöveg oszlopba varázsló 1. lépésében válassza a „Határozott” lehetőséget az Eredeti adattípus alatt, majd kattintson a „Tovább” gombra.
A 2. lépésben törölje az összes „Határoló” jelölőnégyzet bejelölését, és kattintson a Tovább gombra.
A varázsló utolsó lépésében válassza ki a „Dátum” lehetőséget az Oszlop adatformátum alatt, és válassza ki a dátumformátumot a „Dátumok” melletti legördülő listából, majd kattintson a „Befejezés” gombra. Esetünkben az „1995. 01. 02.”-ként ábrázolt szöveges dátumokat konvertáljuk (év hónap nap), ezért a „Dátum:” legördülő listából a „DMY”-t választjuk.
Az Excel most tényleges dátumokká konvertálja a szöveges dátumokat, és jobbra igazítva jeleníti meg őket a cellákban.
Jegyzet: A Szövegből oszlopba funkció használatának megkezdése előtt győződjön meg arról, hogy az összes szöveges karakterlánc azonos formátumban van, különben a szövegek nem lesznek konvertálva. Például, ha a szöveges dátumok egy része hónap/nap/év (MDY), míg mások nap/hónap/év (DMY) formátumúak, és ha a 3. lépésben a „DMY” lehetőséget választja, hibás eredményeket kaphat. Ahogy az alábbi képen is látható.
Összetett szöveges karakterlánc átalakítása dátummá
A Szöveg oszlopokká funkció akkor hasznos, ha összetett szöveges karakterláncokat szeretne dátummá alakítani. Lehetővé teszi a határolók használatát annak meghatározására, hogy az adatokat hol kell felosztani és 2 vagy több oszlopban megjeleníteni. És egyesítse a dátumok felosztott részeit teljes dátummá a DÁTUM funkció segítségével.
Például, ha a dátumok több részből álló szöveges karakterláncokban jelennek meg, például:
2020. február 01., szerda
2020. február 01., 16.10
A Szöveg oszlopba varázsló segítségével elválaszthatja a vesszővel elválasztott nap-, dátum- és időadatokat, és megjelenítheti azokat több oszlopban.
Először válassza ki az összes szöveges karakterláncot, amelyet dátummá szeretne konvertálni. Kattintson a „Szöveg az oszlopokba” gombra az „Adatok” lapon. A Szöveg oszlopba varázsló 1. lépésében válassza a „Határozott” lehetőséget az Eredeti adattípus alatt, majd kattintson a „Tovább” gombra.
A varázsló 2. lépésében válassza ki a szöveges karakterláncok elválasztójeleit, és kattintson a „Tovább” gombra. Példa szöveges karakterláncaink vesszővel és szóközzel elválasztva – „2015. február 01. hétfő, 13:00”. A „vesszőt” és a „szóközt” kell választanunk elválasztóként, hogy a szöveges karakterláncokat több oszlopra bontsuk.
Az utolsó lépésben válassza ki az „Általános” formátumot az összes oszlophoz az Adat-előnézet szakaszban. Adja meg, hogy a „Cél” mezőben hova kell beszúrni az oszlopokat, ha nem, akkor felülírja az eredeti adatokat. Ha figyelmen kívül szeretné hagyni az eredeti adatok egy részét, kattintson rá az Adatok előnézete részben, és válassza a „Ne importáljon oszlopot (kihagyás)” lehetőséget. Ezután kattintson a „Befejezés” gombra.
Most a dátumok részei (hét napjai, hónap, év, idő) a B, C, D, E, F és G oszlopokban vannak felosztva.
Ezután kombinálja a dátum részeket egy DATE képlet segítségével, hogy megkapja a teljes dátumot.
Az Excel DATE függvényének szintaxisa:
=DATE(év,hónap,nap)Példánkban a hónap, nap és év rész a C, D és E oszlopban található.
A DÁTUM függvény csak számokat ismer fel, szöveget nem. Mivel a C oszlopban szereplő havi értékeink mind szöveges karakterláncok, ezeket számokká kell konvertálnunk. Ehhez a HÓNAP funkcióval módosítani kell a hónap nevét a hónap számára.
Ha egy hónap nevét hónap számmá szeretné konvertálni, használja ezt a HÓNAP függvényt a DÁTUM függvényen belül:
=HÓNAP(1&C1)A HÓNAP függvény hozzáad 1-et a hónap nevét tartalmazó C2 cellához, hogy a hónap nevét a megfelelő hónapszámmá alakítsa.
Ezt a DÁTUM függvényt kell használnunk a különböző oszlopokból származó dátumrészek kombinálásához:
=DÁTUM(E1,HÓNAP(1&C1),D1)
Most használja a képletcella alsó sarkában található kitöltő fogantyút, és alkalmazza a képletet az oszlopra.
Szöveg konvertálása dátummá a Keresés és csere módszerrel
Ez a módszer határolókkal módosítja a szöveg formátumát dátumra. Ha a dátumokban a napot, a hónapot és az évet nem kötőjel (-) vagy perjel (/) választja el, az Excel nem ismeri fel dátumként, hanem szövegként tárolja őket.
A probléma megoldásához használja a Keresés és csere funkciót. A nem szabványos időszakhatárolók (.) perjelekkel (/) vagy kötőjelekkel (-) történő megváltoztatásával az Excel automatikusan dátumra módosítja az értékeket.
Először válassza ki az összes dátumot konvertálni kívánt szöveges dátumot. A „Kezdőlap” lapon kattintson a „Keresés és kijelölés” gombra a szalag jobb szélső sarkában, és válassza a „Csere” lehetőséget. Alternatív megoldásként nyomja meg a gombot Ctrl+H a Keresés és csere párbeszédpanel megnyitásához.
A Keresés és csere párbeszédpanelen írja be a szövegben található határolót (esetünkben pont (.) a „Keressen mit” mezőbe, és egy perjelet (/) vagy kötőjelet (-) a „Csere erre” mezőbe. Kattintson a "Minden cseréje" gombra a határolók lecseréléséhez, majd kattintson a "Bezárás" gombra az ablak bezárásához.
Az Excel felismeri, hogy a szöveges karakterláncok mostantól dátumok, és automatikusan dátumként formázza őket. A dátumok az alábbiak szerint igazodnak.
Konvertálja a szöveget dátummá a Paste speciális eszközzel
Egy másik gyors és egyszerű módja a szöveges karakterláncok dátummá alakításának, ha a speciális beillesztési opcióval 0-t adunk a szöveghez. A nulla érték hozzáadása a szöveget a dátum sorozatszámává alakítja, amelyet dátumként formázhat.
Először jelöljön ki egy üres cellát, és másolja ki (jelölje ki és nyomja meg a Ctrl + C másolni).
Ezután válassza ki a konvertálni kívánt szöveges dátumokat tartalmazó cellákat, kattintson a jobb gombbal, és válassza a „Speciális beillesztés” lehetőséget.
A Speciális beillesztés párbeszédpanel Beillesztés szakaszában válassza az „Összes” lehetőséget, a Művelet részben válassza a „Hozzáadás” lehetőséget, és kattintson az „OK” gombra.
Más aritmetikai műveleteket is végrehajthat, a célcellában lévő érték kivonása/szorzása/osztása a beillesztett értékkel (például cellák szorzása 1-gyel, osztás 1-gyel vagy nulla kivonása).
Ha a Műveletben a „Hozzáadás” lehetőséget választja, akkor az összes kiválasztott szöveges dátumhoz „nullát” ad, mivel a „0” hozzáadása nem változtatja meg az értékeket, minden dátumhoz megkapja a sorozatszámot. Most már csak a cellák formátumát kell megváltoztatnia.
Válassza ki a sorozatszámokat, és a „Kezdőlap” fülön kattintson a „Számformátum” legördülő listára a Szám csoportban. A legördülő menüből válassza a „Rövid dátum” lehetőséget.
Mint látható, a számok dátumként vannak formázva és jobbra igazítva.
Konvertálja a szöveget dátummá képletek segítségével
A szöveg dátummá alakítására elsősorban két függvényt használnak: DATEVALUE és VALUE.
Az Excel DATEVALUE függvényének használata
Az Excel DÁTUMÉRTÉK funkciója az egyik legegyszerűbb módja a szövegként ábrázolt dátum dátum sorozatszámává alakításának.
A DATEVALUE függvény szintaxisa:
=DATEVALUE(dátum_szöveg)Érv: dátum_szöveg Megadja azt a szöveges karakterláncot, amelyet el kíván takarni, vagy hivatkozni szeretne a szöveges dátumokat tartalmazó cellára.
A képlet:
=DÁTUMÉRTÉK(A1)A következő kép azt szemlélteti, hogy a DATEVALUE függvény hogyan kezel néhány különböző, szövegként tárolt dátumformátumot.
Megkapta a dátum sorozatszámait, most a dátum formátumot kell alkalmaznia ezekre a számokra. Ehhez válassza ki a sorszámmal rendelkező cellákat, majd lépjen a „Kezdőlap” fülre, és válassza a „Rövid dátum” lehetőséget a „Számformátum” legördülő listából.
Most a formázott dátumok a C oszlopban vannak.
Még akkor is, ha a szöveges dátumban (A8) nincs év rész, a DATEVALUE a számítógép órájának aktuális évet fogja használni.
A DATEVALUE függvény csak azokat a szöveges értékeket konvertálja, amelyek dátumnak tűnnek. Nem tud dátummá alakítani egy számra emlékeztető szöveget, és nem tud dátumra módosítani egy számértéket sem, ehhez az Excel ÉRTÉK funkciójára lesz szükség.
Excel VALUE függvény használata
Az Excel ÉRTÉK funkciója képes bármilyen dátumra vagy számra emlékeztető szöveget numerikus értékké konvertálni, így nagyon hasznos, ha bármilyen számot konvertál, nem csak dátumokat.
Az ÉRTÉK függvény:
=ÉRTÉK(szöveg)szöveg– a konvertálni kívánt szöveges karakterlánc vagy hivatkozás a szöveges karakterláncot tartalmazó cellára.
Példaképlet a szöveg dátumának konvertálásához:
=ÉRTÉK(A1)Az alábbi VALUE képlet a dátumnak tűnő szöveges karakterláncokat számmá változtathatja, az alábbiak szerint.
Az ÉRTÉK függvény azonban nem támogatja minden típusú dátumértéket. Például, ha a dátumok tizedesjegyeket használnak (A11), akkor az #ÉRTÉK! hiba.
Miután megvan a dátum sorozatszáma, formáznia kell a cellát a dátum sorozatszámával, hogy dátumnak tűnjön, ahogyan azt a DATEVALUE függvénynél tettük. Ehhez válassza ki a sorozatszámokat, és válassza a „Dátum” opciót a „Kezdőlap” fül „Számformátum” legördülő menüjéből.
Ez az az 5 különböző mód, amellyel a szövegként formázott dátumokat dátumokká konvertálhatja az Excelben.