Как вставить текст в формулу excel

Как отобразить текст и число в одной ячейке

Иногда необходимо указывать в одной ячейке текст и значение. Например, в конце таблицы с товарами указать «Итого: 123,45». В Excel это можно сделать несколькими способами.

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

• Конкатенация;
• Функция СЦЕПИТЬ;
• Функция ТЕКСТ;
• Пользовательский формат.

Разберем эти способы и рассмотрим плюсы и минусы каждого из них.

Использование конкатенации

Один из самых простых способов реализовать сочетание текста и значения — использовать конкатенацию (символ &).

Допустим ячейка A1 содержит итоговое значение 123,45, тогда в любой другой ячейке можно записать формулу
=»Итого: «&A1

В итоге результатом будет следующее содержание ячейки Итого: 123,45.

Это простое решение, однако имеет много минусов.

1. Результатом формулы будет текстовое значение, которое нельзя будет использовать при дальнейших вычислениях.
2. Значение ячейки A1 будет выводится в общем формате, без возможности всякого форматирования. В следствие чего этот метод не всегда применим.

Применение функции СЦЕПИТЬ

Аналогичное простое решение, но с теми же недостатками — использование функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ(«Итого: «;A1). Результаты ее использования аналогичные:

Применение функции ТЕКСТ

Функция ТЕКСТ позволяет не только объединить текст и значение, но еще и отформатировать значение в нужном формате. Если мы применим следующую формулу =ТЕКСТ(A1;»»»Итого: «»##0»), то мы получим такой результат Итого: 123.

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

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

Использование пользовательского формата

Не такой простой способ как предыдущие, но наиболее функциональный. Заключается в применении к итоговой ячейки пользовательского числового формата. Чтобы добавить текст «Итого» к ячейке A1 необходимы следующие действия:

1. Выберите ячейку A1.
2. Откройте диалоговое окно Формат ячейки.
   
3. В поле Тип укажите нужный формат. В нашем случае «Итого: «# ##0.
   

В результате ячейка A1 будет содержать Итого: 123.

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

Плюсы и минусы методов

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

Как вставить текст в формулу excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Использование текста в формулах

Мы часто слышим, что вы хотите упростить понимание данных, включив в формулы текст, например «2347 проданных единиц». Чтобы включить текст в функции и формулы, заключите текст двойными кавычками («»). В кавычках excel говорится, что он имеет дело с текстом, и под текстом мы подразумеваем любой символ, включая числа, пробелы и знаки препинания. Пример:

=A2&» продано единиц &B2&».

В этом примере притворитесь, что ячейки в столбце A содержат имена, а ячейки в столбце B содержат номера продаж. Результатом будет что-то вроде: Бьюкенен продал 234 единицы.

Формула использует амперсанды (&) для объединения значений в столбцах A и B с текстом. Кроме того, обратите внимание, что кавычки не окружают ячейку B2. Они заключают текст, который поставляется до и после ячейки.

Вот еще один пример общей задачи, добавляя дату на лист. Он использует функции TEXT и TODAY для создания фразы, такой как «Сегодня пятница, 20 января».

=»Today is » & TEXT(TODAY(),»dddd, mmmm dd.»)

Давайте посмотрим, как это работает изнутри. Функция СЕГОДНЯ вычисляет текущую дату, но отображает число, например 40679. Затем функция TEXT преобразует число в читаемую дату, сначала изменяя число на текст, а затем используя «dddd, mmmm dd» для управления тем, как отображается дата — «Пятница, 20 января».

Убедитесь, что вы окружаете формат даты ddd, mmmm dd с двойными кавычками и обратите внимание, как формат использует запятые и пробелы. Обычно формулы используют запятые для разделения аргументов (фрагментов данных), которые необходимо выполнить. Но при обработке запятых как текста их можно использовать в любое время.

Наконец, формула использует & для объединения форматированных дат со словами «Сегодня является «. И да, поместите пробел после «is».

Объединение текста и чисел

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

Использование числового формата для отображения текста до или после числа в ячейке

Если столбец, который требуется отсортировать, содержит как числа, так и текст(например, Продукт No 15, Продукт 100, Продукт 200), он может сортироваться неправильно. Ячейки, содержащие 15, 100 и 200, можно отформатировать таким образом, чтобы они отображались на листе как Product #15, Product #100 и Product #200.

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

1. Выделите ячейки, которые нужно отформатировать.
2. На вкладке Главная в группе Число щелкните стрелку .

Для отображения

Используйте код

Как это работает

12 — продукт No 12

Текст, заключенный в кавычки (включая пробел), отображается перед числом в ячейке. В коде «0» представляет число, содержащееся в ячейке (например, 12).

12:00 по 12:00 EST

Текущее время отображается в формате даты и времени h:mm AM/PM, а текст «EST» отображается после времени.

-12 как $-12.00 Дефицит и 12 как избыток $12,00

$0,00 «избыток»;$0,00 «Дефицит»

Значение отображается в формате валюты. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается значение «Избыток». Если ячейка содержит отрицательное значение, вместо этого отображается слово «Нехватка».

Объединение текста и чисел из разных ячеек в одну ячейку с помощью формулы

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

Для объединения чисел используйте функции CONCATENATE или CONCAT, TEXTили TEXTJOIN и оператор амперсанд (&).

• В Excel 2016, Excel Mobile и Excel в Интернетесцепление заменено функцией CONCAT . Хотя функция CONCATENATE по-прежнему доступна для обратной совместимости, рекомендуется использовать CONCAT, так как в будущих версиях Excel сцепление может быть недоступно.
• ФУНКЦИЯ TEXTJOIN объединяет текст из нескольких диапазонов и (или) строк и включает разделитель, указанный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. ФУНКЦИЯ TEXTJOIN недоступна в Excel 2013 и предыдущих версиях.

Примеры

См. различные примеры на рисунке ниже.

Внимательно изучите использование функции TEXT во втором примере на рисунке. При соединении числа со строкой текста с помощью оператора объединения используйте функцию TEXT для управления способом отображения числа. Формула использует базовое значение из указанной ячейки (.4 в этом примере), а не отформатированное значение, которое отображается в ячейке (40 %). Функция TEXT используется для восстановления форматирования чисел.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.