Как добавить в excel к тексту в ячейке текст

Вставка текста в ячейку с формулой в Microsoft Excel

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

Процедура вставки текста около формулы

Если просто попробовать вставить текст в одну ячейку с функцией, то при такой попытке Excel выдаст сообщение об ошибке в формуле и не позволит совершить такую вставку. Но существует два способа все-таки вставить текст рядом с формульным выражением. Первый из них заключается в применении амперсанда, а второй – в использовании функции СЦЕПИТЬ.

Способ 1: использование амперсанда

Самый простой способ решить данную задачу – это применить символ амперсанда (&). Данный знак производит логическое отделение данных, которые содержит формула, от текстового выражения. Давайте посмотрим, как можно применить указанный способ на практике.

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

  1. Активируем ячейку, содержащую формульное выражение. Для этого либо производим по ней двойной щелчок левой кнопкой мыши, либо выделяем и жмем на функциональную клавишу F2. Также можно просто выделить ячейку, а потом поместить курсор в строку формул.

Сразу после формулы ставим знак амперсанд (&). Далее в кавычках записываем слово «рублей». При этом кавычки не будут отображаться в ячейке после числа выводимого формулой. Они просто служат указателем для программы, что это текст. Для того, чтобы вывести результат в ячейку, щелкаем по кнопке Enter на клавиатуре.

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

