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

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

Смотрите также (формула) «.чтобы на февраль. за которую продалиС уважением.Stormy Day формуле нужно указать: Использую функцию ВПР. в двойные кавычки. может потребоваться добавить статью «Посчитать количество ячейке будет стоять с помощью операторзаменена после времени.В поле числа и текстПримечание:Полосатый жираф аликbuchlotnik данный товар заPelena

: Всем огромное спасибо! ссылку на ячейку, =ВПР (C3;’Лист1′!B:R;4). Таблица Кавычки используются только текст для выделения слов в Excel» решетка (;;#). Это объединения, используйте функциюфункции СЦЕПИТЬ-12 как -12р. дефицитТип — 15 #Мы стараемся как: Или еще проще:

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

: ну да - следующий месяц (например:Stormy Day в которой уже находится на листе1, для текста, который сколько кто-то проданных тут. значит, что втекст. Несмотря на и 12 какизмените коды числовых продукта, продукт #100 можно оперативнее обеспечивать

=»»»Уровень страховки: (« чтоб тянуть по февраль»).200?’200px’:»+(this.scrollHeight+5)+’px’);»>=»Иванов «&СУММ(A1:A6): Народ помогайте, задача будет указан номер а результат и находится в формуле

единиц товара. Это

Какие бывают символы, ячейке стоит формат

, чтобы управлять то, что функция 12р. избыток форматов в формате, и 200 #
Кнопка вызова диалогового окна в группе

вас актуальными справочными & формула & горизонтали — упустилДалее идет своднаяили, если фамилия усложняется. Как с листа. сама формула пишется

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

0.00р. «избыток»;-0.00р. «дефицит» который вы хотите продукта — не материалами на вашем «)»»» — пора баюшки таблица, в которой в отдельной ячейке
 помощью функции СУММЕСЛИKayrat на листе 5. ссылок на ячейки.

 текст в формуле,

 как они работают

 формат на «Общий».

В формуле используется

по-прежнему доступен для

Это значение отображается в создать. может сортировать должным языке. Эта страницаЕсли нужны иstoniq присутствует первая колонка (B1)

посчитать красные яблоки: Попробуйте такую конструкцию

Необходимо брать данные

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

обеспечения обратной совместимости, формате «Денежный». КромеДля отображения текста и

образом. Можно отформатировать

переведена автоматически, поэтому открывающая, и закрывающая: тут всё сложнее с уникальным названиемCode200?’200px’:»+(this.scrollHeight+5)+’px’);»>=B1&» «&СУММ(A1:A6) или желтые? Нужно Код =ВПР(C3;ДВССЫЛ(A1&»!B:R»);4) из разных листов. текст и сегодня кавычки («»). Все многое другое о

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

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

Гость както прописать двойноеVlad999 Суть вопроса: Как для составления фразы, символы (включая цифры,

 символах, знаках, читайтеЕсли нужно

указывает ссылка (вОБЪЕДИНЕНИЯ ячейке находится положительное заключите текст в 100 и 200, содержать неточности иДмитрий ширяев совсем точный получился, с суммой продаж: Pelena, Спасибо за условие: Примерно так прописать в формуле, такие как пробелы и знаки в статье «Символсделать в ячейке данном примере.4) —

, так как значение (или 0), двойные кавычки (» чтобы они отображались грамматические ошибки. Для: можно «=СЦЕПИТЬ (. « общая таблица на за январь, в помощь.Извините забыл прикрепитьКод =ВПР(C3;ДВССЫЛ(«‘»&$C$1&»‘!B:R»);4) чтобы наименование листасегодня Пятница, 20 янв. препинания) между двойные в Excel».

Примеры

Excel текст без абзаца, не форматированное значение,

функции СЦЕПИТЬ после него будет «), или чисел на листе как нас важно, чтобыСергей пимкин отдельном листе(( т.е. третьей сумма продажА если вот примерИспользуйте функциюStormy Day вставлялось к примеру= «Сегодня» & текст кавычки будут показаныПримечание: без разрыва строк отображаемое в ячейкемогут быть недоступны показан текст «(излишек)», с помощью обратной 15 # продукт, эта статья была: Используй команду СЦЕПИТЬ что-то вроде

Примеры объединения текста и чисел

См. также

такая формула с

Код СУММЕСЛИМН(диапазон_суммирования, диапазон_условий1,

Как вставить в ячейку Excel текст Word.

из ячейки С1. (сегодня (); «дддд, в результате какМы стараемся как, то установим формулу (40%). Чтобы восстановить в будущих версиях а если ячейка косой черты (\) продукт #100 и вам полезна. Просим
СЦЕПИТЬ («Уровень страховки:=СУММЕСЛИМН(‘Лист1’B$2:B$15;’Лист1’$A$2:$A$15;»Яблоко») протягивании удалось зафиксировать
Как вставить в Excel текст Word. выбором условие1, [диапазон_условий2, условие2], одним вопросом: В ячейке С1 мммм дд») текст. можно оперативнее обеспечивать . форматов чисел используйте Excel. содержит отрицательное значение,
в начале. 200 # продукта.  вас уделить пару «;формула)Как тут быть? суммы.
=СЧЁТЕСЛИ(K4:K243;»Иванов») подсчет идет …) СУММЕСЛИМНЕсть таблица, например
я прописываю «Лист1″Функция TODAY вычисляет текущуюВот пример простой формулы, вас актуальными справочнымиВ ячейки столбца
функциюTEXTJOIN после него будетПримечание:Использование пользовательского числового формата секунд и сообщить,Дмитрий ткачукbuchlotnikКак можно при кол-ва «Иванов» вЕсли версия ниже с фруктами и и этот текст
дату, а функция которая содержит текст: материалами на вашем С устанавливаем формулытекстОбъединение текста из
показан текст «(недостаток)».изменение встроенного формата для отображения номера помогла ли она: Можно еще проще: А какая разница? протягивании зафиксировать текст шт.

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

 2007, то пользуйтесь количеством, но в автоматически вставляется в текст применяется= A2 &» продал языке. Эта страница с функцией «ПОДСТАВИТЬ».. нескольких диапазонах и/илиПри объединении чисел и не приводит к с текстом, не вам, с помощьюпросто текст пишем вы бы в в формуле зависимото как сделать?=Если( ) ней наименования повторяются формулу. У менядддд, мммм дд «& B2 &»

переведена автоматически, поэтомуКод 10 вФункция СЦЕПИТЬ строки, а также текста в ячейке, удалению формат. изменяя порядок сортировки кнопок внизу страницы. в кавычках, между файле показали от строчки? Т.е._Boroda_и в ней по несколько раз. ни как неформат даты, чтобы единицы.» ее текст может формуле – это

СЦЕП разделитель, указанный между

текст и большеДля отображения число. Таким образом

Для удобства также текстом и формулойstoniq чтобы каждый раз: =»Иванов — «&СЧЁТЕСЛИ(K4:K243;»Иванов»)&» указав нужные условияМоя задача сделать

получается. отобразить, дату какЕсли ячейки в столбце содержать неточности и код перевода строкиФункция ТЕКСТ каждой парой значений, не функция числаИспользуйте код изменение способа отображения приводим ссылку на ставим &: Вот не вставлять название шт.» вставляйте выборки по наименованиямПопробую уточнить вопрос:Пятница, 20 янв

Использование текста в формуле, содержащей функцию

A содержат фамилии, грамматические ошибки. Для (заменили код «абзац»Функция TEXTJOIN который будет добавляться

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

оригинал (на английском=СУММ (E2:E6)&»+»buchlotnik продукта, а простоГостьСУММ и посчитать общее есть функция ВПР,в результирующую строку

а ячейки в нас важно, чтобы из Word).Когда текст. Если разделитель значения. Это означает,12 как Продукт №12 значения. языке) .

вот эта штука: ну и? то протянуть? При протягивании: _Boroda_, Огромное Спасибо,3 вариантатогда лучше использовать количество каждого наименования.

Как в Exel вставить в формулу текст из ячейки?

 выглядит следующим образом: текста. столбце B проданных эта статья былаКак убрать лишниевставляем текст из таблицы пустую текстовую строку, что больше не»Продукт № » 0Выполните следующие действия.Предположим, что нужно создать помогла мне написать же самое берет значение верхней ВСЁ получилось. СУММПРОИЗВ Подскажите какую функцию =ВПР (C3;’Лист1′!B:R;4), мнеОбратите внимание: формат даты единиц, результат будет вам полезна. Просим
пробелы в Excel, Word в Excel эта функция будет может выполнять любыеТекст, заключенный в кавычкиВыделите ячейки, формат которых предложение грамматически правильные 254+ , где200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(Лист1!B$2:B$15;Лист1!$A$2:$A$15;$A2) ячейки (отметил желтымС уважением Анатолий.Не по теме: использовать. нужно, чтобы значениедддд, дд МММ примерно такой: вас уделить пару читайте в статьепростым копированием, то эффективно объединять диапазоны. математических операций на (включая пробел) отображается требуется изменить. из нескольких столбцов

 254 — посчитано,stoniq цветом).

stoniqне люблю я ее
Kayrat

 ‘Лист1’ было переменным.заключен в кавычкикрюков продал 234 единицы.
секунд и сообщить, «Как удалить лишние он вставляется вTEXTJOIN них.
в ячейке передНа вкладке данных для подготовки а + я: И правда пораПример во вложении.

: Всем привет. Прошу :(: файл не смотрел Т.е. не изменяя

 и в немДля объединения значений из помогла ли она пробелы в Excel».
несколько строк (С20,
в Excel 2013

Для объединения чисел с числом. В этомГлавная массовой рассылки. Или,

 добавил сам в спать, спасибо вам

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

Получилось так. С21). Например, так.
и более ранние помощью функции СЦЕПИТЬ коде «0» обозначает
в группе возможно, вам нужно
 кавычках после &
добрый человек!: формула:В столбце А: Как вставить в лучше сводную таблицу. какой-либо ячейке указываю

 пробелы. Если запятые

В с текстом кнопок внизу страницы.

Текс в ячейке с формулой (Как вставить в ячейку текст рядом с формулой)

Если нужно посчитатьЕсли вставляем текст Word версии не поддерживается. или функции ОБЪЕДИНЕНИЯ, число, которое содержится
число форматирование чисел сне уверен, на_Boroda_200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(B$2:B$15;$A$2:$A$15;E2) фиксируется название проданного
ячейку текст рядом
Kayrat

 Лист1 Функция делает рассматриваются как текст, в формуле используются
Для удобства также количество символов, букв, в ячейку Excel,
Примеры различных на рисунке

 текст и TEXTJOIN в ячейке (например,щелкните стрелку.
текстом, не затрагивая всех ли экселях: А зачем СУММЕСЛИМН,
но вот это продукта (названия повторяются с формулой(итоговой) чтобы
:

 выборку из таблицы их можно использовать амперсанды (

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

Вставить в формулу текст из другой ячейки (Формулы/Formulas)

 и амперсанд (&) 12).В списке
формулы, которые могут работает, но на если вроде подходит немножко другое - в зависимости от выглядела вот так:Kayrat на Листе1, меняю по необходимости.& оригинал (на английском то смотрите статью так.Внимательно посмотрите на использование оператор.
12:00 как 12:00 центральноевропейскоекатегории использовать эти числа. 10 и более более шустрая СУММЕСЛИ? см. файл количества продаж), в»Иванов 65″. В, в ячейке значениеНаконец в формуле используется
). Обратите внимание: ссылки языке) . «Как посчитать количествоА нам нужно функцииПримечания: времявыберите категорию, например В Excel существует поздних проверял, все200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(Лист1!$A$2:$A$15;$A2;Лист1!B$2:B$15)
stoniq

 столбце В фиксируется столбце просчитана допустим Так можно на Лист2 выборки& на ячейки (в

Часто услышите сообщение, что символов в ячейке
вставить в ячейкутекст ч:мм «центральноевропейское время»настраиваемые

 несколько способов для в нормеДо кучи еще: По сводной понял) сумма, за которую

 сумма — 65.Код =СУММЕСЛИ(B$2:B$12;»=яблоки»;C$2:C$12) делается с листадля объединения слова этом примере — вы хотите сделать Excel».
Excel текст так.
во втором примере

В Excel 2016Excel Mobile иТекущее время показано ви нажмите кнопку встроенный объединения текста и

Этот метод избавляетКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((Лист1!$A$2:$A$15=$A2)*Лист1!B$2:B$15)

По поводу формулы продали данный товар Надо чтобы формула Kayrat

 2. При этомсегодняA2 данные проще было

Можно посчитать количествоЕсли текст скопировали на рисунке. При Excel Online с
формате даты/времени ч:мм
формат, который похож
чисел.
о необходимости ставить

Можно ли в экселе писать текст и формулу в одной ячейке? Если можно, то как?

но она потормознутее большое спасибо, кажется за месяц (например

 работала но спереди: Согласна с саму формулу не, и пробел си
понять, включая текст слов в ячейке. большой (больше 255

 присоединении к числа помощью функции

 AM/PM, а текст на то, которое
Если столбец, который вы адское количество кавычек

К примеру мне надо нужно добавить еще
январь), в столбце вставить текст.Vlad999 меняю. Те вместо
отформатированной датой.
B2 в формулах. Например Об этом читайте символов), то в в строку текстаОБЪЕДИНЕНИЯ «московское время» отображается
вы хотите. хотите отсортировать содержит и скобок написать «Уровень страховки:$E С фиксируется сумма,
Надеюсь понятно., сводной проще всего текста ‘Лист1’ вKayrat

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

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

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

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

Вот еще один пример распространенной задачи— добавление даты на таблицу. Функции ТЕКСТ и СЕГОДНЯ используются для создания таких фраз, как «Сегодня пятница, 20 января».

=»Сегодня » & ТЕКСТ(СЕГОДНЯ(),»дддд, мммм.дд.»)

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

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

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

Excel формула текст

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

«Общая сумма затрат»

«Вставить функцию»

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

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

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

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

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

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

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

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

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

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

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

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

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

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(“Произвольный текст”;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(“произвольный текст”;5) – возвращенное значение «текст».

Функция ДЛСТР

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

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

искомый_текст – строка, которую необходимо найти;
текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(“старый”;A1);ДЛСТР(“старый”);”новый”)

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

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

текст – строка или ссылка на ячейку, содержащую текст;
старый_текст – подстрока из первого аргумента, которую необходимо заменить;
новый_текст – строка для подмены старого текста;
номер_вхождения – необязательный аргумент. Принимает целое число, указывающее порядковый номер вхождения старый_текст, которое подлежит замене, все остальные вхождения затронуты не будут. Если оставить аргумент пустым, то будут заменены все вхождения.

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

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

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=”Слово1″&” “&“Слово2″».

Как преобразовать формулу в текст в Excel?

Познакомимся с вариантами преобразования формулы Excel в текст (в результате получается не значение ячейки, а формульное выражение в текстовом виде, например, “=A1+A2”, “=СЕГОДНЯ()” и т.д.).

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

Преобразование формулы в текст в Excel

Начнем с более простого варианта, а именно предположим, что нам нужно преобразовать формулу в текст в самой ячейке (т.е. заменить значение на текстовую запись). Тогда в этом случае есть несколько способов преобразования:

Поменять формат ячейки на текстовый, а затем произвести вычисление формулы; При этом для каждой ячейки нужно будет вручную производить изменение.
Добавить апостроф (символ «‘») перед знаком равно (символ «=») в формульном выражении. В данном варианте подставить апостроф можно как вручную, так и через замену («=» на «‘=» с помощью инструмента «Найти и заменить»).

Теперь перейдем к более общему случаю и рассмотрим 2 основных варианта перевода формулы в текст (т.е. получить текстовую запись):

Функция Ф.ТЕКСТ (доступна начиная с версии Excel 2013);
Пользовательская функция (UDF).

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

Функция Ф.ТЕКСТ в Excel

Начиная с версии Excel 2013 для применения доступна функция Ф.ТЕКСТ (FORMULATEXT в английской версии):

Ф.ТЕКСТ(ссылка)
Возвращает формулу в виде строки.

Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон ячеек.

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

При этом в зависимости от выбранного у вас параметра отображения стиля ссылок (A1 или R1C1) формула автоматически будет подстраиваться под формат записи:

Замечания

При работе с данной функцией есть несколько важных особенностей, на которые необходимо обратить внимание:

Аргумент “Ссылка” может ссылаться на другие листы и книги;
Если аргумент “Ссылка” не содержит формульное выражение или содержит ссылку на закрытую книгу, то в результате будет возвращено значение ошибки.

Пользовательская функция (UDF)

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

Перейдем в редактор Visual Basic (сочетание клавиш Alt + F11), вставляем новый модуль и добавляем следующий код:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ТЕКСТ (TEXT)

Например, чтобы отформатировать число 23,5 как сумму в рублях, можно использовать указанную ниже формулу:

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

Результат: 23,50р. в час .

Заполнитель	Описание
0 (ноль)	Отображает незначащие нули, если число имеет меньше разрядов, чем количество нулей в строке форматирования. Например, формат #,00 позволяет отобразить значение 8,9 как 8,90.
#	Работает так же, как 0 (ноль). Но в этом случае функция Text не возвращает дополнительные нули, если число имеет слева или справа от десятичного разделителя меньше цифр, чем количество символов # в строке форматирования. Например, число 8,9 при использовании формата #,## будет отображаться как 8,9.
?	Действует по тем же правилам, что и 0 (нуль). Однако, до и после запятой вместо незначащих нулей отображаются пробелы, чтобы десятичные разделители были выровнены в столбце. Например, формат 0,0? обеспечивает выравнивание запятых для чисел 8,9 и 88,99. (Правда, для чистоты эксперимента, следует выбрать моноширинный шрифт)
, (запятая)	Отображает символ десятичного разделителя. Поведение этого символа зависит от языка пользовательского формата.

Если в дробной части числа больше цифр, чем заполнителей в формате, число округляется до количества разрядов, равного количеству заполнителей. Если в целой части числа больше цифр, чем заполнителей в формате, отображаются дополнительные цифры. Если формат содержит слева от запятой только знаки номера ( #), то числа, меньшие 1, будут начинаться с запятой, например ,47.

Для отображения	В виде	Используемый формат
1234,59	1234,6	«####,#»
8,9	8,900	«#,000»
0,631	0,6	«0,#»
12 1234,568	12,0 1234,57	«#,0#»
44,398 102,65 2,8	44,398 102,65 2,8 (с выравниванием запятых)	«. »
5,25 5,3	5 1/4 5 3/10 (с выравниванием дробных частей)	«# . /. »

Разделитель групп разрядов

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

Для отображения	В виде	Используемый формат
12000	12 000	«# ###»
12000	12	«# «
12200000	12,2	«0,0 «

Форматы даты и времени. Дни, месяцы и годы. Для отображения чисел в формате даты (с указанием числа, месяца и года) в аргументе формат используются коды, указанные ниже.

Формат	Описание
М	Отображение месяца в виде числа без начального нуля.
ММ	Отображение месяца в виде числа с начальным нулем, если он необходим.
МММ	Отображение сокращенного названия месяца (Янв–Дек).
ММММ	Отображение полного названия месяца (Январь–Декабрь).
МММММ	Отображение месяца в виде одной буквы (Я–Д).
Д	Отображение дня в виде числа без начального нуля.
ДД	Отображение дня в виде числа с начальным нулем, если он необходим.
ДДД	Отображение сокращенного названия дня недели (пн–вс).
ДДДД	Отображение полного названия дня недели (понедельник–воскресенье).
ГГ	Отображение года в виде двузначного числа.
ГГГГ	Отображение года в виде четырехзначного числа.

Для отображения	В виде	Используется форма
Месяцев	1-12	«М»
Месяцев	01-12	«ММ»
Месяцев	Янв-Дек	«МММ»
Месяцев	Январь-Декабрь	«ММММ»
Месяцев	Я-Д	«МММММ»
Дней	1-31	«Д»
Дней	01-31	«ДД»
Дней	пн-вс	«ДДД»
Дней	понедельник-воскресенье	«ДДДД»
Лет	00-99	«ГГ»
Лет	1900-9999	«ГГГГ»

Часы, минуты и секунды

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

Форматы денежных сумм, процентов и экспоненциального представления

Символы валют. Чтобы указать перед числом знак доллара ($), введите его в начале аргумента формат (например, «$# ##0,00»). Чтобы включить в числовой формат один из указанных ниже символов валют, нажмите клавишу NUM LOCK и введите с цифровой клавиатуры код ANSI соответствующего символа.

Примечание. Необходимо использовать цифровую панель клавиатуры. При использовании клавиши ALT с цифрами верхнего ряда клавиш коды ANSI не генерируются.
Процентные значения. Чтобы отображать числа в виде процентов — например, 0,08 как 8% или 2,8 как 280%, — включите в аргумент формат знак процента (%).
Экспоненциальное представление. Для отображения чисел в экспоненциальном формате включите в аргумент формат код из числа указанных ниже.

Включение текста и добавление пробелов

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