Работа с текстовыми формулами в excel

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачать пример функции для работы с текстом в Excel

Читайте так же: как перевести число и сумму прописью.

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

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

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

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

СЦЕПИТЬ

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

СТРОЧН

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

ПРОПИСН

Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

ПРОПНАЧ

Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.

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

ДЛСТР

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

ПСТР

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

СОВПАД

Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

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

СЖПРОБЕЛЫ

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

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

ПОВТОР

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

НАЙТИ

Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.

Данная функция чувствительна к регистру…

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

ПОИСК

Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

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

Приведенная ниже формула заменяет все вхождения слова «Excel» на «Word»:

Заменяет только первое вхождение слова «Excel»:

Удаляет все пробелы из текстовой строки:

ЗАМЕНИТЬ

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение «2013». Применительно к нашему примеру, формула заменит «2010» на «2013».

Заменяет первые пять символов текстовой строки, т.е. слово «Excel», на «Word».

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

Текстовые функции Excel в примерах

​Смотрите также​60166​60078​-​ (нуль);​ качестве примера использования​ЛЕВСИМВ(B5;НАЙТИ(«3»;B5)+1) – функция, выделяющая​Функция ПОИСК определяет положение​ первого пробела (найденное​ правой кнопкой мыши​ выполняет функцию возврата​ начинается с символа,​ Каждый из них​ кнопкой мыши, выберите​​ «Word».​​ Строка задается как​

СЦЕПИТЬ

​возвращает заданное количество​Excel предлагает большое количество​Преобразования текстовых строк​Манипулирования целыми строками​Работы с двухбайтовыми символами​​при аргументах: («текст»;15/7) вернет​​ функции ЛЕВСИМВ. Для​ часть текстовой строки​ знака в текстовой​

СТРОЧН

​ ПОИСКом).​ по ячейке со​ числа, которое представляет​ который указан по​ определяется следующим образом:​ команду​​Вот и все! Мы​​ первый аргумент функции,​ символов, начиная с​ функций, с помощью​

ПРОПИСН

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

ПРОПНАЧ

​ счету, на новый​​Под текстом подразумевается строка​​Формат ячеек​ познакомились с 15-ю​ а количество повторов​ указанной позиции. Пробел​

​ которых можно обрабатывать​ пробелы​ раз​ текстовой строке​Примечание 2: в Excel​

ДЛСТР

​ встроенную функцию ОКРГУГЛ​ в которой соответствуют​ ПСТР возвращает текстовые​ отчество и фамилию.​ ячеек». В открывшемся​​ символа находящегося в​​ текст. Выглядит она​

ЛЕВСИМВ и ПРАВСИМВ

​ или ссылка на​​и в диалоговом​​ текстовыми функциями Microsoft​​ как второй.​​ считается за символ.​ текст. Область применения​СИМВОЛ​ПОДСТАВИТЬ​ДЛСТР​

ПСТР

​ также существует схожая​​ и подобные ей.​​ порядковому номеру символа​ значения (см. пример​ Для имени используем​ окне выбираем «все​

СОВПАД

​ подстроке необходимого текста.​​ следующим образом: старый​​ ячейку, содержащая необходимую​ окне​ Excel и посмотрели​Текстовая функция​Функция​ текстовых функций не​CHAR​SUBSTITUTE​LEN​ по принципу работы​Функция ЛЕВСИМВ используется наряду​

​ «3» (функция НАЙТИ​ выше). Либо можно​ такую же формулу:​ форматы». Копируем нужный​ В том случае,​ текст; начальная позиция;​ информацию.​

СЖПРОБЕЛЫ

​Формат ячеек​ их действие на​НАЙТИ​

​СОВПАД​ ограничивается исключительно текстом,​60159​60168​60080​ функция ЛЕВБ, возвращающая​ с прочими функциями​ выполняет поиск порядкового​ заменить найденный текст​Формула для извлечения фамилии​ в строке «Тип».​ если данные не​ количество знаков; новый​Начальная позиция​на вкладке​​ простых примерах. Надеюсь,​​находит вхождение одной​​позволяет сравнить две​​ они также могут​Преобразования типов данных​

​Поиска и замены​​Манипулирования целыми строками​​ определенное количество символов​ для работы с​ номера этого символа​ посредством функции ЗАМЕНИТЬ.​ несколько иная: Это​

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

ПОВТОР

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

НАЙТИ

​ Excel. Если они​​ ячейками, содержащими числа.​​ коду​ старый текст новым​ текстовой строке​ на основе определенного​ Excel (ПРАВСИМВ, СИМВОЛ,​ значение). Единица (+1)​

​ работы с текстом​ Вложенные функции ПОИСК​

​Приведем еще пример, где​ ошибки «#ЗНАЧ!».​ из аргументов данной​ выполняться возвращение строки.​ форматирования (см. здесь). ​ раз кстати, и​ первого символа искомой​​ в точности совпадают,​​ В рамках данного​СОВПАД​ПОИСК​ДОЛЛСША**​

ПОИСК

​ количества байтов. Если​​ СЦЕПИТЬ, ДЛСТР и​​ добавлена для выделения​ в Excel​​ ищут второй и​​ может быть полезна​Вот ее форма в​ текстовой функции в​​Количество знаков​​Одной из самых полезных​ Вы получили от​

ПОДСТАВИТЬ

​ фразы относительно начала​ то возвращается значение​ урока мы на​EXACT​​SEARCH​​USDOLLAR​ по умолчанию используется​ прочими) и имеет​ всего кода ошибки.​

​Читайте так же: как​ третий пробелы в​ данная функция. Добавим​

​ документе. Но без​ Excel:​

​представляет собой целое​ свойств функции ТЕКСТ()​

ЗАМЕНИТЬ

​ него хотя бы​ текста.​ ИСТИНА, в противном​ примерах рассмотрим 15​60165​​60130​​-​ язык с поддержкой​ следующую синтаксическую запись:​2 – количество символов,​

​ перевести число и​ строке. ПОИСК(» «;A3;1)​ нули в начале​ конкретных аргументов: =НАЙТИ(искомый​Ссылка на конкретную ячейку​ натуральное число, указывающее​ является возможность отображения​ малость полезной информации.​

​Данная функция чувствительна к​ случае – ЛОЖЬ.​ наиболее распространенных функций​Манипулирования целыми строками​

​Поиска и замены​Преобразования типов данных​ двухбайтовой кодировки, для​=ЛЕВСИМВ(текст;[число_знаков])​ которые вернет функция​ сумму прописью.​ находит первый пробел​ числа. Если ввести​ текст; текст для​ или строка, содержащая​ объем символов, который​ в текстовой строке​ Всего доброго и​ регистру…​ Данная текстовая функция​

​ Excel из категории​

office-guru.ru

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

​Проверяет идентичность двух текстов​Ищет вхождение одного текста​Преобразует число в текст,​ получения первого символа​Описание аргументов:​ ПРАВСИМВ, работающая аналогично​Синтаксис функции ПОИСК:​ слева (перед отчеством).​ вручную, Excel их​ поиска; нач. позиция).​ занесенную ранее информацию,​ нужно вернуть, начиная​ чисел и дат​ успехов в изучении​… и может начинать​ учитывает регистр, но​Текстовые​СТРОЧН​ в другой (без​

​ используя денежный формат​

​ строки требуется в​

• ​текст – ссылка на​​ рассматриваемой ЛЕВСИМВ (отличие​«искомый текст» — что​ К найденному результату​
• ​ удалит. Поэтому введем​​Теперь рассмотрим по отдельности​ называется​ с начальной позиции.​ в нужном формате​ Excel!​ просмотр текста с​ игнорирует различие в​.​LOWER​ учета регистра)​ЗАМЕНИТЬ​

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

​Как пример можно привести​ (см. подробнее об​Автор: Антон Андронов​ указанной позиции. На​ форматировании.​Для объединения содержимого ячеек​60160​ПОИСКБ***​REPLACE​ число 2, а​ или текстовая строка,​ справа, а не​«просматриваемый текст» — где​ Получаем ту позицию,​​Если нужно вернуть прежние​​Необходимая строка представляет собой​Начальной позицией​​ следующую ситуацию. В​​ отображении чисел, дат​​Функция ТЕКСТ(), английская версия​​ рисунке ниже формула​Если регистр для Вас​

​ в Excel, наряду​Преобразования текстовых строк​SEARCHB​60167​ для получения последующих​ в которой требуется​ слева строки).​ искать;​ с которой будем​ числовые значения (без​Искомый текст.​указывается номер символа,​ ячейке А1 находится​

​ и времени). В​ TEXT(), преобразует число​ начинает просмотр с​

excel2.ru

Примеры текстовых функций в Excel

​ не играет большой​ с оператором конкатенации,​Делает все буквы в​-​Поиска и замены​ второго, третьего и​ выделить требуемое количество​В результате получим:​«начальная позиция» — с​ искать второй пробел.​ нулей), то используем​Данные, среди которых происходит​ с которого должна​

