В excel формулы текстовые

Текстовые функции в Excel. Часть №1

Добрый день уважаемый читатель!

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

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

Итак, рассмотрим первые 7 функций для работы с текстовыми значениями:

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

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

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

Синтаксис функции:

• текст – это прописанный вручную текст или ссылка на ячейку которая содержит текстовое значение.

Пример применения: Дополнительно ознакомится с функцией можно в статье «ТОП 10 функций Excel для SEO специалиста».

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

Функция призвана в указанных данных ячейки произвести замену по определённым параметрам. Попросту заменить некую старую часть текста на новую.

Синтаксис функции:

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

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

Простой пример применения:

Для начала рассмотрим сам механизм замены текста по указанным аргументам. В ячейке А1 мы производим замену с 20 символа, слова «СТАРЫЙ», которое состоит из 6 символов, на слово «НОВЫЙ». Этот способ вполне реален ежели необходима замена небольшого количества ячеек. Сложный пример применения:

Когда у нас очень большое количество различных строк, тогда простой способ нам не поможет, нужно что-то более универсальное и гибкое. Для этой задачи нужно заменить аргумент «начальная позиция» на функцию НАЙТИ (она будет искать нужное условие), а в аргументе «количество знаков» использовать функцию ДЛСТР (будет определять количество символов по условию). И как результат напишем формулу:

=ЗАМЕНИТЬ(A1;НАЙТИ(«ТОП»;A1);ДЛСТР(«ТОП»);»СУПЕР»)

Функция ЗНАЧЕН

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

Синтаксис функции:

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

Пример применения:

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

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

Синтаксис функции:

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

• текст – ссылка на ячейку или текст из которого нужно изъять символы;
• количество знаков – является необязательным аргументом. По умолчанию имеет значение 1. Это целое число, которое указывает сколько символов нужно достать из текста.

Пример применения: Более детально и шире с функцией можно ознакомится в статье «Функции ЛЕВСИМВ и ПРАВСИМВ в Excel».

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

Эта функция программный аналог возможности горячих клавиш Ctrl+F, «Найти», но имеет преимущество в автоматизме, но недостаток в сложности исполнения и сейчас это исправим, сложность я имел ввиду. Работа функции заключается в том, чтобы вернуть число, которое является началом вхождения первого символа текста, который мы ищем в указанной ячейке. Не стоить забывать, что функция НАЙТИ очень чувствительна к регистру значений и не стоит этого забывать. Еще нужно знать, что в случае, когда искомый текст не будет найден, получим ошибку «#ЗНАЧ!».

Синтаксис функции:

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

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

Пример применения: Более детально и шире с функцией можно ознакомится в статье «Функция НАЙТИ в Excel».

Функция ПОВТОР

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

Синтаксис функции:

= ПОВТОР(_текст_; _число_повторений_), где:

• текст – указывается текст или ссылка на него;
• число повторений – задается число повторов для первого аргумента.

Пример применения:

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

Эта функция является зеркальным отражением функции ЛЕВСИМВ, разница заключается только в том, что отсчёт идет не с начала, а с конца, справа налево.

Синтаксис функции:

= ПОВТОР(_текст_; _число повторений_), где:

• текст – указывается текст или ссылка на него;
• число повторений – задается число повторов для первого аргумента.

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

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

Функция ТЕКСТ() в MS EXCEL

