Преобразование даты в текст в Excel
В предыдущей статье мы обсудили различные способы преобразования текста в дату в Excel. Если вы ищете решение противоположной задачи — изменение даты в Excel на текст — вам снова доступно несколько вариантов.
Традиционно мы начнем с решения по формуле, а затем рассмотрим несколько способов, не связанных с формулой.
Использование функции ТЕКСТ в Excel для преобразования даты в текст
Функция Excel ТЕКСТ специально разработана для преобразования числового значения в текстовую строку и отображения ее в указанном вами формате.
Синтаксис функции ТЕКСТ в Excel следующий:
ТЕКСТ(значение, формат_текст)
Где:
- value — это числовое значение, которое вы хотите преобразовать в текст. Это может быть число, формула, возвращающая числовое значение, или ссылка на ячейку, содержащую число.
- format_text это то, как вы хотите отформатировать результирующее текстовое значение, предоставленное в виде текстовой строки, заключенной в кавычки.
Например, вы можете использовать следующую формулу для преобразования даты в ячейке A1 в текстовую строку в традиционном для США формате даты (месяц/день/год):
=ТЕКСТ(A1;»мм/дд/гггг»)
Как вы видите на снимке экрана выше, значение, возвращаемое формулой ТЕКСТ, выравнивается по левому краю, что является первым знаком, указывающим на дату, отформатированную как текст. Помимо выравнивания в ячейке, есть еще несколько индикаторов, которые помогут вам различать даты и текстовые строки в Excel.
Пример 1. Как преобразовать дату в текстовые строки в разных форматах
Поскольку даты Excel по своей природе являются порядковыми числами, функция ТЕКСТ Excel без проблем преобразует их в текстовые значения. Наиболее сложной частью, вероятно, является указание правильного форматирования отображения текстовых дат.
Microsoft Excel понимает следующие коды даты.
Месяцы:
- m — номер месяца без ведущего нуля
- mm — номер месяца с начальным нулем
- ммм — краткая форма названия месяца, например март
- мммм — полная форма названия месяца, например Маршировать
- ммммм — месяц как первая буква, например М (обозначает март и май)
Дни:
- d — количество дней без ведущего нуля
- дд — номер дня с ведущим нулем
- ддд — сокращенный день недели, например Солнце
- dddd — полное название дня недели, например Воскресенье
Годы:
- гг — двузначный год
- гггг — четырехзначный год
Чтобы преобразованная текстовая дата отображалась именно так, как вы хотите, вы можете разделить коды даты различными разделителями, такими как тире (-), косая черта (/), запятая (,), двоеточие (:) и т. д. Вот несколько примеров:
- «мм/дд/гггг» — формат даты, используемый в США, отображается как 08.03.2015
- «дд/мм/гггг» — формат даты, используемый в остальном мире, отображается как 03.08.2015
- «дд-ммм-гг» — отображается как 08 марта 15 чтобы не было путаницы 🙂
- «dddd, mmmm d, yyyy» — полная дата, включая день недели, отображается в виде Воскресенье, 08 марта 2015 г.
Например, если у вас есть столбец с датами в США в Excel, и вам нужно экспортировать их в файл .csv для вашего партнера из Великобритании, вы можете преобразовать даты в британский формат в качестве любезности:
=ТЕКСТ(A1;»мм/дд/гггг»)
Еще несколько примеров формул и их результаты показаны ниже:
Пример 2. Как преобразовать время в текстовые строки
Если в ваших записях даты отображаются как даты, так и время, и вы хотите преобразовать их в текстовые строки точно такими, какие они есть, вы включили следующие временные коды в аргумент format_text функции Excel TEXT.
Часы:
- ч — часы без начального нуля, как 0-23.
- чч — часы с ведущим нулем, как 00-23.
Минуты:
- m — минуты без начального нуля, как 0-59
- мм — минуты с нулем в начале, как 00-59
Секунды:
- s — секунды без ведущего нуля
- сс — секунды с ведущим нулем
Периоды дня:
- AM/PM — отображается как AM или PM
- Если не указано, используется 24-часовой формат времени.
Как вы, наверное, заметили, m-коды используются как для месяцев, так и для минут, и вам может быть любопытно, как Microsoft Excel различает их. Если поставить «м«Сразу после h-кодов (часов) или непосредственно перед s-кодов (секунд) Excel понимает, что вы хотите отображать минуты, а не месяц. Да, это так просто 🙂
Функция ТЕКСТ в Excel позволяет включать в аргумент format_text коды даты и времени, например:
=ТЕКСТ(A2,»дд/мм/гггг чч:мм»)
Если вы хотите преобразовать только временную часть, поместите только временные коды, например:
=ТЕКСТ(A2;»ч:мм AM/PM»)
Результаты ваших формул TEXT могут выглядеть примерно так:
Пример 3. Как преобразовать текущую дату в текст в Excel
Если вы хотите преобразовать текущую дату в текстовый формат, вы можете использовать функцию ТЕКСТ Excel в сочетании с функцией СЕГОДНЯ, которая возвращает текущую дату, например:
=ТЕКСТ(СЕГОДНЯ(), «дд-ммм-гггг»)
Результат этой формулы будет отображаться как 08-Mar-2015. Если вы предпочитаете отображать результирующую текстовую строку в каком-либо другом формате, см. коды даты, обсуждаемые в Пример 1.
Пример 4. Формула Excel TEXT для преобразования текста в дату
Хотя основным назначением функции ТЕКСТ в Excel является преобразование чисел в текст, она также может выполнять обратное преобразование, т. е. изменять текст на дату. Для этого вы просто добавляете двойное отрицание (—) к своей формуле ТЕКСТ.
Например, чтобы преобразовать текстовую строку в ячейке A1 в дату, вы используете приведенную ниже формулу, а затем форматируете ячейку как дату.
=—ТЕКСТ(A1;»мм/дд/гг»)
Примечание. В приведенных выше примерах мы использовали коды даты и времени для английского языкового стандарта Excel. Если у вас другая локаль, коды могут отличаться для вашего языка.
Преобразование даты в текст с помощью мастера Excel «Текст в столбцы»
Как вы только что видели, функция ТЕКСТ в Excel отлично справляется с преобразованием дат в текст. Но если вы не большой поклонник формул Excel, вам может понравиться это решение больше.
Если у вас была возможность прочитать предыдущую часть нашего учебника по датам в Excel, вы уже знаете, как использовать Текст в столбцы чтобы изменить текст на сегодняшний день. Чтобы преобразовать даты в текстовые строки, вы действуете таким же образом, с той лишь разницей, что вы выбираете текст вместо Свидание на последнем шаге мастера.
Примечание. Текст в столбец Мастер всегда преобразует даты в короткий формат даты по умолчанию, независимо от того, как исходные даты отображаются на вашем листе. Дополнительные сведения о форматах даты и времени по умолчанию можно найти в следующей статье: Формат даты по умолчанию в Excel.
Если формат даты по умолчанию вам не подходит, вы можете сразу перейти к следующее решение который позволяет преобразовывать даты в текстовые строки в любом формате по вашему выбору.
Если вы не возражаете против формата по умолчанию, выполните следующие действия:
- В электронной таблице Excel выберите все даты, которые вы хотите преобразовать в текст.
- На Данные вкладку, найдите Инструменты данных группу и щелкните Текст в столбцы.
- На шаге 1 мастера выберите тип файла с разделителями и нажмите Следующий.
- На шаге 2 мастера убедитесь, что ни один из разделителей не установлен, и нажмите Следующий.
- На шаге 3 мастера, который является последним шагом, выберите Текст в разделе Формат данных столбца и нажмите Заканчивать.
Кончик. Если вы не хотите, чтобы результирующие текстовые строки перезаписывали исходные даты, укажите Назначения для верхней ячейки нового столбца.
Это было действительно легко, правда? На приведенном ниже снимке экрана показан результат: даты преобразованы в текстовые строки в формате короткой даты по умолчанию, установленном в региональных настройках Windows, в моем случае это «мм/дд/гггг»:
Преобразование даты Excel в текст через Блокнот
Еще один быстрый способ без формул превратить даты Excel в текстовые строки — использовать Блокнот или любой другой текстовый редактор. В отличие от мастера «Текст в столбцы», он позволяет преобразовать дату Excel в текст в любом формате по вашему выбору.
- На листе Excel отформатируйте даты точно так, как вы хотите, чтобы текстовые строки выглядели.
- Выберите все даты, которые вы хотите преобразовать, и нажмите Ctrl+C, чтобы скопировать их.
- Откройте Блокнот или любой другой текстовый редактор и вставьте туда скопированные даты.
- Блокнот автоматически преобразует даты в текстовый формат. Нажмите Ctrl+A, чтобы выделить все текстовые строки, а затем Ctrl+C, чтобы скопировать их.
- Вернитесь в Microsoft Excel, выберите столбец, в который вы хотите вставить текстовые строки, и примените к нему текстовый формат. Для этого нажмите Ctrl+1, чтобы открыть Формат ячеек диалоговое окно и выберите Текст на Число вкладка
- Наконец, выберите первую ячейку, в которую вы хотите вставить текстовые строки, и нажмите Ctrl+V, чтобы вставить их.
На следующем снимке экрана показан результат с исходными датами Excel в столбце B и текстовыми записями в столбце D. Обратите внимание, что преобразованные текстовые строки отражают исходный формат даты с абсолютной точностью, за исключением того, что они выделены по левому краю, поскольку все текстовые значения должно быть в экселе.
Вот как вы конвертируете дату в текст в Excel. На следующей неделе мы изучим несколько функций Excel для работы с днями недели и днями в году. А пока вы можете ознакомиться с предыдущими частями нашего подробного руководства по работе с датами и временем в Excel.
