Работа с текстовыми функциями. Работа со строками в Excel

ТЕКСТОВЫЕ ФУНКЦИИ

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

К примеру, с помощью функций СЕГОДНЯ и ТЕКСТ можно создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг":

="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")

Функция СЦЕПИТЬ объединяет несколько текстовых строк в одну.

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

В качестве аргументов могут быть указаны от 1 до 30 элементов текста, объединяемых в один элемент текста.

Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор &.

Примеры: если в ячейке А5 содержится значение "Петренко", то функция СЦЕПИТЬ("студент ";А5) вернет результат "студент Петренко". Тот же результат будет получен, если использовать выражение = "студент "&А5.

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

Синтаксис: НАЙТИ (искомый_текст; просматриваемый_текст ; нач_позиция). При этом нач_позиция – это позиция символа в просматриваемом тексте, с которой следует начинать поиск. Если этот аргумент опущен, то он полагается равным 1. Аргумент нач_позиция можно использовать, чтобы пропустить нужное количество символов с левого края текста.

Примеры: НАЙТИ("е"; "предприятие") вернет 3, а НАЙТИ("е"; "предприятие";6) вернет 11.

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

Синтаксис: ПОИСК (искомый_текст; текст_для_поиска ; нач_позиция).

В искомом тексте можно использовать символы шаблона: знак вопроса? (соответствует одному любому символу) и звездочка * (соответствует любой последовательности символов). Если искомый_текст не найден, то возвращается значение ошибки #ЗНАЧ! .

Пример: если ячейка B17 содержит слово "доход", а ячейка A14 ПОИСК($B$17;$A$14) равняется 7.

Функция ПОИСК часто используется совместно с функцией ЗАМЕНИТЬ, которая заменяет часть текстовой строки на другую текстовую строку.

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

Старый_текст – это текст, в котором желательно заменить некоторые литеры. Нач_ном – это позиция символа в старом_тексте, начиная с которой происходит замена. Число_литер – это число символов в старом_тексте, которые заменяются новым_текстом. Новый_текст – это текст, который заменяет символы в тексте старый_текст.

Пример: следующая формула заменяет две последние цифры в тексте 1990 на 91: ЗАМЕНИТЬ("1990"; 3; 2; "91") равняется "1991".

Пример совместного использования функций ПОИСК и ЗАМЕНИТЬ : если ячейка B17 по-прежнему содержит слово "доход", а ячейка A14 содержит "Сумма Доходов", то ЗАМЕНИТЬ($A$14;ПОИСК($B$17;$A$14);7;"Расходов") вернет текст "Сумма Расходов". В данном примере функция ПОИСК использована для того, чтобы задать функции ЗАМЕНИТЬ корректное значение аргумента нач_позиция, с которой нужно вставить новый текст.

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

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

При этом номер_вхождения определяет, какое вхождение фрагмента стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то только это вхождение фрагмента стар_текст заменяется. В противном случае, каждое вхождение фрагмента стар_текст в текстовой строке заменяется на фрагмент нов_текст.

Пример: если в ячейке С3 содержится значение "Годовой отчет", то ПОДСТАВИТЬ(С3; "Годовой"; "Недельный") равняется "Недельный отчет".

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

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

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

Примеры: ПСТР("Расчетная ведомость";11;9) равняется "ведомость", а ПСТР("1234"; 5; 5) равняется "" (пустая строка).

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

Для подсчета количества символов в тексте существует функция ДЛСТР (длина строки).

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

Пробелы считаются как символы.

Примеры: ДЛСТР("Харьков – первая столица") возвращает значение 24, а ДЛСТР("") имеет значение 0.

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

Синтаксис: СОВПАД (текст1; текст2) .

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

Синтаксис: ПРОПИСН (текст) ,

СТРОЧН (текст), ПРОПНАЧ (текст).

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

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

Формат должен быть указан в текстовой форме (с вкладки Число диалога Формат ячеек ). Формат не может содержать звездочку (*) и не может быть Общим числовым форматом.

Примеры: если в ячейках А6 и А7 соответственно содержатся значения 2,715 и "15.4.91", то ТЕКСТ (А6; "0,00 грн.") равняется "2,72 грн.", а результатом функции ТЕКСТ(А7; "Д МММ, ГГГГ") будет "15 Апр, 1991".

Замечание: форматирование ячейки меняет только формат, но не значение. Использование функции ТЕКСТ преобразует значение в форматированный текст, и результат больше не участвует в вычислениях как число.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

в Excel, нарядуПреобразования текстовых строкSEARCHB60167 для получения последующих в которой требуется слева строки). искать; с которой будем числовые значения (безИскомый текст.указывается номер символа, ячейке А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

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

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

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

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

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

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

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

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

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

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

как текст и

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

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

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

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

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

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

в том, что

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

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

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

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

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

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

Функция объединения текста в 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 текста

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

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

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

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

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

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

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

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

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

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

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

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

получим корректный результат: отличным от буквы,ASCИзвлечения символов из строкFINDРаботы с двухбайтовыми символами в текстовой строке,
ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(",";B2)+1)) возвращает числовое с помощью функции соединяем ячейки сВ первой строке естьПокажем на примере. Допустим, таком случае стоит данных, хранящихся в содержаться минимум одинФорматов для отображения чисел заранее известном месте
Функцию также преобразуется в,Извлекает определенное число знаков60172
Преобразует однобайтовые английские буквы рассматриваемая функция вернет значение первого символа ЛЕВСИМВ получилось вытащить текстом и пробелы. только имя и
нужно объединить текст обратиться к двум ячейке, которые должны аргумент. Рассмотрим пример в 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 REPTLENB

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

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

"СЦЕПИТЬ"

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

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

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

"ПСТР"

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

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

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

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

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

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

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

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

"ЗАМЕНИТЬ"

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

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

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

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

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

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

"ДЛСТР"

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

"НАЙТИ"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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