Что такое текстовые функции в excel. Вставка текста в ячейку с формулой в Microsoft Excel. Формулы с текстовыми функциями Excel

Excel предлагает большое количество функций, с помощью которых можно обрабатывать текст. Область применения текстовых функций не ограничивается исключительно текстом, они также могут быть использованы с ячейками, содержащими числа. В рамках данного урока мы на примерах рассмотрим 15 наиболее распространенных функций 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 (краткая справка). Дополнительную информацию о функциях можно найти в окне диалога мастера функций, а также в справочной системе Excel .

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

Функция ТЕКСТ

Функция ТЕКСТ (TEXT) преобразует число в текстовую строку с заданным форматом. Синтаксис:

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

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

=ТЕКСТ(101/4;"0,00")

Функция РУБЛЬ

Функция РУБЛЬ (DOLLAR) преобразует число в строку. Однако РУБЛЬ возвращает строку в денежном формате с заданным числом десятичных знаков. Синтаксис:

=РУБЛЬ(число;число_знаков)

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

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

Функция ДЛСТР (LEN) возвращает количество символов в текстовой строке и имеет следующий синтаксис:

=ДЛСТР(текст)

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

=ДЛСТР("голова")

Функция ДЛСТР возвращает длину отображаемого текста или значения, а не хранимого значения ячейки. Кроме того, она игнорирует незначащие нули.

Функция СИМВОЛ и КОДСИМВ

Любой компьютер для представления символов использует числовые коды. Наиболее распространенной системой кодировки символов является ASCII. В этой системе цифры, буквы и другие символы представлены числами от 0 до 127 (255). Функции СИМВОЛ (CHAR) и КОДСИМВ (CODE) как раз и имеют дело с кодами ASCII. Функция СИМВОЛ возвращает символ, который соответствует заданному числовому коду ASCII, а функция КОДСИМВ возвращает код ASCII для первого символа ее аргумента. Синтаксис функций:

=СИМВОЛ(число)
=КОДСИМВ(текст)

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

Функции СЖПРОБЕЛЫ и ПЕЧСИМВ

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

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

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

=ПЕЧСИМВ(текст)

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

Функция СОВПАД (EXACT) сравнивает две строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется. Синтаксис:

=СОВПАД(текст1;текст2)

Если аргументы текст1 и текст2 идентичны с учетом регистра букв, функция возвращает значение ИСТИНА, в противном случае - ЛОЖЬ. Аргументы текст1 и текст2 должны быть строками символов, заключенными в двойные кавычки, или ссылками на ячейки, в которых содержится текст.

Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ

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

=ПРОПИСН(текст)
=СТРОЧН(текст)
=ПРОПНАЧ(текст)

При работе с уже существующими данными довольно часто возникает ситуация, когда нужно модифицировать сами исходные значения, к которым применяются текстовые функции. Можно ввести функцию в те же самые ячейки, где находятся эти значения, поскольку введенные формулы заменят их. Но можно создать временные формулы с текстовой функцией в свободных ячейках в той же самой строке и скопируйте результат в буфер обмена. Чтобы заменить первоначальные значения модифицированными, выделите исходные ячейки с текстом, в меню "Правка" выберите команду "Специальная вставка", установите переключатель "Значения" и нажмите кнопку ОК. После этого можно удалить временные формулы.

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

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

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

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

Примеры текстовых функций в 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​Количеством знаков.​ том месте, которое​ для успешного выполнения​ числа требуется отобразить​НАЙТИ​ строки сложно отследить,​ придет текстовая функция​

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

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

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

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

Примеры функции ТЕКСТ в 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)),"Число не введено")"​

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

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

