Konwertuj różne wpisy tekstowe w programie Excel
Wypróbowałem szereg sugerowanych rozwiązań w StackExchange do konwertowania tekstu do tej pory w programie Excel, jednak żadne z nich nie działa niezawodnie w przypadku danych, które mam, zobacz poniżej, aby uzyskać wyjaśnienie.
Mam kolumnę z „datą rozpoczęcia”. Zawiera datę w następujących formach:
(Stan początkowy) 17.09.2019 9:30:59 20.10.2020 16:45:59 26.02.2020 12:20:59
Ten obraz przedstawia dane i żądany stan końcowy ...
Chcę zamienić cały wpis na wartość daty Excela, abym mógł na nim wykonać przydatne obliczenia, np. Odjąć czas rozpoczęcia np. 20.11.2020 od czasu zakończenia 21.11.2020, więc np. Chcę drugi wpis powyżej, aby skończyć jako (najlepiej) 23/02/2020 20:31:59 (ponieważ jestem Brytyjczykiem), ale nawet jeśli mogę go pobrać do wersji amerykańskiej, to w porządku, mogę z tym pracować.
(Żądany stan końcowy) 17.09.2019 09:30 20.10.2020 16:45 26.02.2020 12:20
Proszę, nie kieruj mnie na inne odpowiedzi, które wydają się rozwiązać ten problem. Próbowałem ich używać. Wydaje się, że każdy ma problem i nie widzę, jak go rozwiązać, lub nie opublikowałbym tego pytania.
np. jeśli spróbuję =datevalue(left(a1,9)), nie otrzymuję wpisów z podwójną cyfrą miesiąca (tj. 10, 11, 12).
Jeśli spróbuję tego =date(mid.... etcsamego: nie przechwytuje to niezawodnie wszystkich informacji, ponieważ różne komórki mają różne długości informacji.
Użyłem metody Text-To-Columns , która dzieli ją na komórki bez formatu daty / godziny i odcina PM lub AM do innej kolumny, którą mogę odrzucić, ALE oznacza to, że zamiast 20:31:59 otrzymuję 08:31:59 w jednej kolumnie, a PM w innej, i nie widzę, jak to zrobić, aby zwrócić 20:31:59.
Jeśli cokolwiek w tym pytaniu nie jest jasne, zapytaj.
A jeśli przegapiłem coś naprawdę oczywistego, przepraszam, ale spędziłem godziny próbując rozwiązać ten problem.
Odpowiedzi
Moją pierwszą próbą byłoby dopasowanie niestandardowego formatu daty w komórce tekstowej. Jeśli to zadziała, tekst w komórce zostanie wcięty o jedną spację i będziesz mógł powiedzieć = A1 + 0, aby dodać zero dni do daty bez zgłaszania błędu. Następnie sformatuj tę nową komórkę zgodnie z własnymi upodobaniami.
W najgorszym przypadku możesz pociąć ciąg na natywny format daty, który jest zrozumiały dla programu Excel. Nie potrzebujesz stałych długości, ale możesz użyć spacji i znaków / jako ograniczników, np. Aby uzyskać dzień między dwoma /
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
Co oznacza semantycznie
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
Daj lub weź +1 lub -1. Nie jestem teraz przed komputerem, aby przetestować formułę. Ale jeśli zwraca np. Jeden znak do małego, po prostu dodaj +1 do argumentu długości. Po wyodrębnieniu dnia, miesiąca i roku (i godziny, jeśli trzeba), możesz zrekonstruować datę za pomocą funkcji daty.
Ok, więc myślę, że mogę mieć dla Ciebie odpowiednie rozwiązanie. Wydawałoby się, że sposób konwertowania tekstu przez program Excel do tej pory zależy od ustawień regionu Windows. Ustawienie formatowania daty w komórce w programie Excel zmienia tylko sposób wyświetlania wartości dat , a nie sposób ich konwersji.
Moje ustawienia regionu w systemie Windows to obecnie Republika Południowej Afryki. Mój arkusz kalkulacyjny rozwiązuje się tak, z sąsiednią formułą = A1 + 0:
Teraz, jeśli udam się do panelu sterowania Windows > Zmień format daty, godziny lub liczb i tymczasowo zmienię format na angielski (Stany Zjednoczone), a następnie wróć do arkusza kalkulacyjnego i ponownie oceń formułę (naciśnij ctrl + alt + F9, jeśli tak nie aktualizuje się automatycznie):
Teraz skopiuj i wklej jako wartości nową kolumnę wartości daty nad sobą. Zapisz plik. Zmień ustawienia regionu z powrotem do poprzedniego stanu. Wybierz kolumnę i zapisz format komórki z datą i godziną, który Ci się podoba w „niestandardowym”:
Po to aby:
