Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы?

Как преобразовать текст в дату в Excel или извлечь его из сложного предложения?

Теперь вы узнаете, как использовать функции Excel для преобразования текущего текста. Вы также узнаете, как это сделать без использования формул.

Поскольку Excel — не единственное приложение, которое вы используете, иногда вы работаете с данными, импортированными из файла .csv или другого внешнего источника (например, 1С). Когда это произойдет, даты, скорее всего, будут вставлены в таблицу в виде текстовой записи. Хотя на первый взгляд они кажутся датами, Excel не распознает их как таковые.

Существует много способов преобразовать текущий текст в Excel, и это краткое руководство должно помочь вам выбрать метод, который лучше всего подходит для вашего формата данных и рабочих предпочтений, с формулами или без них.

Как отличить обычные даты Excel от «текстовых дат»

Импортированные данные (или данные, введенные неправильно) могут выглядеть как обычные даты Excel, но они не ведут себя так, как кажутся. Microsoft Excel обрабатывает эти записи как текст. Следовательно, вы не сможете правильно отсортировать таблицу в хронологическом порядке или использовать эти «неправильные даты» в формулах, сводных таблицах, диаграммах или любом другом инструменте Excel, который работает со временем.

Во-первых, давайте рассмотрим некоторые признаки, которые могут помочь определить, датирована ли ячейка или содержит ли она текст.

Их можно легко распознать, слегка развернув столбцы, выделив один из них, выбрав «Формат выравнивания ячеек» и установив для параметра «По горизонтали» значение «Общие» (это вид ячеек по умолчанию). Нажмите ОК и внимательно посмотрите на таблицу.

Если некоторые значения не выровнены по правому краю, Excel не рассматривает их как даты.

Как можно использовать форматирование.

Поскольку все функции, которые изменяют текст на дату, в результате возвращают число, давайте сначала более подробно рассмотрим преобразование чисел в даты.

Как вы, наверное, знаете, Excel хранит дату и время как порядковые числа дней, и только форматирование ячейки позволяет отображать число как день-месяц-год. Например, 1 января 1900 года сохраняется как 1, 2 января 1900 года сохраняется как 2, а 1 января 2015 года сохраняется как 42005. Дополнительные сведения о том, как Excel сохраняет дату и время, см. В разделе Формат даты.

При вычислении дат в Excel результат, возвращаемый различными функциями, часто представляет собой число, представляющее дату. Например, если TODAY () + 10 возвращает число типа 44294 вместо 10 дней после сегодняшнего дня, это не означает, что формула неверна. Просто формат ячейки установлен на Общий или Текст, тогда как это должна быть Дата.

Чтобы преобразовать такой порядковый номер в день-месяц-год, все, что вам нужно сделать, это изменить формат представления. Для этого просто выберите «Дата» в поле «Числовой формат» на вкладке «Главная».

Чтобы применить настраиваемый формат, отличный от формата по умолчанию, выберите ячейки с этими числами и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата», укажите желаемый формат даты в разделе «Тип» и нажмите «ОК».

Если вам нужно что-то более сложное, чем стандартные форматы даты, см. Как создать собственный формат даты в Excel.

Если упрямое число отказывается заменять датой, проверьте, почему формат даты не работает — советы по устранению неполадок .

Когда возникает подобная проблема, вы, скорее всего, захотите преобразовать эти текстовые значения в обычные даты Excel, чтобы вы могли ссылаться на них в формулах для выполнения различных вычислений. И, как это часто бывает в Excel, есть несколько способов решить эту проблему.

Функция Excel ДАТАЗНАЧ — изменить текст на дату

Функция DATEVALUE (DATEVALUE в английской версии) преобразует текстовую дату в порядковый номер дня, который Excel распознает как дату.

Синтаксис DATEVALUE очень прост:

= DATEVALUE (date_as_text)

Простейшее использование этой функции = DATEVALUE (A1), где A1 — это ячейка с датой, хранящейся в виде строки символов.

Поскольку при этом текст преобразуется в порядковый номер дня, вам все равно необходимо применить соответствующий формат представления. И иногда программа может делать это автоматически за вас.

На снимке экрана показано несколько формул DATEVALUE в действии:

На первый взгляд результат работы может показаться вам довольно неожиданным. Вы можете увидеть это на скриншоте в столбце C. Но не запутайтесь — вам просто нужно применить нужное форматирование. Как видите, результатом формулы является порядковый номер дня с 1900 года (столбец C). И если мы применим правильный формат, то есть укажем программе, что на самом деле это не число, а день, мы сразу получим приемлемый результат (столбец D).

Следовательно, столбец D показывает результат C, только на этот раз как дату.

Как вы, наверное, знаете, Excel хранит дату и время как порядковые числа, и только форматирование ячеек позволяет отображать число как дату. В частности, 1 января 1900 года сохраняется как номер 1, а 2 января 1900 года сохраняется как 2. В результате 8 марта 2020 года сохраняется как 43898. Чтобы преобразовать такой серийный номер в дату, все, что вам нужно сделать сделать это изменить формат ячейки.

Для этого выберите ячейку или диапазон ячеек с числами, которые вы хотите представить в виде дат, и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Во вкладке «Числовые форматы» в пункте «Дата» выберите подходящий вам аспект в разделе «Тип» и нажмите кнопку «ОК».

Также обратите внимание, что не весь текст можно использовать таким образом. Если Excel не может с этим справиться, он возвращает # ЗНАЧ! (см пункт 3 на фото выше)

Также, если по какой-то причине вы использовали запятые вместо точек, то с помощью функции REPLACE сразу замените их точками — и все будет хорошо.

Функция ДАТАВАЛОРЕ — что запомнить?

При преобразовании текстовой строки в дату имейте в виду, что:

• Информация о времени в текстовых значениях игнорируется, как вы можете видеть в строках 2 и 6 на изображении выше (1). Чтобы преобразовать эти значения, содержащие дату и время, используйте функцию VALUE.
• Если год не указан в «текстовой дате», DATEVALUE заменит текущий год системным временем компьютера, как показано в строке 3 выше (2).
• Поскольку Microsoft Excel начинает отсчет времени только с 1 января 1900 г., использование VALOREDATA с более ранним возрастом приведет к ошибке #VALUE!.
• Все значения, связанные со временем, хранятся в Excel в виде числа. Но DATEVALUE не может преобразовывать числовое значение в меру времени и не может обрабатывать текст, который выглядит как число. Для этого вам нужно будет использовать функцию ЗНАЧЕНИЕ. И именно об этом мы поговорим позже.

Функция ЗНАЧЕН – как конвертировать текст в дату.

По сравнению с DATEVALUE функция VALUE более универсальна. Он может преобразовать любую строку, которая выглядит как дата или число, в действительное число, которое затем можно легко вывести как дату.

Синтаксис следующий:

= ЗНАЧЕНИЕ (текст)

Где текст — это строка или ссылка на ячейку, содержащую символы, которые нужно преобразовать в число.

Функция VALUE () может обрабатывать как дату, так и время. Последняя преобразуется в десятичную часть числа, как вы можете видеть в строке 11 на следующем снимке экрана:

Как видите, результаты такие же, как в приведенном выше примере. Только время тоже определяется и обрабатывается правильно.

Однако, к сожалению, такие данные, пригодные для обработки функцией VALUE (), на практике встречаются нечасто. Разработчики различного программного обеспечения стремятся проявить фантазию, записывая дату и время операции в своих отчетах. И когда мы хотим обработать эти отчеты в Excel, возникает масса проблем.

Остановимся на наиболее типичных и попробуем показать решения.

Математические операции для преобразования текста в дату

Помимо использования функций Excel, о которых мы говорили выше, вы можете выполнить простую математическую операцию, чтобы заставить программу переставить строку в дате. Условие: операция не должна менять свое значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут вам в этом разобраться!

