Главная » Консультация » Работа со строками в Excel. Текстовые функции Excel. Функции для работы с текстом в Excel Текстовые и календарные функции excel

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

качестве аргумента указать ячейку, содержащую текст, – возвращает символы нужно найти; добавляем единицу (+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

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

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

Список текстовых функций

По любой из перечисленных функций можно получить справочную информацию с описанием, синтаксисом и примером, если в мастере функций выбрать интересующую функцию и кликнуть ссылку «Справка по этой функции».

Быстрое использование функций

"БАТТЕКСТ"

В справке к функции "БАТТЕКСТ" написано, что в панели управления, в разделе «Язык и региональные стандарты» можно изменить формат «бат» на другой стиль, но сделать это, как правило, никому не удается. В связи с этим, аналог этой функции на русском языке написан на встроенном в приложения Office языке программирования VisualBasicforApplications и распространяется в виде надстройки.

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

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

"ЗНАЧЕН"

Число в Excel может быть отформатировано, как текст. С такими числами нельзя совершить никаких математических действий, они воспринимаются приложением Excel как обычный текст. Число, отформатированное как текст, можно получить, например, поставив апостроф перед числовым значением или установить в ячейке текстовый формат, а затем внести в него числовое значение. Для преобразования таких чисел в настоящие числа как раз и используется функция «ЗНАЧЕН». Кроме того, преобразовать текст в число можно без использования формулы, при помощи надстройки, которая в заданном диапазоне находит числа, отформатированные как текст и трансформирует их в настоящие числа .

«ПЕЧСИМВ», «ПРОПИСН», «ПРОПНАЧ», «СЖПРОБЕЛЫ», «СТРОЧН»

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

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению сайт, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

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

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

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

Определения аргументов:

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

Пример использования:

Формула: =ЛЕВСИМВ("Произвольный текст";8) – возвращенное значение «Произвол».

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

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ("произвольный текст";5) – возвращенное значение «текст».

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

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

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

Пример использования:

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

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

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

Определения аргументов:

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

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

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

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

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

Определения аргументов:

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ("старый";A1);ДЛСТР("старый") ;"новый")

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

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

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

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

Определения аргументов:

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

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

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

Определения аргументов:

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

Пример использования:

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

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

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

Пример использования:

СЖПРОБЕЛЫ(" Текст с лишними пробелами между словами и по краям " )

Результатом выполнения функции будет строка: "Текст с лишними пробелами между словами и по краям" .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

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

Функция должна содержать не менее одного аргумента

Здесь рассмотрены наиболее часто используемые текстовые функции 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). Функция ПРОПИСН преобразует все буквы текстовой строки в прописные, а СТРОЧН - в строчные. Функция ПРОПНАЧ заменяет прописными первую букву в каждом слове и все буквы, следующие непосредственно за символами, отличными от букв; все остальные буквы преобразуются в строчные. Эти функции имеют следующий синтаксис:

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

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

Познакомимся с вариантами преобразования формулы Excel в текст (в результате получается не значение ячейки, а формульное выражение в текстовом виде, например, "=A1+A2", "=СЕГОДНЯ()" и т.д.).

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

Преобразование формулы в текст в Excel

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

Поменять формат ячейки на текстовый, а затем произвести вычисление формулы;
При этом для каждой ячейки нужно будет вручную производить изменение.
Добавить апостроф (символ «‘») перед знаком равно (символ «=») в формульном выражении.
В данном варианте подставить апостроф можно как вручную, так и через замену («=» на «‘=» с помощью инструмента «Найти и заменить»).

Теперь перейдем к более общему случаю и рассмотрим 2 основных варианта перевода формулы в текст (т.е. получить текстовую запись):

Функция Ф.ТЕКСТ (доступна начиная с версии Excel 2013);
Пользовательская функция (UDF) .

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

Функция Ф.ТЕКСТ в Excel

Начиная с версии Excel 2013 для применения доступна функция Ф.ТЕКСТ (FORMULATEXT в английской версии):

Ссылка (обязательный аргумент) — ссылка на ячейку или диапазон ячеек.

Перейдем к примерам. Применим Ф.ТЕКСТ , в качестве аргумента укажем ссылку на произвольную ячейку, где содержится какое-либо формульное выражение:

При этом в зависимости от выбранного у вас параметра отображения стиля ссылок (A1 или R1C1 ) формула автоматически будет подстраиваться под формат записи:

Замечания

При работе с данной функцией есть несколько важных особенностей, на которые необходимо обратить внимание:

Аргумент "Ссылка" может ссылаться на другие листы и книги;
Если аргумент "Ссылка" не содержит формульное выражение или содержит ссылку на закрытую книгу, то в результате будет возвращено значение ошибки.

Пользовательская функция (UDF)

При использовании версии Excel 2010 или более ранней, стандартными инструментами Excel воспользоваться уже не получится.
Однако данную проблему мы можем решить с помощью создания пользовательской функции (UDF), которая позволит преобразовать формулу в текст в различных вариантах записи в зависимости от стиля ссылок.

Перейдем в редактор ( Alt + F11), вставляем новый модуль и добавляем следующий код:

Visual Basic

Public Function FText(myRange As Range) As String FText = myRange.FormulaLocal End Function

Public Function FText (myRange As Range ) As String

FText = myRange . FormulaLocal

End Function

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

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

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