Как извлечь дату из текста в excel

Дата из текста (ДАТАИЗСТРОКИ)

Описание работы

Часто необходимо извлечь дату из ячейки, которая содержит длинный текст. Задача может усложнится тем, что дата может быть как в начале, в конце или середине текста. Еще сложнее дело будет обстоять, что дата в тексте имеет разные форматы.

Извлечь в данном случае дату из текста стандартными методами крайне сложно.

С помощью функции на VBA =ДАТАИЗСТРОКИ это сделать очень просто. Функция найдет дату в тексте вне зависимости от формата ее записи и расположения ее в строке. Также распознаются даты на русском и английском языке.

Пример 1

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

Пример 2

Аналогичный пример, но дата расположена в конце строки.

Пример 3

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

Синтаксис функции

Функция =ДАТАИЗСТРОКИ(ТЕКСТ;[En]) имеет два аргумента:

  • ТЕКСТ — Текст, из которого необходимо извлечь дату
  • [En] — Необязательный параметр. Если дата содержит наименование месяца на английском языке, укажите данный параметр равный 1. По умолчанию равно 0.

Пример 4

Извлечение даты на английском языке из текста

Преобразование дат из текстового формата в формат даты

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

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

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

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

С помощью индикатора ошибки можно преобразовать даты из текста в формат даты.

Примечания: Сначала убедитесь, что в Excel включена Excel. Для этого:

Щелкните Файл > Параметры > Формулы.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Excel Параметры > Формулы.

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

В области Правила проверки ошибоквыберите ячейки, содержащие годы, представленные 2 цифрами.

Выполните эту процедуру, чтобы преобразовать дату в текстовом формате в обычную дату:

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

Нажмите кнопку ошибки, которая появляется рядом с выбранными ячейками.

Кнопка ошибки

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

Команды преобразования дат

Текстовые даты с двузначным номером года преобразуются в стандартные даты с четырехзначным номером года.

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

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

столбец дат в текстовом формате

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

Щелкните Числовом формате и выберите нужный формат даты.

Краткий формат даты выглядит так:

изменение формата данных на краткий формат даты с помощью ленты

Длинная дата содержит дополнительные сведения, как по рисунку:

кнопка на ленте для преобразования в длинный формат даты

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

Выберите пустую ячейку и убедитесь, что ее число имеет форматОбщий.

В пустой ячейке сделайте следующее.

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

Нажмите ввод, и функция ДАТАVALUE возвращает порядковый номер даты, представленной текстовой датой.

Что такое Excel серийный номер?

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию: 1 января 1900, порядковый номер 1, а 1 января 2008 г. — число 39448, поскольку через 39 448 дней после 1 января 1900.To скопируйте формулу преобразования в диапазон последовательных ячеек, вы выберите ячейку, содержащую введенную формулу, и перетащите его через диапазон пустых ячеек, который соответствует размеру диапазона ячеек, содержащих текстовые даты.

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

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

Сочетания клавиш: Можно также нажать CTRL+C.

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

В диалоговом окне Специальная вставка в разделе Вставить выберите параметр Значения и нажмите кнопку ОК.

На вкладке Главная нажмите кнопку запуска всплывающее окно рядом с кнопкой Число.

В поле Категория выберите пункт Дата, после чего укажите необходимый формат даты в списке Тип.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Ссылка на основную публикацию