Примеры использования функции ЛЕВСИМВ в 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​ позиции​ в другой (с​

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

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

​ в рабочие листы​	​ содержащихся в текстовой​	​) являются полными аналогами​	​ПСТРБ***​	​ учетом регистра)​
​PHONETIC***​	​ где это возможно.​	​Если полученное значение больше​	​ символу.​	​С помощью кавычек в​ Для определения второго​ ТЕКСТ дает «неадекватный»​
​Начальную позицию​	​ для выполнения смены​	​В итоге получится следующая​	​ дат (например, см.​	​применяют, когда известно​ Excel из внешних​ строке, для этого​ соответствующих функций для​
​MIDB​	​НАЙТИБ***​	​-​	​ Например, рассмотрим некоторые​	​ 5, будет возвращен​​ формуле можно добавить​
​ аргумента функции ЛЕВСИМВ​	​ результат:​	​будем заменять на​	​ старого варианта данных.​	​ строка: "Аргумент1 Аргумент2".​ здесь). Также приведено​ где располагается текст,​
​ источников. Такие данные​	​ воспользуйтесь функцией​	​ работы с однобайтовыми​	​-​	​FINDB​-​
​ возможные варианты записи​	​ результат выполнения функции​	​Пример 2. В таблицу​	​ в конечное выражение​	​ – количества знаков​Excel вернул порядковый номер​
​ "НАЙТИ".​	​Номер вхождения​	​Альтернативой данной функции для​	​ много форматов.​	​ при этом сам​ очень часто содержат​
​ДЛСТР​	​ символами. Функции этой​	​Работы с двухбайтовыми символами​	​-​	​Работы с двухбайтовыми символами​
​ функции ЛЕВСИМВ и​	​ ЛЕВСИМВ(B2+1;НАЙТИ(",";B2)-1), то есть​	​ Excel подтягиваются данные​	​ любой знак или​	​ – используется функция​
​ для даты и​	​Количество знаков​	​представляет собой необязательный​	​ работы с текстом​	​Функция ТЕКСТ() преобразует число​ он не важен.​
​ лишние пробелы и​	​. Пробелы учитываются.​	​ подкатегории отсутствуют в​	​Извлекает определенное число знаков​	​Работы с двухбайтовыми символами​Преобразует графическое японское письмо​ результаты ее выполнения:​
​ целой части числа​	​ из другого программного​	​ текст.​	​ ПОИСК. Она находит​	​ общий формат вместо​изменится на "ДЛСТР".​ аргумент в данной​
​ является символ &,​	​ в форматированный текст​	​Формула в примере ниже​	​ различные непечатаемые символы.​	​Текстовые функции​ мастере функций и​ из строки текста,​
​Ищет вхождение одного текста​	​ (катакана) в фонетические​	​ЛЕВСИМВ(«текст»;200%) вернет значение «те»​	​ +1 по правилам​	​ продукта. Одно из​Функция ПОИСК возвращает начальную​ пробел в ячейке​
​ денежного. Чтобы избежать​	​Данная текстовая функция в​	​ текстовой функции в​	​ который также отвечает​	​ и результат больше​ заменяет 4 символа,​ Чтобы удалить все​
​ЛЕВСИМВ​	​ вводятся только вручную.​	​ начиная с указанной​	​ в другой (с​	​ знаки (фуригана)​ (преобразование процентов в​
​ округления.​	​ полей данных получило​	​ позицию искомого текста​	​ А2, начиная слева.​	​ подобного результата, применяется​ Excel помогает пользователю​
​ Excel. При его​	​ за объединение строк.​	​ не может быть​	​ расположенные, начиная с​	​ непечатаемые символы из​и​
​ Кроме того, для​	​ позиции​	​ учетом регистра)​	​БАТТЕКСТ​	​ числовое значение);​Если​ ошибку. Для обработки​
​ (без учета регистра).​	​Формула для извлечения фамилии:​	​ функция ТЕКСТ. Она​	​ определять длину строки.​	​ указании будут заменены​Она отвечает за возвращение​ использован в вычислениях​
​ седьмой позиции, на​	​ текста, необходимо воспользоваться​	​ПРАВСИМВ​	​ их работы необходимы​	​РУБЛЬ​ПЕЧСИМВ​-​
​при аргументах: («текст»;ИСТИНА) вернет​	​Примечание: данный алгоритм нельзя​	​ ошибки необходимо выделить​	​ Например:​	​С помощью функции ПОИСК​ форматирует значения по​ Результатом ее работы​
​ только совпадающие вхождения.​	​ из требуемой строки​	​ в качестве числа.​	​ значение «2013». Применительно​	​ функцией​возвращают заданное количество​
​ соответствующие языковые настройки​	​DOLLAR​	​CLEAN​	​-​	​ «т» (логическое ИСТИНА​ назвать гибким и​ ее код и​
​Функция ПОИСК вернула позицию​	​ Excel определяет количество​	​ заданию пользователя.​	​ будет возврат целого​	​ В случае его​ куска текста в​ Чтобы отформатировать число,​ к нашему примеру,​
​ПЕЧСИМВ​	​ символов, начиная с​	​ системы. В зависимости​	​60061​
​ описание из строки.​	​ 10, т.к. слово​	​ знаков для функции​	​Формула «для даты» теперь​	​ числа, демонстрирующего объем​ отсутствия смена произойдет​
​ указанном количестве символов.​	​ но при этом​	​ формула заменит «2010»​	​.​	​ начала или с​ от языковых параметров,​
​Преобразования типов данных​	​Преобразования текстовых строк​	​Преобразует чисто в текст​	​ 1);​	​ недостатки (например, не​Исходные данные:​
​ «Захар» начинается с​	​ ПРАВСИМВ. Функция ДЛСТР​	​ выглядит так:​	​ символов в тексте.​	​ везде.​
​ Строка начинается с​	​ оставить его числом​	​ на «2013».​	​Функция​	​ конца строки. Пробел​ функция​
​Преобразует число в текст,​	​Удаляет все непечатаемые знаки​	​ (бат) на тайском​	​при аргументах: («текст»;ЛОЖЬ) вернет​	​ учтен вариант ввода​Выделим код ошибки с​
​ десятого символа в​	​ «считает» общую длину​	​Второй аргумент функции –​	​ Выглядит она так:​	​Выполняет функцию замены части​
​ отмеченного символа. Ключевые​	​ (с которым можно​	​Заменяет первые пять символов​	​ПОВТОР​	​ считается за символ.​JIS​
​ используя денежный формат​	​ из текста​	​ языке​	​ пустое значение «»,​	​ сразу целого числа).​ помощью следующей формулы:​ строке. Где это​ текста. Затем отнимается​
​ формат. Где брать​	​ =ДЛСТР(текст).​			​ строки в конкретном​

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

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

Чтобы найти их в программе, проследуйте во вкладку под названием "Формулы", откройте список и выберите "Текстовые".

"СЦЕПИТЬ"

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

• =СЦЕПИТЬ("Аргумент1";"";"Аргумент2");
• В итоге получится следующая строка: "Аргумент1 Аргумент2".

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

"ПСТР"

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

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

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

"ПОДСТАВИТЬ"

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

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

Разберем каждый из аргументов:

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

"ЗАМЕНИТЬ"

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

Рассмотрим по отдельности каждый из аргументов данной текстовой функции в Excel:

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

Теперь рассмотрим пример использования текстовой функции в Excel. В ячейке А1 содержится строка со словом "старый". Оно начинается с 19-го символа и имеет длительность из шести идентичных единиц. Замена происходит на слово "новый".

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

• Начальную позицию будем заменять на "НАЙТИ".
• Количество знаков изменится на "ДЛСТР".

"ДЛСТР"

Данная текстовая функция в Excel помогает пользователю определять длину строки. Результатом ее работы будет возврат целого числа, демонстрирующего объем символов в тексте. Выглядит она так: =ДЛСТР(текст).

"НАЙТИ"

При работе с текстом выполняет функцию возврата числа, которое представляет собой вхождение первого символа находящегося в подстроке необходимого текста. В том случае, если данные не были обнаружены, вместо числа происходит возврат ошибки "#ЗНАЧ!".

Вот ее форма в документе. Но без конкретных аргументов: =НАЙТИ(искомый текст; текст для поиска; нач. позиция).

Теперь рассмотрим по отдельности все приведенные аргументы:

• Необходимая строка представляет собой Искомый текст.
• Данные, среди которых происходит поиск первого аргумента, являются Текстом для поиска.
• Начальная позиция не является обязательным элементом. По умолчанию она имеет значение, равное единице.

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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