Предполагая, что ваши данные находятся в ячейке A1, вы можете использовать одну из следующих формул, а затем применить формат даты к ячейке:

• Дополнение: = LA1 + 0
• Умножение: = A1 * 1
• Деление: = A1 / 1
• Двойное отрицание: = — A1

Как видите, математические операции могут помочь с датами (строки 3,4, 5,7), временем (строки 2 и 6) и числами, отформатированными как текст (строка 8). 

Иногда результат даже автоматически отображается в виде даты, и вам не нужно беспокоиться об изменении формата ячейки.

Как обойти нестандартные разделители

Если запись содержит разделитель, отличный от косой черты (/) или тире (-), функции Excel не распознают их как даты и вернут ошибку #VALUE !. Чаще всего такими «неправильными» разделителями являются пробел и запятая.

Чтобы исправить это, вы можете запустить инструмент «Найти и заменить», чтобы заменить этот несоответствующий разделитель, например, косой чертой (/):

• Выделите все ячейки, которые хотите превратить в даты.
• Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить».
• Введите свой собственный разделитель (например, запятую) в поле «Найти» и косую черту в поле «Заменить.
• Щелкните «Заменить все.

Теперь DATEVALUE или VALUE должны иметь проблемы с преобразованием текстовых строк в даты. Точно так же вы можете исправить записи, содержащие любой другой разделитель, например пробел или обратную косую черту.

Если вы предпочитаете решение на основе формулы, вы можете использовать функцию ЗАМЕНИТЬ, как мы это делали на одном из предыдущих снимков экрана:  

= ЗНАЧЕНИЕ ДАННЫХ (ПОДСТАВИТЬ (A6;»,»;».»))

А текстовые строки преобразуются в даты с использованием единой формулы.

Как видите, функции DATEVALUE или VALUE довольно мощные, но, к сожалению, у них есть свои ограничения. Например, если вы хотите работать со сложными конструкциями, такими как четверг, 1 января 2021 г., ни одна из них не поможет. 

К счастью, есть решение без формул, которое может помочь, и следующий раздел даст нам пошаговое руководство.

Мастер преобразования текста в столбцы — не требующий формул способ преобразования текста в дату.

Пригодится довольно старый инструмент Excel под названием «Текст в столбцы». Умеет работать как с простыми текстовыми датами, так и с текстовыми строками, состоящими из нескольких частей.

Пример 1. Преобразование простых текстовых строк в даты.

В этом примере мы преобразуем текстовые строки, такие как 01 01 2015 (день, месяц и год, разделенные пробелами), в даты.

1. На листе выберите столбец, содержимое которого вы хотите преобразовать.
2. Перейдите на вкладку «Данные» и нажмите «Текст по столбцам».

1. На шаге 1 мастера преобразования текста в столбцы выберите «С разделителями» и нажмите «Далее».
2. На шаге 2 мастера снимите флажки для всех разделителей и нажмите Далее.
3. На последнем шаге выберите «Дата» в разделе «Формат данных столбца», выберите тип, соответствующий вашим данным, в раскрывающемся списке. Щелкните Готово».

Теперь Excel распознает ваши текстовые значения по мере необходимости, автоматически преобразует их в формат даты по умолчанию и отображает их в ячейках с выравниванием по правому краю. 

вы можете изменить представление результата обычным способом через диалоговое окно «Формат ячеек.

Заметка. Для правильной работы мастера текста по столбцу все записи должны быть согласованными. Например, если одни из них выглядят как день / месяц / год, а другие — как месяц / день / год, вы получите неверные результаты.

Пример 2. Преобразование сложных текстовых строк в даты.

Если ваши заметки представляют собой более сложные предложения с несколькими частями, например:

• 8 марта 2020 г., воскресенье
• 8 марта 2020 15:00

Вам придется приложить немного больше усилий и использовать инструмент, который мы рассматриваем вместе с функцией DATA.

Важно! Название месяца при использовании этого метода должно быть в именительном падеже!