Функция ТЕКСТ( ) , английская версия TEXT(), преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;»0,00 р.») вернет текстовую строку 100,00 р., а формула =ТЕКСТ(1;»#%») вернет текст (не число!) 100%. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.

Синтаксис

ТЕКСТ(значение; формат)

• Значение. Численное значение или ссылка на ячейку, содержащую численное значение.
• Формат. Текстовая строка определенного вида (например, «Д.М.ГГГГ» для вывода числа в формате даты или «# ##0,00» для вывода в числовом формате с разделителями разрядов и двумя знаками после запятой. Правила форматирования см. ниже.

Форматов для отображения чисел в MS EXCEL много (например, см. здесь), также имеются форматы для отображения дат (например, см. здесь). Также приведено много форматов в статье к функции ТЕКСТ() на сайте Microsoft .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Текстовые функции в формулах Excel: ПРОПИСН, СТРОЧН и ПРОПНАЧ

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

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

Сначала рассмотрим на примере 3 текстовых функции Excel:

1. ПРОПИСН – данная текстовая функция изменяет все буквы в слове на прописные, большие.
2. СТРОЧН – эта функция преобразует все символы текста в строчные, маленькие буквы.
3. ПРОПНАЧ – функция изменяет только первую букву в каждом слове на заглавную, большую.

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

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

Чтобы решить эту популярную задачу нужно в формуле использовать дополнительные текстовые функции Excel: ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР.

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

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

В левой части формулы используется дополнительная функция ЛЕВСИМВ:

Задача этой части формулы изменить первую букву на большую в исходной текстовой строке ячейки A1. Благодаря функции ЛЕВСИМВ можно получать определенное количество символов начиная с левой стороны текста. Функция требует заполнить 2 аргумента:

1. Текст – ссылка на ячейку с исходным текстом.
2. Количесвто_знаков – число возвращаемых символов с левой стороны (с начала) исходного текста.

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

Правая часть формулы после оператора & очень похожа по принципу действия на левую часть, только она решает другую задачу. Ее задача – преобразовать все символы текста в маленькие буквы. Но сделать это нужно так чтобы не изменять первую большую букву, за которую отвечает левая часть формулы. В место функции ЛЕВСИМВ в правой части формулы применяется функция ПРАВСИМВ:

Текстовая функция ПРАВСИМВ работает обратно пропорционально функции ЛЕВСИМВ. Так же требует запыления двух аргументов: исходный текст и количество знаков. Но возвращает она определенное число букв, полученных с правой стороны исходного текста. Однако в данном случаи мы в качестве второго аргумента не можем указать фиксированное значение. Ведь нам заранее неизвестно количество символов в исходном тексте. Кроме того, длина разных исходных текстовых строк может отличаться. Поэтому нам необходимо предварительно подсчитать длину строки текста и от полученного числового значения отнять -1, чтобы не изменять первую большую букву в строке. Ведь первая буква обрабатывается левой частью формулы и уже преобразована под требования пользователя. Поэтом на нее недолжна влиять ни одна функция из правой части формулы.

Для автоматического подсчета длины исходного текста используется текстовая функция Excel – ДЛСТР (расшифроваться как длина строки). Данная функция требует для заполнения всего лишь одного аргумента – ссылку на исходный текст. В результате вычисления она возвращает числовое значение, попетому после функции =ДЛСТР(A1) отнимаем -1. Что дает нам возможность не затрагивать первую большую букву правой частью формулы. В результате функция ПРАВСИМВ возвращает текстовую строку без одного первого символа для функции СТРОЧН, которая заменяет все символы текста в маленькие строчные буквы.

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

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

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

Все текстовые функции можно найти на вкладке Формулы → Библиотека функций → Текстовые

Функция ЛЕВСИМВ() — возвращает первые (левые) символы строки исходя из заданного количества знаков

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

Функция ПРАВСИМВ() — аналогична функции ЛЕВСИМВ(), только знаки возвращаются с конца строки (справа)

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

Функция ЗАМЕНИТЬ() — замещает часть знаков текстовой строки начиная с указанного по счёту символа, другой строкой текста

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

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

В данном примере в строке А2 необходимо заменить 6 символов, начиная с 8го (Старый на НОВЫЙ):

заменяет 6 символов, с 8го

(Старый на НОВЫЙ):

Функция ПОДСТАВИТЬ() — заменяет в строке определённый текст или символ

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

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

В данном примере в строке А2 слово вместо «Старый» подставляем «НОВЫЙ»

подставляет 6 символов, начиная с 8го

(вместо Старый — НОВЫЙ):

Функция СЦЕПИТЬ() — позволяет соединить в одной ячейке две и более части текста, чисел, символов а также ссылок на ячейки.

=СЦЕПИТЬ(текст1; текст2; …; текстN)

текст1 — обязательный аргумент. Первый текстовый элемент, подлежащий соеденению.
текст2 … — необязательные аргументы. Дополнительные текстовые элементы (до 255 штук)

Функция самостоятельно не добавляет пробелы между строками, поэтому добавлять их приходится самостоятельно, или запятые. Ещё удобно склеить текст в EXCEL с помощью знака «&» — детальнее можно ознакомится в этой статье: как объединить ячейки в EXCEL

Как выполнить обратную операцию — разъединить текст в разные ячейки можно прочитать тут: разбить текст по столбцам в EXCEL

Функция СЖПРОБЕЛЫ() — позволяет удалить все лишние пробелы, пробелы по краям, и двойные пробелы в середине текста.

в некоторых случаях можно использовать такой лайфхак : Как убрать лишние пробелы в Excel (Найти и Заменить)