В этом руководстве объясняются особенности функции Excel ТЕКСТ и показаны некоторые полезные примеры форматирования чисел и дат в тексте в Excel.

В своих таблицах вы, скорее всего, большую часть времени будете иметь дело с числами.

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

Функция ТЕКСТ синтаксис и использование

По своей сути ТЕКСТ в Excel используется для преобразования числового значения в текстовую строку в определенном формате.

Синтаксис функции Excel ТЕКСТ следующий:

ТЕКСТ(значение; format_text )

Где:

  • Значение — числовое значение, которое необходимо преобразовать в текст. Это может быть число, дата, ссылка на ячейку, содержащую числовое значение, или другую функцию, возвращающую число или дату.
  • Format_text — формат, который вы хотите применить. Он предоставляется в виде кода формата , заключенного в кавычки, например «мм/дд/гг».

Функция ТЕКСТ доступна во всех версиях Excel.

Обычно формула Excel ТЕКСТ используется в следующих ситуациях:

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

Например, если вы хотите извлечь дату из ячейки A2 и отобразить ее в другой ячейке в традиционном формате даты, например «15 июль 2024», вы используете следующую формулу Excel ТЕКСТ:

=ТЕКСТ($A$2; "ДД ММММ ГГГГ")

Вот еще несколько вариантов, как можно записать дату в виде текста:

Еще больше вариантов форматирования даты вы можете посмотреть здесь: Формат даты и как его настроить.

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

Как использовать формат в формуле ТЕКСТ

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

КодОписаниеПример кода формата
0Заполнитель цифр, отображающий незначащие нули.#.00 — всегда отображает два знака после запятой.Если вы введете 2.5 в указанную ячейку, оно отобразится как 2.50
#Заполнитель цифр, не отображающий дополнительных нулей.#.## — отображает до 2 знаков после запятой.Если вы наберете 2.5, оно будет отображаться как 2.5 Если вы наберете 2.555, оно отобразится как 2.56
?Заполнитель цифр, который оставляет место для незначащих нулей, но не отображает их. Используется для выравнивания чисел в столбце по десятичному разделителю.#.?? - отображает максимум 2 десятичных знака и выравнивает десятичные разделители в столбце.
. (точка)Десятичная точка 
, (запятая)Разделитель тысяч.###,###.## — отображает разделитель тысяч и два знака после запятой.Если вы наберете 250000 - отобразится как 250,000.00 

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

СимволОписание
+ и -Знаки плюс и минус
( )Левая и правая скобка
:Двоеточие
^Степень
'Апостроф
{ }Фигурные скобки
< >Знаки «меньше» и «больше»
"="Знак равенства
/Косая черта
!Восклицательный знак
&Амперсанд
~Тильда

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

 АВС
1Исходное значениеФорматированное значениеФормула
25.55.50=ТЕКСТ($A$2; "0.00")
3 550%=ТЕКСТ($A$2;"#%")
4 $,5.50=ТЕКСТ(A2; "$#,##0.00")
5 + $,5.50=ТЕКСТ($A$2; "+ $#,##0.00;- $#,##0.00;$0.00")
6  5.50 ₽ =ТЕКСТ($A$2; "_-* # ##0.00 ₽_-;-* # ##0.00 ₽_-;_-* -?? ₽_-;_-@_-")
7  11/2=ТЕКСТ($A$2;" ##??/?")
8 5.50E+00=ТЕКСТ($A$2;"0.00E+00")
9 ~!=ТЕКСТ($A$2;"~# !")

При использовании функции ТЕКСТ Excel с датами и временем вы можете использовать любой из следующих кодов формата.

Код форматаОписаниеПримеры
дДень месяца илидень неделид – одно- или двузначное число без ведущего нуля (от 1 до 31)дд — двузначное число с ведущим нулем (от 01 до 31)ддд — трехбуквенная аббревиатура (с понедельника по воскресенье)дддд - полное название дня недели (с понедельника по воскресенье)
мМесяц (при использовании как часть даты)м — одно- или двузначное число без ведущего нуля (от 1 до 12)мм – двузначное число с ведущим нулем (от 01 до 12)ммм — сокращенный месяц (с января по декабрь)мммм — полное название месяца (с января по декабрь)
гГодгг — двузначное число (например, 06 означает 2006 год или 16 означает 2016 год)гггг — четырехзначное число (например, 2006, 2016)
чЧасч – одно- или двузначное число без ведущего нуля (от 1 до 24)чч — двузначное число с ведущим нулем (от 01 до 24)
мМинута (при использовании как часть времени)м – одно- или двузначное число без ведущего нуля (от 1 до 60)мм – двузначное число с ведущим нулем (от 01 до 60)
сСекундас – одно- или двузначное число без ведущего нуля (от 1 до 60)сс — двузначное число с ведущим нулем (от 01 до 60)
AM/PMВремя представлено в виде 12-часового формата, за которым следует «AM» или «PM» (американский формат времени - До полудня, После полудня).

В следующей таблице показано несколько текстовых формул Excel для дат:

АВС
1ДатаДата в различных форматахФормула
215.07.2024Июль 15 2024=ТЕКСТ($A$2; "ММММ дд ггг")
3 15 июля 2024=ТЕКСТ($A$2; "[$-ru-RU-x-genlower]Д ММММ ГГГГ")
4 15 Июль 2024=ТЕКСТ($A$2; "ДД ММММ ГГГГ")
5 понедельник=ТЕКСТ($A$2;"ДДДД")

До сих пор у вас могло сложиться впечатление, что использование функции ТЕКСТ в Excel весьма ограничено (поскольку отображение значения можно легко изменить с помощью диалогового окна «Формат ячеек» , доступного через контекстное меню ячейки или сочетание клавиш Ctrl+1). 

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