При этом, если мы попытаемся поставить пробел вручную, то это ничего не даст. Как только будет нажата кнопка Enter, результат снова «склеится».

  • Как видим, теперь результат вычисления формулы и текстовое выражение разделены пробелом.
  • Естественно, что все указанные действия проделывать не обязательно. Мы просто показали, что при обычном введении без второго амперсанда и кавычек с пробелом, формульные и текстовые данные сольются. Вы же можете установить правильный пробел ещё при выполнении второго пункта данного руководства.

    При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.

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

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

    Способ 2: применение функции СЦЕПИТЬ

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

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

    Посмотрим, как работает данная функция на практике. Для примера возьмем все ту же таблицу, только добавим в неё ещё один столбец «Общая сумма затрат» с пустой ячейкой.

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

    Производится активация Мастера функций. Перемещаемся в категорию «Текстовые». Далее выделяем наименование «СЦЕПИТЬ» и жмем на кнопку «OK».

    Запускается окошко аргументов оператора СЦЕПИТЬ. Данное окно состоит из полей под наименованием «Текст». Их количество достигает 255, но для нашего примера понадобится всего три поля. В первом мы разместим текст, во втором – ссылку на ячейку, в которой содержится формула, и в третьем опять разместим текст.

    Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.

    Потом переходим в поле «Текст2». Устанавливаем туда курсор. Нам нужно тут указать то значение, которое выводит формула, а значит, следует дать ссылку на ячейку, её содержащую. Это можно сделать, просто вписав адрес вручную, но лучше установить курсор в поле и кликнуть по ячейке, содержащей формулу на листе. Адрес отобразится в окошке аргументов автоматически.

    В поле «Текст3» вписываем слово «рублей».

    После этого щелкаем по кнопке «OK».

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

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

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

    =СЦЕПИТЬ(“Итого”;” “;D2;” “;”рублей”)

    Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.

    При желании можно спрятать первый столбец «Общая сумма затрат» с исходной формулой, чтобы он не занимал лишнее место на листе. Просто удалить его не получится, так как это нарушит функцию СЦЕПИТЬ, но убрать элемент вполне можно. Кликаем левой кнопкой мыши по сектору панели координат того столбца, который следует скрыть. После этого весь столбец выделяется. Щелкаем по выделению правой кнопкой мыши. Запускается контекстное меню. Выбираем в нем пункт «Скрыть».

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник: lumpics.ru

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

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

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

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

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

    Выполните указанные ниже действия.

    Выделите ячейки, которые нужно отформатировать.

    На вкладке Главная в группе число щелкните стрелку.

    В списке Категория выберите категорию, например ” Настраиваемая“, а затем — встроенный формат, похожий на нужный.

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

    Чтобы в ячейке отображались как текст, так и числа, заключите их в двойные кавычки (“”) или перед числами с помощью обратной косой черты ().

    Примечание. При редактировании встроенного формата формат не удаляется.

    12 как #12 продукта

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

    12:00 в качестве 12:00 AM

    Текущее время отображается в формате даты и времени, которое не входит в отчет, а текст “EST” отображается после времени.

    -12 в виде $-12,00 недостачи и 12 в $12,00 излишков

    $0,00 “излишки”; $-0,00 “недостачи”

    Значение отображается в денежном формате. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается “излишек”. Если ячейка содержит отрицательное значение, вместо этого отображается “нехватка”.

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

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

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

    В Excel 2016, Excel Mobile и Excel в Интернете функция сцепить была заменена функцией concat . Несмотря на то, что функция сцепить по-прежнему доступна для обеспечения обратной совместимости, следует использовать функцию concat, так как функция сцепить может быть недоступна в будущих версиях Excel.

    Объединить сочетает текст из нескольких диапазонов и (или) строк и включает разделитель, заданный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. Объединить недоступен в Excel 2013 и предыдущих версиях.

    Примеры

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

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

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

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    См. также

    Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

    Источник: support.office.com

    Добавить символ, слово или текст во все ячейки столбца

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

    Добавить значение в начало или конец ячеек формулой

    Можно решить задачу формулой «СЦЕПИТЬ» или простой конкатенацией через амперсанд (символ «&»). Но на это нужно много времени, т.к. нужно

    • добавить дополнительный столбец рядом с исходным
    • ввести формулу с функцией «СЦЕПИТЬ» или формулу с амперсандом в первую ячейку — здесь кому что больше нравится. =СЦЕПИТЬ(A1;» слово») или A1&» слово».
    • протянуть формулу до конца нового столбца, убедившись, что она затронула весь диапазон
    • заменить в нем формулы на значения
    • скопировать данные из нового столбца в первый
    • удалить новый столбец

    Ниже пример с добавлением одного символа, в данном случае — знака вопроса. Вместо него можно вписать внутри кавычек любое значение.

    Читайте также:  Как в excel снять защиту с ячейки

    формулы добавления символа к значению ячейки

    Могут возникнуть сложности с добавлением кавычек («) — поскольку формулы текстовые, аргумент указывается в кавычках, и во избежание ошибок одинарные кавычки обозначаются двойными, итого получается 4 знака подряд:

    «заключит» значение ячейки A1 в кавычки.

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

    Добавить символ в конце каждой ячейки

    Выделяем диапазон ячеек, жмем макрос, вводим необходимый символ и жмем ОК. Так просто. Данные изменяются в том же столбце.

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

    Как добавить символ в конце и начале ячеек столбца

    Допустим, у нас есть названия ООО, но они без кавычек и без «ООО».
    Ниже пример, как ускорить процесс.
    1. Добавления кавычек с обеих сторон ячейки — чтобы не производить две операции.

    Добавить символы в начале каждой ячейки

    2. Теперь добавим символы «ООО » в начале каждой ячейки, чтобы завершить задачу.

    Добавление символов в начале каждой ячейки выделенного столбца

    Добавить слово в каждую ячейку столбца

    Допустим, нужно добавить слово «выплатить» в начале каждой ячейки с ФИО определенного человека. Если исходные данные в именительном падеже, может быть также полезна процедура склонения по падежам в Excel.

    Выделяем столбец, склоняем ФИО, и далее выбираем процедуру в меню и вводим слово в окошко, вместе с пробелом после него:

    Вставляем слова в начале и конце каждой ячейки выбранных столбцов в Excel с помощью !SEMTools

    Добавить символы перед или после каждого слова

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

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

    Источник: semtools.guru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Источник: micro-solution.ru

    Добавить текст в ячейки Excel

    Для того чтобы в одной ячейке совместить как текст так и значение

    можно использовать следующие способы:

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

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

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

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

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

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

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

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

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

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

    Читайте также:  В excel не активно объединение ячеек

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

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

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

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

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

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

    1. Выберите ячейку A1.
    2. Откройте диалоговое окно Формат ячейки.

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

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

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

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


    Источник: officeassist.ru

    Функции для работы с текстом в Excel

    Для удобства работы с текстом в Excel существуют текстовые функции. Они облегчают обработку сразу сотен строк. Рассмотрим некоторые из них на примерах.

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

    Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

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

    Покажем на примере. Допустим, нужно объединить текст в строках и числовые значения:

    Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

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

    Формула «для даты» теперь выглядит так:

    Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

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

    Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «–»:

    Обратите внимание, что значения теперь отображаются в числовом формате.

    Функция разделения текста в Excel

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

    • ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
    • ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
    • ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

    В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(” “;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

    Формула для извлечения фамилии:

    С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

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

    Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(” “;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

    Часть формулы – ПОИСК(” “;A3;ПОИСК(” “;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

    Далее из общей длины строки отнимается количество знаков с начала строки до второго пробела. Результат – число символов справа, которые нужно вернуть.

    Формула «для отчества» строится по тем же принципам:

    Функция объединения текста в Excel

    Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

    Например, значения расположены в разных столбцах (ячейках):

    Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова – &. И так последовательно соединяем ячейки с текстом и пробелы.

    Получаем в одной ячейке объединенные значения:

    Использование функции СЦЕПИТЬ:

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

    Функция ПОИСК текста в Excel

    Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

    Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

    Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

    Синтаксис функции ПОИСК:

    • «искомый текст» – что нужно найти;
    • «просматриваемый текст» – где искать;
    • «начальная позиция» – с какой позиции начинать искать (по умолчанию – 1).

    Если нужно учитывать регистр, используется функция НАЙТИ.

    Источник: exceltable.com