1. Выберите желаемую область данных.
2. Вызов мастера конвертации.
3. На шаге 1 укажите «С разделителями» и нажмите «Далее» .
4. На шаге 2 мастера выберите используемые разделители.

Например, если вы конвертируете строки, разделенные запятыми и пробелами, например «8 марта 2020 г., воскресенье», вы должны выбрать и запятую, и разделители пробелов.

Также имеет смысл выбрать параметр «Рассматривать последовательные разделители как один», чтобы игнорировать лишние пробелы или пробелы после запятых, если они есть в ваших данных.

Я немного усложнил наш пример, добавив букву «g» с точкой после порядкового номера года. Поэтому эта буква добавляется в список разделителей.

Наконец, взгляните на окно анализа данных образца и проверьте, правильно ли ваши слова разделены на столбцы. Затем нажмите «Далее».

1. На шаге 3 мастера убедитесь, что все столбцы в разделе «Предварительный просмотр данных» имеют общий формат. В противном случае щелкните столбец и выберите «Общие» в формате данных.

Заметка. Не выбирайте формат даты, поскольку каждый столбец содержит только его часть. Следовательно, Excel не сможет понять, что вы действительно хотите делать.

Если вам не нужны никакие столбцы, щелкните по ним и выберите «Пропустить».

Как видите, я буду использовать только первые три столбца, а последние два не нужны.

Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выберите ячейку B2.

Когда закончите, нажмите кнопку «Готово.

Как видно на скриншоте выше, мы пропускаем четвертый и пятый столбцы с периодом и днями недели, делим остальные данные на 3 столбца (в общем формате) и вставляем их, начиная с ячейки B2.

На рисунке ниже показан результат с необработанными данными в A и блоками данных в B, C и D.

1. Наконец, вам нужно связать эти части вместе с помощью функции ДАТА. Его синтаксис не требует особых пояснений:

= ДАТА (год, месяц, день)

В нашем случае год записан в столбце D, а день — в столбце B, с этим проблем нет.

Но с месяцем не все так просто, потому что это слово и его номер необходим. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ, которая может изменить название месяца на его порядковый номер:

= МЕСЯЦ (порядковый номер)

Чтобы функция МЕСЯЦ знала, что это дата, мы выражаем ее так:

= МЕСЯЦ (1 и C2)

Здесь C2 содержит название месяца, в нашем случае март. «1 &» добавляется, чтобы получить дату (1 марта), чтобы формула могла преобразовать ее в соответствующий номер месяца. Еще раз повторю, что месяц здесь должен быть в именительном падеже, что не всегда удобно.

Теперь давайте встроим это выражение в качестве второго аргумента:

= ДАТА (D2; МЕСЯЦ (1 и C2); B2)

И вуаля, наши сложные комбинации слов и чисел успешно превращаются в даты:

Как преобразовать текст в дату при помощи формул.

Давайте рассмотрим другой способ превратить сложную формулировку в дату. Столбец A содержит даты в виде текстовых выражений формы «08 марта 2020». Сразу отметим, что записи должны быть единообразными, то есть все числа хранятся в двузначном формате: 01, 07, 08, 10, 12 и так далее

Мы используем формулы для работы с символьными значениями: LEFT, RIGHT, PSTR. Они позволяют извлекать указанное количество символов из начала строки текста, из конца или из центра.

Наша задача — разобрать текст на части, из которых потом «наклеить» дату.

Воспользуемся формулой:

= ЗНАЧЕНИЕ ДАННЫХ (LEFT (A2; 2) & «-» & MID (LEFT (A2; 6); 4; 3) & «-» & MID (RIGHT (A2; 6); 1; 4))

Давайте узнаем, как это работает.

LEFT (A2; 2) — извлекает первые 2 символа, т.е число.

PSTR (LEFT (A2; 6); 4; 3) — получаем первые 3 буквы названия месяца.

PSTR (RIGHT (A2; 6); 1; 4) — отображает 4 цифры года.

И все это объединяем с оператором &, помещая между этими частями дефис.

Можно написать немного иначе:

= ЗНАЧЕНИЕ ДАННЫХ (LEFT (A2; 2) & «-» & MID (A2; 4; 3) & «-» & LEFT (RIGHT (A2; 6; 4))

В английской версии:

= ЗНАЧЕНИЕ ДАННЫХ (LEFT (A2,2) & «-» & MID (A2,4,3) & «-» & LEFT (RIGHT (A2,6); 4))

Как извлечь дату из сложного текста при помощи формул?

Когда мы переносим различные бухгалтерские данные в Excel, мы постоянно убеждаемся, что фантазия наших бухгалтеров поистине безгранична. Это также относится к написанию дат в пояснениях к бухгалтерским проводкам.

Вот несколько готовых решений, которые могут вам пригодиться.

Попробуем вывести дату из пояснения бухгалтерских записей. Как видите, варианты написания могут быть самыми разными. Единственное условие — день записывается двузначным числом: в нашем случае 07.

Формула извлечения даты из текста выглядит так:

• если у вас есть две цифры для дня и месяца и четыре для года:

= СРЕДНИЙ (A2; ПОИСК («??. ??. ????»; A2); 10)

• но если год состоит из последних двух или всех четырех цифр, то есть более универсальный вариант:

= — MID (A2; ПОИСК («??. ??.»; A2); 8 + 2 * ISNUMBER (-PSTR (A2; SEARCH («. ??.»; A2) +6; 1)))

Как должен выглядеть результат в ячейке — определяйте сами. Как именно: мы это уже несколько раз анализировали. См. Чуть выше.

Но что, если мы убедимся, что внешний вид извлеченной даты тоже задается не вручную, а по формуле? Здесь нам поможет функция ТЕКСТ.

Самое простое представление выглядит так:

= ТЕКСТ (MID (A1; ПОИСК («??. ??.»; A1); 8) & ТЕКСТ (MID (A1; ПОИСК (. ??. «; A1) +6; 2);» 0 ;; ; «);» ДД ММММ ГГГГ»)

Как видите, фокус в том, чтобы правильно описать формат во втором аргументе. «ДД ММММ ГГГГ» обозначает день, месяц в именительном падеже и год из четырех цифр. Но «[$ -FC19] ДД ММММ ГГГГ» будет делать то же самое, но месяц будет в родительном падеже (7 ноября). Остальные варианты вы можете увидеть на фото выше. Вы можете выбрать тот, который лучше всего соответствует вашим потребностям.

Но что, если ваш день можно записать как однозначными, так и двузначными числами? А вот и универсальный вариант. Правда, посложнее.

Здесь мы применяем формулу массива.

{= ЕСЛИ ОШИБКА (- LEFT (REPLACE (REPLACE (PSTR (A2; MIN (SEERROR (FIND ($ A $ 2: $ A $ 363))); {«dd.mm.»; «D.mm.»; » dd / mm / «;» d / mm / «}); A2); 9 ^ 9)); 10);» «;» «);» г «;» «); 10);»»)}

Конечно, скобки вставлять не обязательно: они появятся автоматически после нажатия комбинации клавиш CRTR + SHIFT + ENTER, когда вы закончите ввод формулы.

Как видите, здесь мы постарались охватить почти все возможные варианты написания. Вам нужно вручную настроить внешний вид ячеек с результатом. Что ж, если вам нужно сделать это автоматически, снова используйте функцию ТЕКСТ, как это было сделано чуть выше.

Например, чтобы сразу получить «07 ноября 2019 г.», вы можете изменить наше выражение следующим образом:

{= ТЕКСТ (ЕСЛИОШИБКА (- ЛЕВО (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2; МИН (ЕСЛИОШИБКА (НАЙТИ (ТЕКСТ (СТРОКА ($ A $ 2: $ A $ 363))); {«dd.mm.»; «D mm. «;» dd / mm / «;» d / mm / «}); A2); 9 ^ 9)); 10);» «;» «);» «;» «); 10);» «);» [$ — FC19] дд мммм гггг \ r.;@»)}

И не забывайте формулу массива!

