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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читайте также  Нумерация столбцов в Microsoft Excel

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

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

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


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

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

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

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

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

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

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

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


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

    Замечания

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Функция ПСТР

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

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

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

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

    Читайте также  Разворот таблицы в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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


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

    Читайте также  Построение графика в программе Microsoft Excel

    Замечания

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

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

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

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

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

    Текстовые функции 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″».