Istnieje wiele sposobów konwertowania dat sformatowanych jako tekst na rzeczywiste daty w programie Excel, a ten samouczek ma na celu zbadanie ich wszystkich.
Dane importowane do programu Excel mogą mieć wiele różnych form, których program Excel nie rozpoznaje. Czasami, gdy daty są importowane z zewnętrznego źródła, przychodzą sformatowane jako tekst.
Ważne jest również, aby wiedzieć, że program Excel stosuje formaty dat na podstawie ustawień regionu systemu. Dlatego podczas importowania do programu Excel danych pochodzących z innego kraju program Excel może ich nie rozpoznać i przechowywać jako wpisy tekstowe.
Jeśli masz ten problem, istnieje wiele sposobów konwersji tekstu na datę w programie Excel, a omówimy je wszystkie w tym samouczku.
Metody konwersji tekstu na datę
Jeśli arkusz zawiera daty sformatowane jako tekst zamiast rzeczywistych dat, nie można ich użyć w żadnych obliczeniach. Musisz więc przekonwertować je z powrotem na rzeczywiste daty.
Jeśli widzisz swoje daty wyrównane do lewej, oznacza to, że są one sformatowane jako tekst, ponieważ teksty są domyślnie wyrównane do lewej. A liczba i daty są zawsze wyrównane do prawej.
Istnieje kilka różnych sposobów konwertowania tekstu na datę w programie Excel, są to:
- Opcja sprawdzania błędów
- Funkcja tekstu do kolumn w programie Excel
- Znaleźć zamiennik
- Wklej narzędzie specjalne
- Formuła i funkcje programu Excel
Konwertuj tekst na datę za pomocą opcji sprawdzania błędów
Program Excel ma wbudowaną funkcję sprawdzania błędów, która wykrywa oczywiste błędy w danych. Jeśli znajdzie jakiś błąd, pokaże mały zielony trójkąt (wskaźnik błędu) w lewym górnym rogu komórki, która zawiera błąd. Jeśli wybierzesz tę komórkę, pojawi się znak ostrzegawczy z żółtym wykrzyknikiem. Gdy najedziesz kursorem na ten znak, program Excel poinformuje Cię o możliwym problemie z tą komórką.
Na przykład po wprowadzeniu roku w formacie dwucyfrowym w dacie program Excel przyjmuje tę datę jako tekst i przechowuje ją jako tekst. A jeśli wybierzesz tę komórkę, pojawi się wykrzyknik z ostrzeżeniem: „Ta komórka zawiera ciąg daty reprezentowany tylko przez 2 cyfry roku”.
Jeśli Twoje komórki pokazują ten wskaźnik błędu, kliknij wykrzyknik, a wyświetli się kilka opcji konwersji dat sformatowanych jako tekst na rzeczywiste daty. Excel pokaże Ci opcje konwersji do 19XX lub 20XX (19XX dla 1915, 20XX dla 2015). Wybierz odpowiednią opcję.
Następnie tekst zostanie skonwertowany do odpowiedniego formatu daty.
Jak włączyć opcję sprawdzania błędów w programie Excel?
Zwykle opcja Sprawdzanie błędów jest domyślnie włączona w programie Excel. Jeśli funkcja sprawdzania błędów nie działa, musisz włączyć funkcję sprawdzania błędów w programie Excel.
Aby to zrobić, kliknij zakładkę „Plik” i wybierz „Opcje” w lewym panelu.
W oknie Opcje programu Excel kliknij „Formuły” w lewym panelu, a w prawym panelu włącz opcję „Włącz sprawdzanie błędów w tle” w sekcji Sprawdzanie błędów. I zaznacz „Komórki zawierające lata reprezentowane jako 2 cyfry” w sekcji Zasady sprawdzania błędów.
Konwertuj tekst na datę za pomocą funkcji tekstu Excel na kolumnę
Tekst na kolumnę to świetna funkcja programu Excel, która umożliwia dzielenie danych na wiele kolumn, a także jest potężnym narzędziem do konwersji wartości tekstowych na wartości dat. Ta metoda rozpoznaje kilka różnych formatów danych i konwertuje je na odpowiedni format daty.
Konwersja prostych ciągów tekstowych na daty
Załóżmy, że Twoje daty są sformatowane w postaci ciągów tekstowych w następujący sposób:
Możesz użyć kreatora Tekst na kolumny, aby szybko przeformatować je z powrotem na daty.
Najpierw wybierz zakres wpisów tekstowych, które chcesz przekonwertować na daty. Następnie przejdź do zakładki „Dane” na Wstążce i kliknij opcję „Tekst do kolumn” w grupie Narzędzia danych.
Pojawi się kreator tekstu do kolumn. W kroku 1 kreatora Tekst do kolumny wybierz opcję „Rozdzielone” w obszarze Oryginalny typ danych i kliknij „Dalej”.
W kroku 2 odznacz wszystkie pola „Ograniczniki” i kliknij Dalej.
W ostatnim kroku kreatora wybierz „Data” w obszarze Format danych kolumny i wybierz format daty z listy rozwijanej obok „Daty”, a następnie kliknij przycisk „Zakończ”. W naszym przypadku konwertujemy daty tekstowe reprezentowane jako „01 02 1995” (dzień miesiąc rok), więc wybieramy „DMY” z rozwijanej listy „Data:”.
Teraz program Excel konwertuje daty tekstowe na rzeczywiste daty i wyświetla je wyrównane do prawej w komórkach.
Notatka: Zanim zaczniesz korzystać z funkcji Tekst na kolumnę, upewnij się, że wszystkie ciągi tekstowe są w identycznym formacie, w przeciwnym razie teksty nie zostaną przekonwertowane. Na przykład, jeśli niektóre daty tekstowe są sformatowane w formacie miesiąc/dzień/rok (MDY), podczas gdy inne mają format dzień/miesiąc/rok (DMY), a gdy wybierzesz „DMY” w kroku 3, otrzymasz nieprawidłowe wyniki. Jak pokazano na poniższym obrazku.
Konwersja złożonego ciągu tekstowego na datę
Funkcja tekstu do kolumn przydaje się, gdy chcesz przekonwertować złożone ciągi tekstowe na daty. Pozwala na użycie ograniczników do określenia, gdzie dane powinny być podzielone i wyświetlane w 2 lub więcej kolumnach. I połącz podzielone części dat w całą datę za pomocą funkcji DATE.
Na przykład, jeśli daty są wyświetlane w wieloczęściowych ciągach tekstowych, takich jak ten:
środa, 01.02.2020
01.02.2020, godz. 16.10
Za pomocą kreatora Tekst do kolumn można oddzielić informacje o dniu, dacie i godzinie oddzielone przecinkiem i wyświetlić je w wielu kolumnach.
Najpierw zaznacz wszystkie ciągi tekstowe, które chcesz przekonwertować na daty. Kliknij przycisk „Tekst do kolumn” na karcie „Dane”. W kroku 1 kreatora Tekst do kolumny wybierz opcję „Rozdzielone” w obszarze Oryginalny typ danych i kliknij „Dalej”.
W kroku 2 kreatora wybierz ograniczniki zawarte w ciągach tekstowych i kliknij „Dalej”. Nasze przykładowe ciągi tekstowe oddzielone przecinkiem i spacją – „Poniedziałek, 1 lutego 2015 r., 13:00”. Powinniśmy wybrać „przecinek” i „spacje” jako ograniczniki, aby podzielić ciągi tekstowe na wiele kolumn.
W ostatnim kroku wybierz format „Ogólne” dla wszystkich kolumn w sekcji Podgląd danych. Określ, gdzie kolumny powinny być wstawione w polu „Destination”, jeśli tego nie zrobisz, nadpisze oryginalne dane. Jeśli chcesz zignorować jakąś część oryginalnych danych, kliknij ją w sekcji Podgląd danych i wybierz opcję „Nie importuj kolumny (pomiń)”. Następnie kliknij „Zakończ”.
Teraz części dat (dni tygodnia, miesiąca, roku, czasu) są podzielone na kolumny B, C, D, E, F i G.
Następnie połącz ze sobą części daty za pomocą formuły DATE, aby uzyskać całą datę.
Składnia funkcji Excel DATA:
=DATA(rok;miesiąc;dzień)W naszym przykładzie części miesiąca, dnia i roku znajdują się odpowiednio w kolumnach C, D i E.
Funkcja DATA rozpoznaje tylko liczby, a nie tekst. Ponieważ wszystkie nasze wartości miesiąca w kolumnie C to ciągi tekstowe, musimy je przekonwertować na liczby. W tym celu należy za pomocą funkcji MIESIĄC zmienić nazwę miesiąca na numer miesiąca.
Aby przekonwertować nazwę miesiąca na numer miesiąca, użyj funkcji MIESIĄC w funkcji DATA:
=MIESIĄC(1&K1)Funkcja MIESIĄC dodaje 1 do komórki C2, która zawiera nazwę miesiąca, aby przekonwertować nazwę miesiąca na odpowiedni numer miesiąca.
To jest funkcja DATE, której potrzebujemy do łączenia części dat z różnych kolumn:
=DATA(E1MIESIĄC(1&C1);D1)
Teraz użyj uchwytu wypełniania w dolnym rogu komórki z formułą i zastosuj formułę do kolumny.
Konwertuj tekst na datę za pomocą metody Znajdź i zamień
Ta metoda używa ograniczników do zmiany formatu tekstu na daty. Jeśli dzień, miesiąc i rok w datach są oddzielone jakimś ogranicznikiem innym niż myślnik (-) lub ukośnik (/), program Excel nie rozpozna ich jako dat i zapisze je jako tekst.
Aby rozwiązać ten problem, użyj funkcji Znajdź i zamień. Zmieniając niestandardowe ograniczniki kropki (.) za pomocą ukośników (/) lub myślników (-), Excel automatycznie zmieni wartości na daty.
Najpierw wybierz wszystkie daty tekstowe, które chcesz przekonwertować na daty. Na karcie „Strona główna” kliknij przycisk „Znajdź i wybierz” w prawym górnym rogu wstążki i wybierz „Zamień”. Ewentualnie naciśnij Ctrl+H aby otworzyć okno dialogowe Znajdź i zamień.
W oknie dialogowym Znajdowanie i zamienianie wpisz ogranicznik, który zawiera tekst (w naszym przypadku kropka (.) w polu „Znajdź” i ukośnik (/) lub myślnik (-) w polu „Zamień na” Kliknij przycisk „Zamień wszystko”, aby zastąpić ograniczniki i kliknij „Zamknij”, aby zamknąć okno.
Teraz program Excel rozpoznaje, że ciągi tekstowe są teraz datami i automatycznie formatuje je jako daty. Twoje daty zostaną wyrównane, jak pokazano poniżej.
Konwertuj tekst na datę za pomocą specjalnego narzędzia wklejania
Innym szybkim i łatwym sposobem konwersji ciągów tekstowych na daty jest dodanie 0 do ciągu tekstowego za pomocą specjalnej opcji wklejania. Dodanie zera do wartości konwertuje tekst na numer seryjny daty, który można sformatować jako datę.
Najpierw wybierz pustą komórkę i skopiuj ją (zaznacz ją i naciśnij Ctrl + C kopiować).
Następnie wybierz komórki zawierające daty tekstu, które chcesz przekonwertować, kliknij prawym przyciskiem myszy i wybierz opcję „Wklej specjalnie”.
W oknie dialogowym Wklej specjalnie wybierz „Wszystkie” w sekcji Wklej, wybierz „Dodaj” w sekcji Operacja i kliknij „OK”.
Możesz także wykonywać inne operacje arytmetyczne, odejmować/mnożyć/dzielić wartość w komórce docelowej z wklejoną wartością (np. pomnożyć komórki przez 1 lub podzielić przez 1 lub odjąć zero).
Gdy wybierzesz „Dodaj” w operacji, dodaje „zero” do wszystkich wybranych dat tekstowych, ponieważ dodanie „0” nie zmienia wartości, otrzymasz numer seryjny dla każdej daty. Teraz wszystko, co musisz zrobić, to zmienić format komórek.
Wybierz numery seryjne i na karcie „Strona główna” kliknij listę rozwijaną „Format numeru” w grupie Numer. Wybierz opcję „Data krótka” z menu rozwijanego.
Jak widać teraz liczby są sformatowane jako daty i wyrównane do prawej.
Konwertuj tekst na datę za pomocą formuł
Istnieją dwie funkcje używane głównie do konwersji tekstu na datę: DATA.WARTOŚĆ i WARTOŚĆ.
Korzystanie z funkcji DATEVALUE w programie Excel
Funkcja Excel DATA.WARTOŚĆ jest jednym z najłatwiejszych sposobów konwersji daty reprezentowanej jako tekst na numer seryjny daty.
Składnia funkcji DATA.WARTOŚĆ:
=DATA.WARTOŚĆ(data_tekst)Argument: data_tekst określa ciąg tekstowy, który chcesz ukryć, lub odwołanie do komórki zawierającej daty tekstowe.
Formuła:
=DATA.WARTOŚĆ(A1)Poniższy rysunek ilustruje, jak funkcja DATA.WARTOŚĆ obsługuje kilka różnych formatów daty, które są przechowywane jako tekst.
Masz numery seryjne dat, teraz musisz zastosować format daty do tych liczb. W tym celu zaznacz komórki z numerami seryjnymi, a następnie przejdź do zakładki „Strona główna” i wybierz „Data skrócona” z rozwijanej listy „Format liczb”.
Teraz masz sformatowane daty w kolumnie C.
Nawet jeśli w dacie tekstowej (A8) nie ma części dotyczącej roku, DATA.WARTOŚĆ użyje bieżącego roku z zegara komputera.
Funkcja DATA.WARTOŚĆ konwertuje tylko wartości tekstowe, które wyglądają jak data. Nie może przekonwertować tekstu, który przypomina liczbę do tej pory, ani nie może zmienić dotychczasowej wartości liczbowej, do tego będziesz potrzebować funkcji WARTOŚĆ programu Excel.
Korzystanie z funkcji Excel WARTOŚĆ
Funkcja WARTOŚĆ programu Excel jest w stanie przekonwertować dowolny ciąg tekstowy przypominający datę lub liczbę na wartość liczbową, więc jest bardzo pomocna w przypadku konwersji dowolnej liczby, a nie tylko dat.
Funkcja WARTOŚĆ:
=WARTOŚĆ(tekst)tekst– ciąg tekstowy, który chcemy przekonwertować lub odwołanie do komórki zawierającej ciąg tekstowy.
Przykładowa formuła do konwersji daty tekstu:
=WARTOŚĆ(A1)Poniższa formuła WARTOŚĆ może zmienić dowolne ciągi tekstowe, które wyglądają jak data, na liczbę, jak pokazano poniżej.
Jednak funkcja WARTOŚĆ nie obsługuje wszystkich typów wartości dat. Na przykład, jeśli daty używają miejsc dziesiętnych (A11), zwróci #ARG! błąd.
Po uzyskaniu numeru seryjnego daty będziesz musiał sformatować komórkę za pomocą numeru seryjnego daty, aby wyglądała jak data, tak jak zrobiliśmy to dla funkcji DATA.WARTOŚĆ. Aby to zrobić, wybierz numery seryjne i wybierz opcję „Data” z menu rozwijanego „Format numeru” w zakładce „Strona główna”.
To wszystko, oto 5 różnych sposobów konwertowania dat sformatowanych jako tekst na daty w programie Excel.