Функции работы с текстом в excel

Функции работы с текстом в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Программа которая обрезает видео

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как сделать закрепление областей в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Akelpad что это за программа

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

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

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

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

  • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
  • количество_знаков – целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1
Текст Значение Формула Описание
Бюджет Старый Бюджет =ЛЕВСИМВ(A2;6) возвращает первые 6 символа

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

  • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
  • количество_знаков – целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1
Текст Значение Формула Описание
Бюджет Старый Старый =ПРАВСИМВ(A2;6) возвращает последние 6 символов

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

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

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

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

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

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

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

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

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

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

Текст Значение Формула Описание
Бюджет Старый Бюджет НОВЫЙ =ЗАМЕНИТЬ(A2;8;6;»НОВЫЙ»)

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

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

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

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

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

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

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

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

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

Текст Значение Формула Описание
Бюджет Старый Бюджет НОВЫЙ = ПОДСТАВИТЬ(A2;»Старый»;»НОВЫЙ»;1)
Ссылка на основную публикацию
Файлы mdi чем открыть
Если вы не смогли открыть файл двойным нажатием на него, то вам следует скачать и установить одну из программ представленных...
Украли сумку с документами что делать
В связи с угрозой распространения на территории Российской Федерации коронавирусной инфекции приостановлен личный прием граждан в судах. Смотреть как изолируются...
Ультра исо вам необходимо иметь права администратора
Очень многие пользователи, когда им нужно сделать загрузочную флешку Windows или с дистрибутивом другой операционной системы, прибегают к использованию программы...
Файлы mdx чем открыть
MDX - это формат образов дисков, который был создан разработчиками программы DAEMON Tools. Это формат был создан в результате усовершенствования...
Adblock detector