Функция Excel ТЕКСТ с примерами формул

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

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

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

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

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

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

Где:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

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

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

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

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

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

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

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

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

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

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

или

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

В этой статье вы можете найти различные форматы дат и выбрать из них наиболее подходящий для себя: Как изменить формат даты в 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 ТЕКСТ позволяет использовать тире и круглые скобки в кодах форматов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Как разделить текст из одной ячейки в несколько
• Как перенести текст в ячейке на следующую строку?
• Как преобразовать текст в число 
• Как быстро извлечь число из текста
• Извлекаем имя, фамилию, а также любое слово из ячейки с текстом
• Извлекаем текст слева от определённого символа
• Как получить текст справа после определённого символа
• 7 примеров удаления пробелов в ячейках
• 4 способа убрать пробелы в числах
• 4 метода быстро удалить перенос строки в ячейках
• Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел
• Как удалить символы в ячейке Excel
• Функции НАЙТИ и ПОИСК с примерами формул
• Как удалить цифры и оставить текст в ячейке или наоборот
• Как удалить лишние и нежелательные символы из ячейки

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

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

Как преобразовать текст в число в Excel — 10 способов.

Как быстро сосчитать количество символов в ячейке Excel

5 примеров с функцией ДЛСТР в Excel

Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов

Функция ЛЕВСИМВ в Excel. Примеры использования и советы.

Функция ПРАВСИМВ в Excel — примеры и советы.

Функция СЖПРОБЕЛЫ — как пользоваться и примеры

Как удалить пробелы в ячейках Excel