​ некоторый текст. Нужно​ файле примера приведен​ в текст и​ четвертого символа, т.е.​ роли (так бывает​

«СЦЕПИТЬ»

​ можно использовать текстовую​ тексте строчными​Работы с двухбайтовыми символами​Заменяет знаки в тексте​ прочих символов необходимо​ символов слева. Аргумент​Выделим часть текста ошибки:​ какой позиции начинать​Часть формулы – ПОИСК(«​ оператор «—»:​ поиск первого аргумента,​ производиться замена (нумерация​ вернуть два последних​ наглядный пример: с​

• ​ позволяет задать формат​
• ​ c буквы «​ в большинстве случаев),​

​ функцию​СЦЕПИТЬ​Ищет вхождение одного текста​ЗАМЕНИТЬБ***​ указывать соответствующие числа,​

«ПСТР»

​ обязателен для заполнения.​В данном случае удобнее​ искать (по умолчанию​ «;A3;ПОИСК(» «;A3;1)+1) –​Обратите внимание, что значения​ являются​ идет слева на​ слова, общая длина​ форматированием и без​ отображения с помощью​

• ​r​ то можно применить​СЦЕПИТЬ​CONCATENATE​
• ​ в другой (без​​REPLACEB​ кратные двум. Для​[число_знаков] – необязательный для​
• ​ использовать ПРАВСИМВ, которая​​ – 1).​ находит второй пробел.​ теперь отображаются в​Текстом для поиска.​ право).​

​ символов у них​ форматирования.​ специальных строк форматирования,​«. Но даже в​ формулу, просто проверяющую​. Она последовательно объединяет​60384​ учета регистра)​-​ языков с однобайтовой​ заполнения аргумент; числовое​ является, по сути,​

«ПОДСТАВИТЬ»

​Если нужно учитывать регистр,​ Это будет конечная​ числовом формате.​Начальная позиция​Новый текст​ 12. Символ, который​В файле примера приведено​ например, формула =ТЕКСТ(100;»0,00​ этом случае положение​ равенство двух ячеек.​ значения указанных ячеек​Манипулирования целыми строками​ПРАВБ***​Работы с двухбайтовыми символами​

​ кодировкой функции ЛЕВСИМВ​ значение, характеризующее количество​ зеркальным отражением ЛЕВСИМВ.​ используется функция НАЙТИ.​

​ позиция отчества.​

• ​​не является обязательным​представляет собой строку,​ необходимо вернуть в​ также множество других​
• ​ р.») вернет текстовую​​ символа считается относительно​Удаляет из текста все​ в одной строке.​Объединяет несколько текстовых элементов​
• ​RIGHTB​​Заменяет знаки в тексте​ и ЛЕВБ возвращают​ символов, которые требуется​
• ​ Для определения второго​​Функция ЛЕВСИМВ в Excel​Далее из общей длины​Отдельные текстовые функции и​ элементом. По умолчанию​ которой заменяется занесенная​ конкретной фразе, числится​ форматов.​ строку 100,00 р.,​ начала просматриваемого текста.​

«ЗАМЕНИТЬ»

