Извлечь дату из ячейки Excel

Обновлено: 30.06.2024

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

Итак, как нам извлечь дату из текстовой строки? В этой статье мы объясним вам, как это сделать.

Способ 1. Используйте функцию «Найти и заменить»

Оглавление

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

<р>1. Выберите столбцы, чтобы преобразовать тексты в значения даты.

<р>2. Перейдите на вкладку "Главная", а затем выберите параметр "Найти и выбрать".

<р>3. В открывшемся окне выберите вариант «Заменить».

<р>4. В поле "Найти" введите значок точки, а в поле "Заменить на" – косую черту.

<р>5. Наконец, нажмите «Заменить все». Все точки будут преобразованы Excel в косые черты.

Метод 2: использование текста в столбцах

<р>1. Выберите диапазон значений

<р>2. Перейдите на вкладку Данные на ленте главного меню.

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

После нажатия кнопки "Готово" данные будут извлечены в следующую пустую ячейку

Заключение

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

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

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

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

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

=MID(A2,MIN(ЕСЛИОШИБКА(MIN(НАЙТИ(,A2&"0123456789",1)),LEN(A2)+1)),LOOKUP(1,0*MID(A2,СТРОКА(НЕПРЯМОЙ(" 1:"&ДЛСТР(A2))),1),СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)))) + 1 - МИН(ЕСЛИОШИБКА(МИН(НАЙТИ(,A2&"0123456789",1)),ДЛСТР (А2)+1)))

<р>2. Затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите нужные вам результаты, см. снимок экрана:

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

Преобразование различных нестандартных дат в обычные реальные даты в Excel

С помощью утилиты «Преобразовать в дату» Kutools for Excel вы можете быстро конвертировать различные нестандартные даты в обычные реальные даты одновременно в Excel. Нажмите, чтобы скачать Kutools for Excel!

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!

Извлечь или получить дату только из даты и времени в Excel

Чтобы извлечь только дату из списка ячеек даты и времени на листе Excel, функции INT, TRUNC и DATE помогут вам справиться с этой задачей быстро и легко.

Извлечение даты только из ячеек даты и времени с помощью функции INT или TRUNC

Обычно в Excel дата и время распознаются как порядковый номер, целая часть — это порядковый номер даты, а дробная часть — это порядковый номер времени. Функции INT и TRUNC могут помочь извлечь только целую часть из ячеек даты и времени.

<р>1. Введите или скопируйте любую из приведенных ниже формул в пустую ячейку, куда вы хотите поместить результат:

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

<р>2. Затем вы должны изменить ячейки формулы формата на формат даты, как показано ниже:

Извлечение даты только из ячеек даты и времени с функцией DATE

В Excel функция ДАТА также может помочь вам напрямую извлечь только дату из ячеек даты и времени.

datetime : ячейка содержит дату и время, из которых вы хотите извлечь только дату.
ГОД() , МЕСЯЦ() , ДЕНЬ() : эти три аргумента используются для извлечения отдельного номера года, месяца и дня из ячейки даты.
ДАТА: эта функция используется для объединения чисел года, месяца и дня из отдельных ячеек в действительную дату.

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

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

Используемые относительные функции:

INT:
Возвращает целую часть значения.

ТРАНК:
Эта функция используется для возврата усеченного числа на основе количества цифр.

ДАТА:
Он возвращает правильную дату на основе столбцов года, месяца и дня.

Статьи с относительными датами:

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

Сравнить две даты по месяцам и годам только в Excel
Если у вас есть два списка дат, теперь вам нужно сравнить даты только по месяцу и году и игнорировать значение дня, если они имеют один и тот же месяц и год, результат должен отображаться как True, в противном случае должен быть False, как показано на следующем снимке экрана. Как сравнить даты только с месяцем и годом, но игнорировать день в Excel?

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

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

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

Лучшие инструменты для повышения производительности в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите выполнять свою повседневную работу быстро и качественно?Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

В этом руководстве мы покажем вам, как извлечь дату из строки в Excel с помощью формул и VBA.

Обратите внимание, что не существует единой формулы или подхода для всех сценариев. Извлечение дат из строк, которые не соответствуют определенному шаблону, — одна из немногих вещей, с которыми не может справиться Excel. Вам почти нужен ИИ, чтобы иметь возможность инкапсулировать каждый возможный сценарий. Хорошо, что мы собираемся предложить вам несколько способов анализа даты.

Мы создали строки, включающие даты, используя косую черту (/) в качестве разделителя дня, месяца и года, а также даты американского типа (месяц/день/год). Таким образом, если у вас другой формат даты, вы можете увидеть ошибки (пустые ячейки из-за функций ЕСЛИОШИБКА) после открытия нашего файла. Обновите даты в соответствии с вашими настройками локализации. Кроме того, не забудьте обновить символы косой черты, если ваши данные содержат даты с другими разделителями, такими как точка (.) или дефис (-).

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

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

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

07.01.2022
7 января 2022 г.
7 января 22
12.01.2022
1 декабря 2022 г.
1 декабря
12 декабря 2022 г.

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

В нашем образце формулы мы использовали формат "07.01.2022". Таким образом, мы предполагаем, что значения дат в данных соответствуют следующим правилам:

Формат даты США
Месяц и день — две цифры
Год состоит из четырех цифр.
Каждая единица разделяется косой чертой "/"
Дата содержит 10 символов, включая разделители.

Исходя из этих предположений, мы можем использовать ключевое слово "/??/" с функцией ПОИСК, чтобы найти дату. Функция ПОИСК может использовать подстановочные знаки для поиска шаблона вместо точной строки. Так как вопросительный знак (?) представляет собой один символ, "/??/" означает 2 символа между косой чертой, как день в дате.

Функция ПОИСК возвращает номер позиции, с которой начинается ключевое слово, если есть совпадающая строка.

Вычтите позицию на 2, чтобы найти, где начинается дата.

После того, как дата будет найдена, вы можете использовать функцию MID для ее анализа. Функция MID нуждается в позиции строки, которую вы хотите проанализировать, и ее длине. Поскольку мы предполагаем, что формат даты "мм/дд/гггг", длина будет равна 10.

Несмотря на то, что формула работает, она вернет любые символы, соответствующие шаблону, т. е. буквы.

Хотя эта формула подходит для нашего примера, очевидно, что для других она не годится. Несколько примеров:

Дата месяца, состоящая из одной цифры, без пробела впереди: ячейки C6, C9.
Текст, содержащий аналогичную строку перед фактической датой: ячейка C8
Месяц и дни из одной цифры: ячейки между C19 и C25.
Годы с двузначным числом: ячейки между C26 и C32.

Использование VBA для извлечения даты из строки

Альтернативой формулам Excel является создание собственной функции с помощью VBA. Функции на основе VBA, также известные как функции, определяемые пользователем (UDF), можно использовать как обычные формулы, если они правильно написаны и сохранены.

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

Метод Split может разделять слова или строки пробелами.
Метод DateValue может проверять даты лучше, чем его аналог-формула.

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

Вы можете скопировать и вставить следующий код в модуль и сразу начать его использовать. Только не забудьте сохранить файл Excel как книгу Excel с поддержкой макросов (XLSM), а не как обычную книгу.

После вставки кода используйте его на листе, указав ссылку на ячейку данных.

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

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

Читайте также: