Загальна формула
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Резюме
Щоб перетворити текст у нерозпізнаному форматі дати у відповідну дату Excel, ви можете проаналізувати текст та зібрати відповідну дату за формулою, заснованою на декількох функціях: ДАТА, ВЛІВО, ПОСЕРЕДНЯ та ПРАВА. У наведеному прикладі формула в C6 така:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ця формула витягує значення року, місяця та дня окремо та використовує функцію DATE, щоб зібрати їх до дати 24 жовтня 2000 року.
Передумови
Коли ви працюєте з даними з іншої системи, ви можете зіткнутися з ситуацією, коли дати неправильно розпізнаються в Excel, який замість цього обробляє дати як текст. Наприклад, у вас можуть бути такі текстові значення:
| Текст | Дата представлення |
| 20001024 | 24 жовтня 2000 р |
| 20050701 | 1 липня 2011 р |
| 19980424 | 24 квітня 1998 р |
| 28.02.2014 | 28 лютого 2014 року |
Коли Excel обчислює значення дати як текст, одним із варіантів є використання формули для синтаксичного розбору тексту на його компоненти (рік, місяць, день) і використовувати їх для створення дати за допомогою функції DATE. Як було зазначено вище, я рекомендую спочатку спробувати наведені нижче рішення (додавання нуля та використання тексту до стовпців), перш ніж використовувати формулу. Обидва обхідні шляхи швидші і вимагають менше зусиль.
Пояснення
Функція DATE створює дійсну дату, використовуючи три аргументи: рік, місяць та день:
=DATE(year,month,day)
У комірці C6 ми використовуємо функції LEFT, MID та RIGHT для вилучення кожного з цих компонентів із текстового рядка та передачі результатів у функцію DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Функція ВЛІВО витягує крайні ліві 4 символи за рік, функція MID витягує символи в позиціях 5-6 на місяць, а функція ВПРАВО вилучає крайні 2 символи як день. Кожен результат повертається безпосередньо до функції DATE. Кінцевим результатом є відповідна дата Excel, яку можна відформатувати будь-яким способом.
Цей підхід можна налаштувати за потреби. Наприклад, нерозпізнаний формат дати в рядку 8 - ддмм.рррр, а формула в С8:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Довгий текст
Іноді у вас можуть бути дати в довшій формі, наприклад "11 квітня 2020 08:43:13", які Excel не розпізнає належним чином. У цьому випадку ви зможете відрегулювати рядок таким чином, щоб Excel міг правильно розпізнавати дату за допомогою функції ЗАМІНИ. Формула нижче замінює другий екземпляр пробілу ("") комою та пробілом (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Після того, як ми додамо кому після назви місяця, Excel зрозуміє дату, але вона все одно потребує невеликого удару. Ось чому в кінці ми додаємо нуль. Операція математики змушує програму Excel перетворити рядок на число. У разі успіху це призведе до дійсної дати Excel. Зауважте, вам може знадобитися застосувати форматування номера дати, щоб правильно відобразити дату.
Без формул
Перш ніж використовувати формулу для синтаксичного аналізу та побудови дати з тексту вручну, спробуйте одне з виправлень нижче. Перший варіант використовує математичну операцію, щоб трохи «підштовхнути» Excel і змусити його спробувати оцінити текст як число. Оскільки дати Excel фактично є цифрами, це часто може зробити трюк. Можливо, вам доведеться застосувати формат дати, якщо операція буде успішною.
Додайте нуль, щоб виправити дати
Іноді ви зустрічаєте дати у текстовому форматі, який Excel повинен розпізнавати. У цьому випадку ви можете примусити Excel перетворити текстові значення в дати, додавши до значення нуль. Коли ви додасте нуль, Excel спробує прив'язати текстові значення до чисел. Оскільки дати - це просто цифри, цей фокус є чудовим способом перетворення дат у текстовому форматі, який Excel насправді повинен розуміти.
Щоб перетворити дати на місце, додавши нуль, спробуйте спеціальну вставку:
- Введіть нуль (0) у невикористаній комірці та скопіюйте в буфер обміну
- Виберіть проблемні дати
- Спеціальна вставка> Значення> Додати
- Застосувати формат дати (за потреби)
Ви також можете додати нуль у такій формулі:
=A1+0
де А1 містить невизнану дату.
Текст у стовпці для фіксації дат
Інший спосіб змусити Excel розпізнавати дати - використовувати функцію «Текст у стовпці»:
Виберіть стовпець дат, а потім спробуйте Дані> Текст до стовпців> Виправлено> Готово
Якщо Excel розпізнає дати, він зафіксує їх за один крок.