​ лишние пробелы, кроме​Если в Excel необходимо​ в один​-​ЗНАЧЕН​ идентичные результаты.​ выделить из текстовой​ аргумента используется запись​ используется для получения​ строки отнимается количество​ их комбинации позволяют​

​ она имеет значение,​ ранее информация, которая​ под порядковым номером​В строки программы Microsoft​

• ​ а формула =ТЕКСТ(1;»#%»)​Текстовая функция​ одиночных между словами.​ сделать все буквы​​Т​
• ​Работы с двухбайтовыми символами​​VALUE​Функции для работы​ строки.​ ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию​ части текста и​
• ​ знаков с начала​​ распределить слова из​ равное единице.​ задавалась​ 12.​​ Office Exel можно​​ вернет текст (не​​ПОИСК​

​В случаях, когда наличие​ строчными, т.е. преобразовать​Т​Возвращает указанное количество символов​60081​ с текстом предназначены​Примечания 1:​ символа «u» в​ возвращает заданное количество​ строки до второго​ одной ячейки в​

​Автор: Ксения Медкова​Начальной позицией​Выполняет замену готового текста​ заносить любую информацию,​ число!) 100%. Эта​очень похожа на​ лишнего пробела в​ их в нижний​60178​ с конца текстовой​Преобразования типов данных​ для работы с​Если необязательный аргумента [число_знаков]​ слове «unknown» (единица​ символов текстовой строки,​ пробела. Результат –​ отдельные ячейки:​

• ​Для удобства работы с​​и​ на необходимый в​
• ​ которая необходима пользователям​​ функция полезна, если​

«ДЛСТР»

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

«НАЙТИ»

​ при этом отсчет​ число символов справа,​ЛЕВСИМВ (текст; кол-во знаков)​ текстом в Excel​Количеством знаков.​ том месте, которое​ для успешного выполнения​ числа требуется отобразить​НАЙТИ​ строки сложно отследить,​ придет текстовая функция​

​Преобразует аргумент в текст​ПРАВСИМВ​ число​ Кроме того функции​ по умолчанию будет​

​ пробела).​ символов начинается слева.​

• ​ которые нужно вернуть.​​ – отображает заданное​
• ​ существуют текстовые функции.​Теперь рассмотрим пример использования​ будет указано пользователем.​​ поставленных задач. Чтобы​
• ​ в более удобном​​, основное их различие​ данная функция становится​СТРОЧН​ТЕКСТ​

​RIGHT​

fb.ru

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

​КОДСИМВ​ этой категории могут​ возвращен первый символ​В результате получим:​Пример 1. В таблице​Формула «для отчества» строится​ число знаков с​

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

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

​TEXT​60164​CODE​ работать и с​ строки. Например, функция​Пример 3. Создать функцию​ Excel содержатся имена​ по тем же​ начала ячейки;​ сразу сотен строк.​

​ Excel. В ячейке​ функцию под названием​ необходимо уметь разбираться​ требуется объединить числа​

​ что​ рисунке ниже видно,​ знаки, не являющиеся​

​60096​Извлечения символов из строк​60169​ числовыми значениями, то​ =ЛЕВСИМВ(«текст») вернет значение​ для округления дробных​ переменных вместе с​ принципам:​

​ПРАВСИМВ (текст; кол-во знаков)​ Рассмотрим некоторые из​

​ А1 содержится строка​ «Заменить». Но о​ с данными в​ с текстом или​ПОИСК​ что содержимое ячеек​ буквами.​Преобразования типов данных​Возвращает указанное количество символов​Преобразования типов данных​ есть обрабатывать числа​ «т».​

​ чисел до ближайших​ текстом, определяющим их​Для объединения значений из​ – возвращает заданное​ них на примерах.​ со словом «старый».​ ней подробности будут​ строках. В этом​

​ символами.​не чувствительна к​ А1 и B1​Текстовая функция​

​Форматирует число и преобразует​ с конца текстовой​Определяет числовой код первого​

​ как текст и​

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

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

• ​Преобразует числа в текст.​ Оно начинается с​ представлены позже. Главное​ материале будут рассмотрены​
• ​Синтаксис​ регистру.​ абсолютно одинаково, но​ПРОПИСН​
• ​ его в текст​ строки​ знака в текстовой​ текст как числа.​ качестве необязательного аргумента,​ использованием формул для​ выделить только названия​

​ одну строку используется​ конца ячейки;​ Синтаксис: значение (числовое​ 19-го символа и​ отличие данного варианта​

​ примеры текстовых функций​ТЕКСТ(значение; формат)​Заменяет определенный текст или​ это не так.​

​делает все буквы​ФИКСИРОВАННЫЙ​ПРОПИСН​ строке​Список текстовых функций:​ должно быть взято​ работы с текстом.​ переменных и записать​ оператор амперсанд (&)​ПОИСК (искомый текст; диапазон​ или ссылка на​ имеет длительность из​

​ в том, что​

​ в Excel.​Значение.​ символ на требуемое​ В ячейке А1​ прописными, т.е. преобразует​FIXED​UPPER​ЛЕВБ***​Функция​

​ из диапазона неотрицательных​Форма для округления числовых​ их в отдельный​ или функция СЦЕПИТЬ.​

​ для поиска; начальная​ ячейку с формулой,​ шести идентичных единиц.​ он автоматически вычисляет​Чтобы найти их в​  Численное значение или​ значение. В Excel​ мы намеренно поставили​ их в верхний​60062​60161​LEFTB​Function​ чисел, то есть​

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

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

​Преобразования типов данных​Преобразования текстовых строк​-​

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

​id​ должно быть равным​ вид:​Исходная таблица данных:​ разных столбцах (ячейках):​

​ позицию первого появления​ число); формат (для​

​ слово «новый».​ и выполняет замену.​ вкладку под названием​ содержащую численное значение.​ПОДСТАВИТЬ​ конце слова​ как и​Форматирует число и преобразует​Делает все буквы в​Работы с двухбайтовыми символами​Подкатегория​ нулю или больше​В ячейке B3 введена​Для выделения первого символа,​

​Ставим курсор в ячейку,​ искомого знака или​

​ отображения числа в​

​Стоит отметить, что данная​Выглядит конкретная функция следующим​ «Формулы», откройте список​Формат.​применяют, когда заранее​

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

​Excel​СТРОЧН​ его в текст​ тексте прописными​

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

​ строки при просмотре​ виде текста).​ ситуация достаточно двоякая.​ образом: текст; старый​ и выберите «Текстовые».​     Текстовая строка определенного​ известно какой текст​. В итоге функция​

​, не заменяет знаки,​ с заданным числом​ПРОПНАЧ​

​ от начала текстовой​ASC***​ случае будет возвращен​

​=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’​

• ​ название переменной, используем​ объединенные три значения.​
• ​ слева направо​Самая полезная возможность функции​
• ​ С одной стороны,​ текст; новый текст;​Данная функция дает пользователю​ вида (например, «Д.М.ГГГГ»​

​ необходимо заменить, а​СОВПАД​

exceltable.com

Примеры использования функции ЛЕВСИМВ в Excel работа с текстом

​ не являющиеся буквами.​ десятичных знаков​PROPER​ строки​ASC​ код ошибки #ЗНАЧ!.​ class=’formula’>​

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

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

​возвратила нам значение​

​Текстовая функция​Ф.ТЕКСТ​60162​ЛЕВСИМВ​

​-​Если в качестве аргумента​Логика работы:​Второй равен 1 по​ первую ячейку с​ строке учитывается положение​ числовых данных для​

​ работать с небольшим​Разберем каждый из аргументов:​ собой несколько строк.​ в формате даты​Приведенная ниже формула заменяет​ ЛОЖЬ.​ПРОПНАЧ​

​FORMULATEXT​

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

​Преобразования текстовых строк​LEFT​Работы с двухбайтовыми символами​ [число_знаков] будет переданы​Первая функция ЕСЛИ проверяет​ умолчанию, поэтому функция​ текстом и нажимаем​ каждого знака. Пробелы​ объединения с текстовыми​

​ количеством ячеек, то​

​Под текстом понимается ссылка​ Максимально допустимое для​

​ или «# ##0,00″​

• ​ все вхождения слова​Применив функцию​делает прописной первую​Возвращает текст формулы​Делает прописной первую букву​60163​Преобразует полуширинные (однобайтовые) английские​ текстовые данные, функция​ с помощью формулы​ возвращает первый символ.​ на клавиатуре &.​ показывают начало или​
• ​ данными. Без использования​ данный вариант отлично​ на определенную ячейку​ соединения количество достигает​ для вывода в​ «Excel» на «Word»:​СЖПРОБЕЛЫ​

​ букву каждого слова,​

​** — недокументированная функция​

​ в каждом слове​Извлечения символов из строк​ буквы в полноширинные​ ЛЕВСИМВ вернет код​ ЕПУСТО, заполнена ли​ Аналогично выделим названия​ Затем – знак​ конец искомого имени.​ функции Excel «не​ сработает. Но как​ или строка, которая​

​ 255. Обратите внимание​

Округление чисел текстовыми функциями в текстовой строке

​ числовом формате с​Заменяет только первое вхождение​к значению ячейки​ а все остальные​ Excel​ текста​

​Возвращает указанное количество символов​ (двухбайтовые)​ ошибки #ЗНАЧ!.​

​ ячейка, которая должна​ переменных из других​

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

​ понимает», как показывать​

1. ​ быть в ситуации,​ содержит эту самую​ на то, что​ разделителями разрядов и​ слова «Excel»:​ А1, мы удалим​
2. ​ преобразует в строчные.​*** — Функции подкатегории​ПСТР​ от начала текстовой​JIS***​Если число, переданное в​
3. ​ содержать значение, которое​ строк. В результате​ кавычки (“ “).​ имя, фамилию и​ числа, и преобразует​ когда предстоит выполнить​ информацию.​
4. ​ проставлять пробелы здесь​

​ двумя знаками после​Удаляет все пробелы из​ из него все​Каждая первая буква, которая​работы с двухбайтовыми символами​MID​ строки​-​ качестве необязательного аргумента,​ требуется округлить.​ получим:​ Снова — &.​

Функция ЛЕВСИМВ в Excel и особенности ее использования

​ отчество в разные​ их в базовый​ замену среди нескольких​Старый текст​ необходимо самостоятельно. В​ запятой. Правила форматирования​ текстовой строки:​ лишние пробелы и​

​ следует за знаком,​

​(за исключением​

• ​60079​НАЙТИ​-​ превышает количество знаков​Если число введено, запись​Как видно на рисунке​ И так последовательно​
• ​ столбцы.​ формат.​ тысяч строк? В​- исходный вариант​ теле «СЦЕПИТЬ» должен​ см. ниже.​

​Заменяет символы, расположенные в​

1. ​ получим корректный результат:​ отличным от буквы,​ASC​Извлечения символов из строк​FIND​Работы с двухбайтовыми символами​ в текстовой строке,​
2. ​ ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое​ с помощью функции​ соединяем ячейки с​В первой строке есть​Покажем на примере. Допустим,​ таком случае стоит​ данных, хранящихся в​ содержаться минимум один​Форматов для отображения чисел​ заранее известном месте​
3. ​Функцию​ также преобразуется в​,​Извлекает определенное число знаков​60172​
4. ​Преобразует однобайтовые английские буквы​ рассматриваемая функция вернет​ значение первого символа​ ЛЕВСИМВ получилось вытащить​ текстом и пробелы.​ только имя и​
5. ​ нужно объединить текст​ обратиться к двум​ ячейке, которые должны​ аргумент. Рассмотрим пример​ в MS EXCEL​ строки, на требуемое​СЖПРОБЕЛЫ​

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

​ много (например, см.​ значение. В Excel​полезно применять к​В Excel Вы можете​и​ начиная с указанной​Ищет вхождение одного текста​ (японское письмо) в​Excel выполняет автоматическое преобразование​ сравнивает полученное значение​ из каждой ячейки​ объединенные значения:​ Формула для извлечения​ числовые значения:​ Excel, которые будут​Новый текст​ действия:​ здесь), также имеются​ текстовую функцию​ данным, которые импортируются​ подсчитать количество знаков,​PHONETIC​ позиции​ в другой (с​

exceltable.com

Функции текстовые.

​ двухбайтовые​ типов данных там,​ с числом 5.​ по одному (первому)​Использование функции СЦЕПИТЬ:​ имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)).​Использование амперсанда без функции​ рассмотрены чуть позже:​является строкой, необходимой​=СЦЕПИТЬ(«Аргумент1″;»»;»Аргумент2″);​ форматы для отображения​
​ЗАМЕНИТЬ​

​ позиции (текст, начальная​ выполнять арифметические действия),​
​ текстовой строки, т.е.​​повторяет текстовую строку​​Текстовая функция​​может иметь другое​​СЖПРОБЕЛЫ​​ПОВТОР​​ДЛИНБ***​​ поскольку логическое ЛОЖЬ​​ Он приведен в​Описание:​ может пригодиться?​ количество знаков до​ строку формата? Щелкаем​При работе с текстом​ количестве символов. Процесс​ позиция, количество знаков).​ щелкните ячейку правой​ слово «Excel», на​ указанное количество раз.​ПСТР​ название.​ ​TRIM​​REPT​LENB​

excelworld.ru

​ эквивалентно числовому 0​

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

Для примера возьмем строку «ExcelGuide.ru – про Excel и не только» и ее будем использовать в наших функциях ниже.

ЛЕВСИМВ

Функция ЛЕВСИМВ возвращает указанное количество знаков с начала строки. В качестве аргументов на первом месте указываем ту строку, из которой хотим извлечь текст, а вторым аргументом количество символов, которое хотим получить.
Давайте из нашей строки получим текст «ExcelGuide.ru»:

=ЛЕВСИМВ(B1;13)

ПРАВСИМВ

Функция ПРАВСИМВ аналогична ЛЕВСИМВ, только возвращает указанное количество символов не с начала, а с конца строки. Первым аргументом указываем строку, откуда будем получать часть текста, а вторым аргументом – количество символов.
Из нашей строки извлечем текст «про Excel и не только»:

=ПРАВСИМВ(B1;21)

ПСТР

Функция ПСТР позволяет получить указанное количество символов начиная с определенной позиции. У этой функции 3 аргумента: Текст, из которого нам нужно получить часть; стартовая позиция, с которой нужно извлечь символы; количество символов, которое хотим получить.
В нашей строке есть слово Excel, давайте его получим:

=ПСТР(B1;21;5)

ДЛСТР

Функция ДЛСТР возвращает количество символов в строке.

=ДЛСТР(B1)

ПОИСК

Функция ПОИСК предназначена для нахождения первого вхождения указанного текста в исходную строку. Аргументы функции: сначала указываем тот текст, который хотим найти; далее строку, в которой ищем текст.
Давайте в нашем примере найдем текст «про Excel»:

=ПОИСК(«про Excel»;B1)

СЦЕПИТЬ

Функция СЦЕПИТЬ позволяет последовательно объединить несколько текстовых элементов в одну строку.
В качестве аргументов необходимо перечислить те текстовые элементы, которые вы хотите соединить.
 В качестве примера объединим наш пример и строку «. Пожалуй лучший сайт про Excel )))»:

=СЦЕПИТЬ(B1;». Пожалуй лучший сайт про Excel )))»)

СОВПАД

Функция СОВПАД проверяет идентичность двух строк и возвращает Истина, если строки совпадают и ЛОЖЬ, если строки не совпадают.
Сравним нашу строку с текстом «ExcelGuide.ru»:

=СОВПАД(B1;»ExcelGuide.ru»)

СЖПРОБЕЛЫ

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

=СЖПРОБЕЛЫ(B1)

ЗНАЧЕН

Функция ЗНАЧЕН преобразует текст в число. Часто случается при экспорте из разных информационных систем мы получаем числовые значения в текстовом формате, в таких случаях нам и пригодится этот функционал.
В качестве примера преобразуем текст «1000» в число 1 000:

=ЗНАЧЕН(«1000»)

ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ заменяет новым текстом старый текст в исходной текстовой строке. Аргументов у функции три: сначала указываем ту строку, в которой будем менять текст; далее указываем старый текст; а затем тот, которым мы хотим заменить.
В качестве примера в нашей строке заменим «про» на «о»:

=ПОДСТАВИТЬ(B1;»про»;»о»)

ПРОПИСН

Функция ПРОПИСН преобразует все буквы в прописные. У функции только один аргумент – та строка, которую надо преобразовать.

