Formuła programu Excel: Konwertuj tekst na datę -

Spisie treści

Formuła ogólna

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))

Podsumowanie

Aby przekonwertować tekst w nierozpoznanym formacie daty na prawidłową datę w programie Excel, można przeanalizować tekst i zebrać odpowiednią datę za pomocą formuły opartej na kilku funkcjach: DATA, LEWO, ŚRODEK i PRAWO. W pokazanym przykładzie formuła w C6 to:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Ta formuła wyodrębnia osobno wartości roku, miesiąca i dnia i używa funkcji DATA, aby zebrać je w datę 24 października 2000 r.

tło

Podczas pracy z danymi z innego systemu możesz napotkać sytuację, w której daty nie są prawidłowo rozpoznawane przez program Excel, który zamiast tego traktuje daty jak tekst. Na przykład możesz mieć takie wartości tekstowe:

Tekst Przedstawiona data
20001024 24 października 2000
20050701 1 lipca 2011 5
19980424 24 kwietnia 1998
28.02.2014 28 lutego 2014

Gdy program Excel ocenił wartość daty jako tekst, jedną z opcji jest użycie formuły do ​​przeanalizowania tekstu na jego składniki (rok, miesiąc, dzień) i użycie ich do utworzenia daty za pomocą funkcji DATA. Jak wspomniano powyżej, zalecamy najpierw wypróbowanie poniższych rozwiązań (dodanie zera i użycie tekstu w kolumnach) przed użyciem formuły. Oba rozwiązania są szybsze i wymagają mniejszego wysiłku.

Wyjaśnienie

Funkcja DATA tworzy prawidłową datę przy użyciu trzech argumentów: roku, miesiąca i dnia:

=DATE(year,month,day)

W komórce C6 używamy funkcji LEFT, MID i RIGHT, aby wyodrębnić każdy z tych składników z ciągu tekstowego i przekazać wyniki do funkcji DATE:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Funkcja LEFT wyodrębnia 4 skrajne lewe znaki na rok, funkcja MID wyodrębnia znaki na pozycjach 5-6 dla miesiąca, a funkcja RIGHT wyodrębnia 2 skrajne prawe znaki jako dzień. Każdy wynik jest zwracany bezpośrednio do funkcji DATA. Ostatecznym wynikiem jest odpowiednia data w Excelu, którą można sformatować w dowolny sposób.

To podejście można dostosować w razie potrzeby. Na przykład nierozpoznany format daty w wierszu 8 to dd.mm.rrrr, a formuła w C8 to:

=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))

Długi tekst

Czasami możesz mieć daty w dłuższej formie, na przykład „Apr 11 2020 08:43:13”, których program Excel nie rozpoznaje poprawnie. W takim przypadku możesz dostosować ciąg w sposób, który umożliwi programowi Excel prawidłowe rozpoznanie daty za pomocą funkcji PODSTAW. Poniższy wzór zastępuje drugie wystąpienie spacji („”) przecinkiem i spacją („,”):

=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month

Gdy dodamy przecinek po nazwie miesiąca, program Excel zrozumie datę, ale nadal będzie wymagał małego „kopa”. Dlatego na końcu dodajemy zero. Operacja matematyczna powoduje, że program Excel próbuje przekonwertować ciąg na liczbę. Jeśli się powiedzie, da to prawidłową datę w Excelu. Pamiętaj, że może być konieczne zastosowanie formatowania numeru daty, aby data była wyświetlana poprawnie.

Bez formuł

Zanim użyjesz formuły do ​​ręcznego przeanalizowania i skonstruowania daty na podstawie tekstu, wypróbuj jedną z poniższych poprawek. Pierwsza opcja wykorzystuje operację matematyczną, aby nieco „podeprzeć” Excela i zmusić go do próby oceny tekstu jako liczby. Ponieważ daty w programie Excel są w rzeczywistości liczbami, często może to załatwić sprawę. Może być konieczne zastosowanie formatu daty, jeśli operacje się powiodą.

Dodaj zero, aby naprawić daty

Czasami napotkasz daty w formacie tekstowym, który powinien rozpoznawać Excel. W takim przypadku możesz zmusić program Excel do konwersji wartości tekstowych na daty, dodając zero do wartości. Po dodaniu zera program Excel spróbuje przekształcić wartości tekstowe w liczby. Ponieważ daty to tylko liczby, ta sztuczka to świetny sposób na konwersję dat na format tekstowy, który program Excel naprawdę powinien zrozumieć.

Aby przekonwertować daty na miejscu, dodając zero, spróbuj wkleić specjalnie:

  1. Wpisz zero (0) w nieużywanej komórce i skopiuj do schowka
  2. Wybierz problematyczne daty
  3. Wklej specjalnie> Wartości> Dodaj
  4. Zastosuj format daty (w razie potrzeby)

Możesz również dodać zero w formule takiej jak ta:

=A1+0

gdzie A1 zawiera nierozpoznaną datę.

Tekst do kolumn, aby ustalić daty

Innym sposobem na to, aby program Excel rozpoznawał daty, jest użycie funkcji zamiany tekstu na kolumny:

Wybierz kolumnę dat, a następnie wypróbuj Dane> Tekst do kolumn> Stałe> Zakończ

Jeśli program Excel rozpozna daty, naprawi je wszystkie w jednym kroku.

Interesujące artykuły...