Быстрая конвертация при помощи Специальной вставки.

Чтобы быстро преобразовать ряд простых текстовых строк в даты, вы можете использовать следующий трюк.

• Скопируйте любую пустую ячейку (выделите ее и нажмите Ctrl + C).
• Выберите диапазон с текстовыми значениями, которые нужно преобразовать в даты.
• Щелкните выделение правой кнопкой мыши, выберите «Специальная вставка» и выберите «Сложить» в диалоговом окне:

• Нажмите ОК, чтобы выйти и закрыть диалоговое окно.

Вы только что попросили Excel добавить ноль (пустую ячейку) к вашим датам текста. Для этого программа преобразует текст в число, и, поскольку добавление нуля не меняет значения, вы получаете именно то, что хотели: порядковый номер дня. 

Как обычно, установите желаемый вид для этих ячеек с помощью диалогового окна «Формат ячеек».

Исправление записей с двузначными годами.

Современные версии Microsoft Excel достаточно умны, чтобы обнаруживать явные ошибки в данных, или, скорее, они говорят то, что Excel считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу ячейки, а когда вы выберете его, появится восклицательный знак.

При нажатии на восклицательный знак появятся различные параметры, связанные с вашими данными. В случае двузначного года программа спросит вас, хотите ли вы преобразовать его в 19XX или 20XX.

Если у вас есть несколько записей этого типа, вы можете исправить их все за один раз: выберите все ячейки с ошибками, затем щелкните восклицательный знак и выберите соответствующий вариант.

Как преобразовать 8-значное число в дату в Excel

Это очень распространенная ситуация, когда дата вводится как 8-значное число, например 10032021. Вам необходимо преобразовать ее в формат, который может распознать Excel (например, 20.03.2021). В этом случае простое изменение формата ячейки на «Дата» не сработает — в результате вы получите ##########.

Чтобы преобразовать его в дату, вам нужно будет использовать функцию DATE () в сочетании с функциями RIGHT (), LEFT () и MID (). К сожалению, невозможно написать универсальную формулу, которая работает во всех сценариях, потому что исходное значение можно ввести разными способами. Например:

Я постараюсь объяснить общий подход к преобразованию таких чисел в даты и приведу несколько примеров формул.

Во-первых, запомните порядок аргументов функции DATE():

= ДАТА (год; месяц; день)

Итак, что вам нужно сделать, это извлечь год, месяц и день из исходного числа и передать их в качестве соответствующих аргументов функции DATE().

Например, давайте посмотрим, как преобразовать 20032021 (хранится в ячейке A2) в 20.03.2021.

• Извлекаем год. Это последние 4 цифры, поэтому мы используем функцию ВПРАВО, чтобы выбрать последние 4 символа: ВПРАВО (A2; 4).
• Месяц. Это третья и четвертая цифры, поэтому мы используем функцию MID, чтобы получить их: MID (A2; 3; 2). Здесь число 3 (второй аргумент) — это начальная позиция, а 2 — количество символов, которые нужно извлечь.
• День. Это первые две цифры, поэтому у нас есть функция LEFT, которая их возвращает: LEFT (A2; 2).

Наконец, вставьте указанные выше элементы в функцию DATE (), и вы получите формулу для преобразования числа в дату в Excel:

= ДАТА (ВПРАВО (A2,4); СРЕДНИЙ (A2,3,2); ЛЕВЫЙ (A2,2))

На снимке экрана ниже показано это и еще несколько операторов в действии:

Обратите внимание на строку 5 на скриншоте выше:

= ДАТА («20» & ЛЕВО (A5; 2); ВПРАВО (A5; 2); СРЕДНЕЕ (A5; 3; 2))

 Исходное число (212003) содержит только два символа, обозначающих год (21). Итак, чтобы получить 2021 год, мы объединяем 20 и 21 год по следующей формуле:

«20» И СЛЕВА (A5; 2)

В противном случае функция DATE () вернет 1921 год, что немного странно, как будто Microsoft все еще живет в 20 веке