=ПРОПИСН(B1)

СТРОЧН

Функция СТРОЧН преобразует все буквы в строчные. У функции один аргумент – тот текст, который мы хотим модифицировать.

=СТРОЧН(B1)

Кстати, если вы хотите более подробно изучить Excel, научиться строить быстро сложные отчеты и графики, то рекомендую вам курс «Excel + Google Таблицы с нуля до PRO» от Skillbox.

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

в некоторых случаях можно использовать такой лайфхак : Как убрать лишние пробелы в 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.

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

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

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

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

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

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

Задача 1. Объединение текстовых строк

Есть список сотрудников. Фамилии, имена и отчества разнесены в отдельные столбцы. Необходимо объединить данные в один столбец.

Решение. Задача достаточно простая и для ее реализации воспользуемся функцией СЦЕПИТЬ.

В ячейку D1 запишем формулу =СЦЕПИТЬ(A1;» «;B1;» «;C1). Можно воспользоваться мастером функций.

Далее скопируем ее на весь необходимый диапазон столбца D.

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

Посмотрите на рисунок ниже. Результат преобразования в столбце D.

Окно мастера функции СЦЕПИТЬ

Задача 2. Разделение текстовых строк

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

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

Для отделения фамилии сотрудника и запишем в ячейку B1 формулу

=ЛЕВСИМВ(A1;НАЙТИ(» «;A1))

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

Строка формул при разделении ФИО

Для записи имени в ячейку C1 запишем следующую формулу

=ПСТР(A1;НАЙТИ(» «;A1)+1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1)

Если посмотреть на синтаксис записи данной функции, то получаем:

1. Выражение НАЙТИ(» «;A1)+1 отвечает поиск позиции первого пробела в текстовой строке. А чтобы получить позицию первой буквы имени, прибавляется единица.
2. Для определения количества символов в имени используется конструкция посложнее ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1. Количество символов определяется как разность позиций пробелов, отделяющих имя. Чтобы исключить из числа найденных символов сами пробелы, в начальной позиции прибавляется единица, а потом из полученного результата вычитается единица.

Отчество получается в ячейке D1 по более сложной формуле

=ПСТР(A1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1;ДЛСТР(A1)-ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1)

Здесь количество знаков в отчестве определяется как разность общего количества символов (ДЛСТР) и позицией второго пробела.

В рассмотренных примерах функции ПОИСК и НАЙТИ выполняют одинаковые операции, так как разница в регистрах символов не учитывается. Возможно обойтись только одной из них.

Задача 3. Укорачивание текстовых строк

В список сотрудников внести изменения. Записать в одном столбце Фамилии и инициалы.

Решение. В зависимости от исходного состояния списка возможны два варианта.

1 вариант. Исходные данные содержатся в одном столбце. ФИО разделены одинарным пробелом.

Записываем следующую формулу

=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1));ПСТР(A1;НАЙТИ(» «;A1);2);».»;ПСТР(A1;НАЙТИ(» «;A1; НАЙТИ(» «;A1)+1);2);».»)

Преобразуем имя и отчество в инициалы (исходные данные в одном столбце)

2 вариант. Исходные данные содержатся в разных столбцах.

Формула для преобразования

=СЦЕПИТЬ(A1;» «;ЛЕВСИМВ(B1);».»;ЛЕВСИМВ(C1);».»)

Преобразуем имя и отчество в инициалы (исходные данные в разных столбцах)

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