Как объединить текст и число (или дату) в произвольном формате.

При создании документов или отчетов вам часто может потребоваться не только посчитать итоги, проценты и тому подобное, но и объяснить вашим пользователям, что означает то или иное число. Для этого используйте функцию СЦЕПИТЬ , чтобы объединить текст и числа, и функцию ТЕКСТ, чтобы отобразить число (или дату) так, как вы желаете.

Пример 1. Форматирование чисел внутри текстовой строки

Предположим, вы рассчитываете общую сумму на основе цены за единицу в ячейке A2, количества в B2 и процента скидки в C2, используя следующий расчет: =A2*B2*(1-C2). Чтобы ваши пользователи точно знали, что означает итоговый результат, вы хотите отобразить его вместе с пояснительным текстом, например «Ваша цена ». Кроме того, вы хотите отобразить символ валюты, разделитель тысяч и два десятичных знака.

Чтобы это сделать, укажите приведенное выше вычисление в первом аргументе функции ТЕКСТ, затем укажите соответствующий код формата во втором аргументе и объедините формулу ТЕКСТ со текстовой строкой, используя либо оператор амперсанда, либо функцию СЦЕПИТЬ:

="Итоговая цена "&ТЕКСТ(A2*B2*(100%-C2); "###,###.00 ₽") 

или

=СЦЕПИТЬ("Итоговая цена ";ТЕКСТ(A2*B2*(100%-C2);"###,###.00 ₽")) 

Ниже вы можете видеть результат:

Форматирование чисел в тексте Excel

Подробные инструкции по форматированию чисел представлены в этой статье: Как сделать пользовательский числовой формат в Excel.

Пример 2. Объединяем текст и дату в нужном формате

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

Однако если вы попытаетесь объединить текст и дату обычным способом:

=СЦЕПИТЬ("Сегодня ";СЕГОДНЯ())

Excel вернет очень странный результат, что-то вроде « Сегодня 45418 ».

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

Например, встраивание функции СЕГОДНЯ в формулу текста с кодом формата "ДДДД, Д ММММ ГГГГ " вернет текст наподобие этого: «Сегодня понедельник, 6 май 2024».

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

=СЦЕПИТЬ("Сегодня ";ТЕКСТ(СЕГОДНЯ();"ДДДД, Д ММММ ГГГГ "))

или

="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДДДД, Д ММММ ГГГГ ")

Как записать дату в тексте Excel

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

Пример 3. Функция ТЕКСТ в формулах расчета.

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

=ЕСЛИ(РАЗНДАТ(B2;B3;"y");РАЗНДАТ(B2;B3;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)&ЕСЛИ(РАЗНДАТ(B2;B3;"ym");РАЗНДАТ(B2;B3;"ym")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;B3;"ym")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)&ЕСЛИ(РАЗНДАТ(B2;B3;"md");РАЗНДАТ(B2;B3;"md")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"md")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)

Функция ТЕКСТ в формуле разности дат

Как видите, разность дат здесь посчитана при помощи функции РАЗНДАТ, а функция ТЕКСТ помогает объединить все рассчитанные числа в одну текстовую строку и представить их в удобочитаемом виде, даже с учетом падежей.

Более подробно об этом и других методах расчета разности дат читайте здесь: Как посчитать количество дней между датами? Примеры и советы.

Как добавить ведущие нули к числам различной длины

Как вы знаете, Microsoft Excel автоматически удаляет ведущие нули, введенные перед числом в ячейке, что отлично работает в большинстве ситуаций. Но что, если вы хотите сохранить нули перед числом?

Функция Excel ТЕКСТ может быть простым решением для дополнения чисел в столбце ведущими нулями, даже если исходные значения имеют разное число цифр. Просто используйте код формата, содержащий только нули, например «0000000», где их количество соответствует количеству цифр, которые вы хотите отобразить.

Например, чтобы отобразить семизначные числа с ведущими нулями, используйте следующую формулу (где A2 — исходное число):

=ТЕКСТ(A2;"0000000")

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

как добавить ведущие нули перед числом Excel

Преобразование значений в номера телефонов в определенном формате

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

Итак, чтобы отобразить число в ячейке A2 в семизначном телефонном формате, например 123-4567, используйте следующую формулу:

=ТЕКСТ (A2; "###-####")

Если некоторые из исходных значений могут содержать префикс (т. е. среди них могут быть как 7-значные, так и 10-значные числа), включите следующий код условного числового формата для отображения 10-значных чисел в формате (123) 456-7891:

=ТЕКСТ(A2;"[<=9999999]###-####;(###) ###-####")

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

число как номер телефона в Excel

Функция Excel ТЕКСТ не работает – причины и решения

По сравнению с другими функциями Excel, ТЕКСТ очень проста и удобна, и у вас вряд ли возникнут какие-либо трудности при ее использовании в электронных таблицах. 

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

  1. Функция ТЕКСТ возвращает ошибку #ИМЯ?, если вы опустите кавычки вокруг кода формата.

Например, формула =ТЕКСТ(A2; дд-мм-гг) неверна и ее следует записать так: =ТЕКСТ(A2; “дд-мм-гг”)

  1. Функция ТЕКСТ в Excel зависит от языка и требует использования кодов формата даты и времени применительно к конкретному региону.

Например, формула =ТЕКСТ(A2; "mm/dd/yy"), которая хорошо работает для пользователей английского языка, может возвращать ошибку #ЗНАЧ в русском языковом стандарте. 

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

Другие функции Excel для работы с текстом

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

Так можно использовать функцию ТЕКСТ в Excel.

Надеюсь, эта информация была вам полезна.