Выделение подстроки в строке excel

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

Примеры использования функции ПСТР в Excel

Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

Для заполнения номера дня используем следующую формулу (использовать в качестве формулы массива):

Описание аргументов:

• A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
• 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
• 2 – номер последней позиции символа извлекаемой подстроки.

Аналогичным способом выделим номера месяца и годы для заполнения соответствующих столбцов с учетом, что номер месяца начинается с 4-го символа в каждой строке, а год – с 7-го. Используем следующие формулы:

=ПСТР(A2:A10;4;2)

=ПСТР(A2:A10;7;4)

Вид заполненной таблицы данных:

Таким образом нам удалось разрезать на части текст в ячейках столбца A. Удалось отдельно каждую дату разделить на несколько ячеек по столбцам: день, месяц и год.



Как вырезать часть текста ячейки в Excel?

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

=ПСТР(A2;1;НАЙТИ(» «;A2))

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

Для заполнения столбца «Марка» используем следующую формулу массива:

=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)

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

В результате расчетов получим:

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

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

Описание аргументов:

• текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
• начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
• число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

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

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

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

Скачать примеры ПСТР для разделения текста в Excel

Примечания:

1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

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

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

Как в Excel сделать разделение строки на подстроки

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

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

1. Expression. Это строка, которую нужно разбить на подстроки.
2. Delimiter. Разделитель. Этот аргумент необязательный. Если в нем не указывать никаких значений, то по умолчанию будет в качестве разделителя приниматься пробел.
3. Limit. То количество подстрок, на которое входная должна быть разделена. Этот аргумент также не обязательно указывать. В этом случае в качестве значения по умолчанию будет установлено -1.
4. Compare. С помощью этого аргумента функции передается тип сравнения – двоичный или текстовый. Простыми словами, в первом случае (если тип сравнения установлен на 0), функция учитывает регистр букв при сравнении. В случае же текстового сравнения регистр букв не учитывается.

Значение, которое эта функция вернет – массив, в котором перечислены подстроки, число которых задается параметром limit. Как могло заинтересовать наблюдательного читателя, если поставить значение -1, то функцией будут возвращены все подстроки. И теперь давайте приведем несколько примеров, как работает эта VBA функция.

Sub Test1()

Dim a() As String

a = Split(«vremya ne zhdet»)

MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)

End Sub

Эта функция показывает оповещение, которое выдает три подстроки «vremya ne zhdet». В этом случае стоят настройки по умолчанию. Если записать такой код, то оповещение покажет строку «vremya ne-zhdet» из исходной строки той же самой, только используется вместо пробела дефис.

Sub Test2()

Dim a() As String

a = Split(«vremya-ne-zhdet»,»-«, 2)

MsgBox a(0) & vbNewLine & a(1)

End Sub

Здесь используются значения аргумента Delimiter в –, а Limit – 2. Таким образом, всего возможно была разбивка на три части, но поскольку мы указали только две, то видим, итоговый результат тоже являет собой одну подстроку «vremya» и одну подстроку «ne-zhdet». Видим, что все на самом деле невероятно просто.

Текстовые функции в Эксель

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

1. БАТТЕКСТ(Значение). Функция, необходимая для превращения ячейки числового формата в текстовый. Ее полезно использовать, если формула требует текстового значения, в то время как в ячейке число представлено в виде цифрового. С помощью данной функции можно конвертировать данные из одного типа в другой.
2. ДЛСТР(Значение). Эта функция позволяет определить длину строки и то, сколько символов находится в ней. Возвращает число, соответствующее количеству знаков, которые записаны в этой строке.
3. ЗАМЕНИТЬ(Старый текст, Начальная позиция, число знаков, новый текст). С помощью этой функции можно заменить один текст на другой, в качестве ориентира используя определенное количество знаков, начиная с позиции, которая указана пользователем.
4. ЗНАЧЕН(Текст). Эта функция совершает противоположную первому оператору операцию – значение текстового формата превращает в числовой.
5. ЛЕВСИМВ(Строка, Количество знаков). С помощью этой функции можно получить заданное пользователем количество символов строки, указанной человеком. При этом в учет берутся те знаки, которые располагаются слева.
6. ПРАВСИМВ(Строка, Количество знаков). Принцип работы этой функции аналогичный, только с ее помощью можно вернуть определенное количество знаков справа. То есть, узнать, какой будет часть строки, начиная с самого последнего символа.
7. НАЙТИ(текст для поиска, текст, в котором ищем, начальная позиция). С помощью этой функции можно получить позицию, на которой находится текст, заданный пользователем. Этот оператор можно использовать, только если регистр для нас важен. Если же нет разницы, какие буквы использовать: большие или маленькие, то есть аналогичная функция – ПОИСК. Также следует отметить, что эта функция будет возвращать исключительно первое вхождение, все последующие уже не берутся в учет. Для этого существуют другие функции.
8. ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то она схожа на оператор ЗАМЕНИТЬ, но имеет более широкий функционал. Если пользователь не указал последний аргумент, то замена осуществляется всех вхождений в тексте. Таким образом, это позволяет автоматизировать опции Excel «Заменить все».
9. ПОДСТРОКА(Текст, разделитель, номер). С помощью этой функции можно получить строку, которая была разделена с помощью разделителя.
10. ПСТР (Текст, Начальная позиция, Количество знаков). Это одна из самых главных функций, которую мы сегодня будем разбирать очень подробно. Она в чем-то имеет схожий принцип на ЛЕВСИМВ, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
11. СЦЕПИТЬ (Текст1, Текст2…). Это функция, позволяющая объединить несколько строк. Является некой заменой оператору &. Максимальное количество строк, которые можно соединить между собой – 30.

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

Пример использования текстовых функций в Эксель

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

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

А в этом примере мы попробуем разбить номер телефона на несколько частей.

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

Если нужно отделить одно слово от другого, то можно использовать разделитель в виде пробела. В таком случае надо открыть кавычку, поставить пробел, а потом закрыть кавычку в соответствующем аргументе.

Синтаксис функции ПСТР в Excel

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

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

1. Текст. Текстовая строка, из которой мы будем получать «обрезанный» вариант. Кроме результата работы функции и ссылки на ячейку, в качестве параметра этого аргумента может также выступать и непосредственно текстовая константа. Но на практике ее использование лучше всего подходит для тренировки. В реальной жизни в этом нет необходимости, поскольку всегда можно вручную вставить нужный фрагмент текста в любую ячейку.
2. Начальная позиция. Отсчет знаков для этого аргумента начинается с самого первого символа строки слева. Эта функция отличается от некоторых других тем, что отсчет символов осуществляется с числа 1, а не нуля.
3. Число знаков. Здесь записывается итоговое количество знаков, которые нам надо отсчитать с начальной позиции. Минимальное значение – 1. Чисто гипотетически есть возможность указать в качестве значения этого аргумента 0, но в этом случае в качестве результата будет получена пустая строка.

В своем самом общем виде формула выглядит так: =ПСТР(текст; начальная_позиция; число_знаков)

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

1. Ошибка. Если аргументы функции были неправильно указаны, то появляется ошибка #ЗНАЧ!. Типичные причины появления этой ошибки – нулевая позиция начала или отрицательное значение в аргументе «Число знаков».
2. Строка. Если все параметры были указаны правильно, мы получаем итоговую текстовую строку.

Выделим некоторые моменты, на которые нужно обратить внимание при использовании этой функции:

1. Параметр «Начальная позиция» не может быть больше, чем общая величина строки. Иначе в качестве результата функции будет выдана строка с нулевой длиной (то есть, пустая).
2. Если, даже несмотря на то, что значение «начальная позиция» меньше итоговой длины строки, сумма значений «начальная позиция» и «число знаков» больше, чем общее количество знаков этого текста, то функцией возвращаются оставшиеся знаки, начиная с той позиции, которая указана. Таким образом, можно указать в качестве числа знаков заведомо большое число, чтобы функция вернула те символы, которые расположены справа до самого конца строки.
3. Ошибка #ЗНАЧ! возникает в следующих ситуациях: если начальная позиция меньше единицы, число знаков или число байтов (для функции ПСТРБ) отрицательное.

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

Подстрока из строки в Эксель при помощи функции ПСТР

Давайте рассмотрим небольшой пример того, как можно выделить отдельные знаки из строки с помощью функции ПСТР.  Приведем очень простую ситуацию. Предположим у нас в ячейке B14 записана простая строка, состоящая из последовательности чисел от 1 до 0. Предположим, нам надо из строки 1234567890 получить тройку. В таком случае формула должна быть такой: =ПСТР(B14;3;1).

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

Функции ПСТР, ПСТРБ в Excel

​Смотрите также​ | 0011​’ Purpose :​​ цифр и вставить​​начальная_позиция – обязательный аргумент,​​Вид таблицы данных:​​ качестве первого аргумента,​

Описание

​ регистру.​ лишнего пробела в​ функцию​Скопируйте образец данных из​

​ПОИСКБ​Эта функция возвращает​ строках попадаются​ использовать функцию ПРАВСИМВ(),​ извлечь число или​ возвращает строку «»​

​В этой статье описаны​​В соседней ячейке​

• ​ Выделяет из текста​ ее в соседнюю​ принимающий целые числа​

• ​Для заполнения столбца «Наименование»​ и возвращает требуемое​Заменяет определенный текст или​ конце или начале​СЦЕПИТЬ​ следующей таблицы и​не учитывают регистр.​4​английские буквы​

• ​ но сколько символов​ дату из текстовой​ (пустую строку).​ синтаксис формулы и​ надо получить three​ субстринг/и, ориентируясь по​

• ​ ячейку справа.​ из диапазона от​ используем следующую формулу:​ количество символов начиная​ символ на требуемое​ строки сложно отследить,​. Она последовательно объединяет​ вставьте их в​ Если требуется учитывать​, так как «н»​. Их также можно​ извлечь? Два? А​

​ строки.​Если значение «начальная_позиция» меньше,​ использование функций​Формула, которая это​ символам-разделителям​

Синтаксис

​китин​

​ 1 до N,​

​=ПСТР(A2;1;НАЙТИ(» «;A2))​ с заданной позиции.​

• ​ значение. В Excel​​ данная функция становится​ значения указанных ячеек​ ячейку A1 нового​

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

• ​ делает (я проверял!)​​’ Notes :​: как то так​ где N –​

• ​Функция НАЙТИ возвращает номер​​Один символ в языках​ текстовую функцию​ просто незаменимой. На​ в одной строке.​

Замечания

• ​ листа Excel. Чтобы​НАЙТИ​ в слове «принтер».​ см. Есть ли в​ адресах номер дома​

• ​ т.е. в ней​ но сумма значений​и​ такова:​ Substring(текст; символ_разделитель; Начальный_Номер_фрагмента,​ формула массива​ длина строки, из​ позиции символа пробела​

• ​ с однобайтовой кодировкой​ПОДСТАВИТЬ​ рисунке ниже видно,​Если в Excel необходимо​

• ​ отобразить результаты формул,​и​Можно также находить слова​ слове в MS​

• ​ состоит из 1​ содержатся ссылки на​ «начальная_позиция» и «число_знаков»​ПСТРБ​

Пример

​СЖПРОБЕЛЫ(ЛЕВСИМВ(ПРАВСИМВ(ПОДСТАВИТЬ(D2,»|»,ПОВТОР(» «,99)),198),99))​ Конечный_Номер_фрагмента), где​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);ПОИСКПОЗ(1=1;ЕЧИСЛО(—ПСТР(ПОДСТАВИТЬ(A2;» «;»»);СТРОКА($1:$50);10));0);10)​ которой требуется извлечь​ « » в​ соответствует 1 байту.​применяют, когда заранее​ что содержимое ячеек​ сделать все буквы​ выделите их и​НАЙТИБ​ в других словах.​ EXCEL латинские буквы,​

support.office.com

Разбор текстовых строк в MS EXCEL

​ а затем — клавишу​В аргументе​=ПОИСК(«base»;»database»)​Все статьи сайта, связанные​ можно попытаться найти​ задачи. Начнем с​ знаки вплоть до​ число знаков из​ 99?​’ символ_разделитель -​SergeyKorotun​ в строке соответствует​ аргумента число_знаков функции​ функций ПСТР и​ не его местоположение.​ это не так.​ регистр, на помощь​ ВВОД. При необходимости​искомый_текст​

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

​ символ, который надо​, а вообще то​ числу 1. Если​ ПСТР. В результате​ ПСТРБ (возвращает подстроку​Приведенная ниже формула заменяет​ В ячейке А1​ придет текстовая функция​ измените ширину столбцов,​можно использовать подстановочные​5​ строк собраны в​ которой идет номер​Самый простейший случай, если​Если значение «начальная_позиция» меньше​ с указанной позиции.​ объясните пожалуйста.​ считать разделителем фрагментов​ с вашим стажем​ данный аргумент принимает​ расчетов получим:​ из строки на​ все вхождения слова​ мы намеренно поставили​СТРОЧН​ чтобы видеть все​ знаки: вопросительный знак​, так как слово​ этом разделе: Изменение Текстовых​ дома. Это можно​ адрес, состоящий из​ 1, то функция​Функция ПСТРБ возвращает определенное​

​AlexM​’ Начальный_Номер_фрагмента -​ можно бы и​ дробное число из​Для заполнения столбца «Марка»​ основе количества заданных​ «Excel» на «Word»:​ лишний пробел в​. Она не заменяет​ данные.​

​ (​ «base» начинается с​ Строк (значений).​ сделать с помощью​

​ названия города, улицы​ ПСТР возвращает значение​ число знаков из​: А где ваш​

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

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

​ функции ПОИСК() (см. статью​ и т.д., импортирован​ ошибки #ЗНАЧ!.​ текстовой строки, начиная​ файл?​ с которого нужна​ тут красиво на​ дробная часть будет​ массива:​ Если на компьютере​ слова «Excel»:​Excel​ буквами.​Выписки​) и звездочку (​ «database». Можно использовать​ товара: 2-3657; 3-4897;​ Нахождение в MS​ в ячейку MS​Если значение «число_знаков» отрицательно,​ с указанной позиции,​198 = 2*99​ выборка​ любое кол-во цифр​ усечена;​=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)​ используется двухбайтовый язык,​Удаляет все пробелы из​. В итоге функция​Текстовая функция​Доход: маржа​

​*​ функции​ … ​ EXCEL позиции n-го​ EXCEL из другой​ то функция ПСТР​ на основе заданного​buchlotnik​’ Конечный_Номер_фрагмента -​Сергей-К​число_знаков – обязательный аргумент,​Функция НАЙТИ возвращает позицию​ каждый символ при​ текстовой строки:​СОВПАД​ПРОПИСН​маржа​). Вопросительный знак соответствует​ПОИСК​Как видно, артикул состоит​ вхождения символа в​ информационной системы. В​ возвращает значение ошибки​ числа байтов.​: оформите формулу тегами,​ порядковый номер фрагмента,​: Добрый вечер. Помогите,​ принимающий значение из​

​ символа пробела. К​ использовании ПСТРБ будет​Заменяет символы, расположенные в​возвратила нам значение​делает все буквы​Здесь «босс».​ любому знаку, звездочка —​и​ из 2-х числовых​ слове). Далее нужно​ этом случае у​ #ЗНАЧ!.​Важно:​

​ приложите файл -​ по который нужна​​ пожалуйста, «выдернуть» часть​ диапазона неотрицательных чисел,​ полученному числу прибавляется​ считаться за два.​ заранее известном месте​ ЛОЖЬ.​ прописными, т.е. преобразует​

​Формула​ любой последовательности знаков.​​ПОИСКБ​ частей, разделенных дефисом.​

​ вычислить количество цифр​​ адреса имеется определенная​Если значение «число_байтов» отрицательно,​

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

​ их в верхний​Описание​ Если требуется найти​для определения положения​ Причем, числовые части​

Артикул товара

​ номера дома. Это​ структура (если элементы​ то функция ПСТРБ​

​Эти функции могут быть​Цитата​’—————————————————————————————​ До этого работал​ в символах возвращаемой​ позиции первого символа​ корейский, японский и​ значение. В Excel​СЖПРОБЕЛЫ​

​ регистр. Так же,​Результат​ вопросительный знак или​ символа или текстовой​

​ имеют строго заданный​ сделано в файле​

​ адреса хранились в​ возвращает значение ошибки​

​ доступны не на​откуда там цифры​On Error Resume​ только с функциями​ подстроки. Если в​ названия марки продукта.​ китайский.​ текстовую функцию​к значению ячейки​ как и​=ПОИСК(«и»;A2;6)​ звездочку, введите перед​ строки в другой​ размер: первое число​

ВНИМАНИЕ!

​ примера, ссылка на​ отдельных полях) и​ #ЗНАЧ!.​ всех языках.​цифры​ Next​ ПСТР, ЛЕВСИМВ и​ качестве этого аргумента​ Итоговое значение используется​Пример 1. В столбце​ЗАМЕНИТЬ​

excel2.ru

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​ А1, мы удалим​СТРОЧН​Позиция первого знака «и»​​ ним тильду (​​ текстовой строке, а​​ состоит из 1​​ который внизу статьи.​

Описание

​ скорее всего нет​​Скопируйте образец данных из​​Функция ПСТР предназначена для​​там 1,8 и​​Dim sArr() As​ ПРАВСИМВ, но в​ передано число 0​ в качестве аргумента​ таблицы содержатся даты,​применяют, когда известно​ из него все​, не заменяет знаки,​ в строке ячейки​~​ затем вернуть текст​

​ цифры, второе -​

​Усложним ситуацию. Пусть подстрока «д.»​​ (мало) опечаток. Разгадав​​ следующей таблицы и​ языков с однобайтовой​ 9 , скорее​

​ String, li As​ данном случае задача​ (нуль), функция ПСТР​

​ начальная_позиция функции ПСТР.​

​ записанные в виде​​ где располагается текст,​​ лишние пробелы и​ не являющиеся буквами.​ A2, начиная с​).​ с помощью функций​​ из 4-х.​​ может встречаться в​​ структуру можно быстро​​ вставьте их в​ кодировкой, а ПСТРБ​ всего, введены с​ Long​ осложняется неодинаковым количеством​ вернет пустую строку.​​ Для упрощения, вместо​​ текстовых строк. Записать​​ при этом сам​​ получим корректный результат:​Текстовая функция​​ шестого знака.​​Если​​ПСТР​​Задача состоит в том,​ адресе несколько раз,​ разнести адрес по​

​ ячейку A1 нового​​ — для языков​

• ​ клавиатуры, а вот​sArr = Split(Application.Trim(Текст),​ знаков в ячейке.​

• ​ Если аргумент задан​ поиска номера последней​ отдельно в соседних​ он не важен.​Функцию​ПРОПНАЧ​7​искомый_текст​и​ чтобы определить артикулы,​ например, при указании​ столбцам. Например, адрес «г.Москва,​ листа Excel. Чтобы​

​ с двухбайтовой кодировкой.​числа​ Символ_разделитель)​Спасибо.​ числом, превышающим количество​

Синтаксис

​ позиции (например, с​

​ столбцах номер дня,​

​Формула в примере ниже​СЖПРОБЕЛЫ​

• ​делает прописной первую​​=ПОИСК(A4;A3)​не найден, возвращается​

• ​ПСТРБ​​ у которых левый​ названия деревни используется​ ул.Тверская, д.13″ очевидно​​ отобразить результаты формул,​​ Язык по умолчанию,​

• ​помогают искать предпоследнее​​If Конечный_Номер_фрагмента >​Nic70y​​ символов в строке,​​ помощью функции ДЛСТР)​ месяца и год,​

Замечание

• ​ заменяет 4 символа,​​полезно применять к​​ букву каждого слова,​​Начальная позиция строки «маржа»​​ значение ошибки #ЗНАЧ!.​или заменить его​ индекс​​ сокращение «д.», т.е.​​ состоит из 3-х​​ выделите их и​​ заданный на компьютере,​

• ​ значение — каждый​​ 0 Then​​:​ будет возвращена вся​ указано число 100,​​ выделенные из представленных​​ расположенные, начиная с​​ данным, которые импортируются​​ а все остальные​ (искомая строка в​Если аргумент​ с помощью функций​Первая часть задачи решается​ совпадает с префиксом​ блоков: город, улица,​​ нажмите клавишу F2,​​ влияет на возвращаемое​

• ​ разделитель заменяется на​​Начальный_Номер_фрагмента = Начальный_Номер_фрагмента​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(C2;ПОИСК(«(«;C2)+1;99);»)»;ПОВТОР(» «;99));99))​ часть строки начиная​

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

• ​ преобразует в строчные.​​ ячейке A4) в​​начальная_позиция​ЗАМЕНИТЬ​ формулой =—ЛЕВСИМВ(A16;1)​​ номера дома. В​​ дом, разделенных пробелами​ а затем —​

• ​ значение следующим образом.​​ 99 пробелов, потом​​ — 1​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(C2;ПОИСК(«»;ПОДСТАВИТЬ(C2;»(«;»»;2))+1;99);»)»;ПОВТОР(» «;99));99))Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(C2;ПОИСК(«-«;C2)-6;5)Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(C2;ПОИСК(«-«;C2)+13;5)такие варианты​ с указанной вторым​ примере гарантированно превышает​​Вид исходной таблицы данных:​​ значение «2013». Применительно​ Excel из внешних​Каждая первая буква, которая​ строке «Доход: маржа»​опущен, то он​и​Вторая часть задачи решается​​ этом случае нужно​​ и запятыми. Кроме​ клавишу ВВОД. При​Функция ПСТР всегда считает​ берется 198 символов​Конечный_Номер_фрагмента = Конечный_Номер_фрагмента​Сергей-К​ аргументом позиции. В​​ количество знаков в​​Для заполнения номера дня​ к нашему примеру,​ источников. Такие данные​ следует за знаком,​​ (ячейка, в которой​​ полагается равным 1.​ЗАМЕНИТЬБ​ формулой =ЗНАЧЕН(ПРАВСИМВ(A16;4)). ​​ определить все строки,​​ того, перед названием​ необходимости измените ширину​ каждый символ (одно-​​ справа, т.е. подстрока,​​ — 1​: Да, отлично. А​ дробных числах, используемых​​ изначальной строке.​​ используем следующую формулу​

Примеры

​ формула заменит «2010»​ очень часто содержат​ отличным от буквы,​ выполняется поиск — A3).​Если аргумент​. Эти функции показаны​Зачем нам потребовалась функция ЗНАЧЕН()?​ в которых имеется​ стоят сокращения г.,​ столбцов, чтобы видеть​ или двухбайтовый) за​ включающая последние два​For li =​

support.office.com

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

​ в ней 99​ = Начальный_Номер_фрагмента, sArr(li),​: дополнил сообщение выше​Функция ПСТРБ имеет схожий​ сотрудниках в столбцах​A2:A10 – диапазон ячеек​ слово «Excel», на​ непечатаемые символы из​ подсчитать количество знаков,​ «объем», определяя позицию​ длина​ ​ возвращают текст, а​ пункта) и исключить​​ MS EXCEL Текст​​Формула​

СЦЕПИТЬ

​Функция ПСТРБ считает каждый​ символов слева -​ Substring & _​Сергей-К​ синтаксис:​​ ФИО и дата​​ с текстовым представлением​ «Word».​ текста, необходимо воспользоваться​

СТРОЧН

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

ПРОПИСН

​Символ_разделитель & sArr(li))​​:​​=ПСТРБ(текст;начальная_позиция;число_байтов)​ рождения. Создать столбец,​ дат, из которых​Вот и все! Мы​ функцией​​ строке, для этого​​ ячейке A3 и​, возвращается значение ошибки​

ПРОПНАЧ

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

​ предпоследнее значение (и​Next li​Nic70y​Она отличается единственным аргументом:​ в котором будет​

ДЛСТР

​ будут выделены номера​ познакомились с 15-ю​ПЕЧСИМВ​ воспользуйтесь функцией​ заменяя этот знак​​ #ЗНАЧ!.​​ всех языках.​

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

​ число в текстовом​​ из строки в​​ в статье Текст-по-столбцам (мастер​​=ПСТР(A2;1;5)​​ поддержка ввода на​ куча пробелов), а​Else​, спасибо, всё работает!​число_байтов – обязательный аргумент,​

ПСТР

​ отображаться фамилия сотрудника​​ дней;​​ текстовыми функциями Microsoft​.​ДЛСТР​ и последующие пять​

СОВПАД

​Аргумент​​Функция ПОИСКБ отсчитывает по​​ формате). Для того,​ отдельный диапазон (см.​ текстов) в MS​Возвращает пять знаков из​ языке с двухбайтовой​ потом СЖПРОБЕЛЫ() наводит​Substring = Split(Application.Trim(Текст),​ Очень оперативно!​ принимающий целые числа​ и его возраст​1 – номер начальной​

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

СЖПРОБЕЛЫ

​ статью Извлекаем в MS​ EXCEL.​ строки в ячейке​

​ кодировкой, а затем​ красоту, как-то так​ _​ShAM​ из диапазона от​ в формате «Иванов​ позиции символа извлекаемой​ их действие на​ПОВТОР​Текстовые функции​ «объем.»​можно использовать, чтобы​ каждый символ, только​ таким числам в​ EXCEL число из​​Очевидно, что не всегда​​ А2, начиная с​​ этот язык назначен​​[scv]​Символ_разделитель)(Начальный_Номер_фрагмента — 1)​

​: Как вариант (для​​ 1 до N,​​ – 27».​ подстроки (первый символ​ простых примерах. Надеюсь,​повторяет текстовую строку​ЛЕВСИМВ​

​Доход: объем​​ пропустить определенное количество​​ если языком по​ текстовом формате операцию​ конца текстовой строки).​ адрес имеет четкую​ первого знака.​ языком по умолчанию.​: Число (в данном​End If​ скобок):​ где N –​Вид исходной таблицы:​ в исходной строке);​​ что данный урок​​ указанное количество раз.​

ПОВТОР

​и​​=ПСТР(A3;ПОИСК(» «;A3)+1,4)​​ знаков. Допустим, что​ умолчанию является язык​ сравнения с другим​ Но, что делать,​ структуру, например, могут​Поток​

НАЙТИ

​ В противном случае​​ случае 99) -​​End Function​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(C2;ПОИСК(«(«;C2)+1;ПОИСК(«)»;C2)-ПОИСК(«(«;C2)-1)​ число байтов в​Для возврата строки с​2 – номер последней​ пришелся Вам как​

​ Строка задается как​ПРАВСИМВ​

​Возвращает первые четыре знака,​ функцию​ с поддержкой БДЦС.​ числом, т.е. ​ если в названии​ быть пропущены пробелы​=ПСТР(A2;7;20)​​ функция ПСТРБ считает​​ должно быть минимум​krosav4ig​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПСТР(C2;ПОИСК(«(«;C2;ПОИСК(«(«;C2;1)+1)+1;ПОИСК(«)»;C2;ПОИСК(«)»;C2;1)+1)-ПОИСК(«(«;C2;ПОИСК(«(«;C2;1)+1)-1)​ исходной строке, характеризующий​

ПОИСК

​ фамилией и текущим​​ позиции символа извлекаемой​​ раз кстати, и​ первый аргумент функции,​​возвращают заданное количество​​ которые следуют за​ПОИСК​ В противном случае​​Если у Вас есть​​ улицы есть числа?​ (запятые все же​

ПОДСТАВИТЬ

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

​ а количество повторов​ символов, начиная с​ первым пробелом в​

​нужно использовать для​ функция ПОИСКБ работает​

​ примеры или вопросы,​ Например, «26 Бакинских​

ЗАМЕНИТЬ

​ стоят). В этом​ строки в ячейке​ один.​ двух сегментов. Больше​ зашла за UDF,​​: Ну когда я​​ возвращаемой подстроке.​ формулу:​Аналогичным способом выделим номера​ него хотя бы​

​ как второй.​ начала или с​ строке «Доход: маржа»​ работы с текстовой​ так же, как​ связанные с разбором​ комиссаров». Короче, тут​ случае помогут функции,​

​ А2, начиная с​К языкам, поддерживающим БДЦС,​ либо равно. Тогда​ вот еще вариант​

​ вырасту для таких​Примечания:​Функция ПСТР возвращает часть​ месяца и годы​ малость полезной информации.​Текстовая функция​ конца строки. Пробел​ (ячейка A3).​ строкой «МДС0093.МужскаяОдежда». Чтобы​ функция ПОИСК, и​ текстовых строк -​ начинается творчество. ​ работающие с текстовыми​ седьмого знака. Так​ относятся японский, китайский​

​ можно полностью получить​

office-guru.ru

Подсчет вхождений в MS EXCEL текстового значения в строке

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function substring(s$, n%) As​ формул?​Функция ПСТР вернет пустую​ строки до символа​

​ для заполнения соответствующих​ Всего доброго и​НАЙТИ​ считается за символ.​​марж​​ найти первое вхождение​

​ отсчитывает по одному​ смело пишите в​Не забудьте про пробелы!​
​ строками. Вот эти​

​ как количество возвращаемых​ (упрощенное письмо), китайский​

​ содержание предпоследнего сегмента,​
​ Variant​

​ShAM​ строку, если в​ пробела, позиция которого​ столбцов с учетом,​ успехов в изучении​

excel2.ru

Пример функция ПСТР для разделения текста на части в Excel

​находит вхождение одной​Текстовая функция​=ПОИСК(«»»»;A5)​ «М» в описательной​ байту на каждый​ комментариях к этой​ Каждый пробел -​ функции:​

Примеры использования функции ПСТР в Excel

​ знаков (20) больше​ (традиционное письмо) и​ будет меньше -​Select Case n​, спасибо!​ качестве аргумента начальная_позиция​ определяется функцией НАЙТИ.​ что номер месяца​ Excel!​ строки в другую​ПСТР​Позиция первой двойной кавычки​ части текстовой строки,​ символ.​ статье или в​ это отдельный символ.​- Функция ЛЕВСИМВ() в MS​ длины строки (10),​

Как разделить текст на несколько ячеек по столбцам в Excel?

​ корейский.​ будет обрезка от​Case 1: substring​AlexM​ было передано число,​ Для нахождения возраста​ начинается с 4-го​Автор: Антон Андронов​ и возвращает положение​

​возвращает заданное количество​

​ («) в ячейке​ задайте для аргумента​К языкам, поддерживающим БДЦС,​ группу ! Я​

​ Часто при печати​

• ​ EXCEL — выводит нужное​ возвращаются все знаки,​ПСТР(текст;начальная_позиция;число_знаков)​ начала. Всем спасибо!​ = Split(Split(s, «)»)(0),​
• ​: Еще вариант для​ превышающее количество символов​ сотрудника используется функция​ символа в каждой​
• ​Найдем количество вхождений подстроки​ первого символа искомой​ символов, начиная с​

​ A5.​начальная_позиция​ относятся японский, китайский​ дополню эту статью​ их ставят 2​ количество левых символов​ начиная с седьмого.​ПСТРБ(текст;начальная_позиция;число_байтов)​AndreTM​ «(«)(1)​

​ скобок​

​ в исходной строке.​

​ ДОЛЯГОДА, полученное значение​

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

​ строки;​

Как вырезать часть текста ячейки в Excel?

​ Пустые символы (пробелы)​Аргументы функций ПСТР и​: На самом деле​Case 2: substring​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРАВБ(ЗАМЕНИТЬ(C2;ПОИСК(«)»;C2);99;»»);2)​Если в качестве аргумента​ которой усекается до​ – с 7-го.​ символов) в исходной​ текста.​

​ считается за символ.​

​=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1)​ поиск не выполнялся​

​ (традиционное письмо) и​

​ них.​ а это совсем​- Функция ПРАВСИМВ() в MS​ не добавляются в​ ПСТРБ описаны ниже.​ «чисел» там три.​ = Split(Split(s, «)»)(1),​иКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРАВБ(ЗАМЕНИТЬ(C2;ПОИСК(«)»;C2;ПОИСК(«)»;C2)+1);99;»»);4)​

​ начальная_позиция было передано​ ближайшего меньшего целого,​ Используем следующие формулы:​

​ текстовой строке.​

​Данная функция чувствительна к​Функция​Возвращает из ячейки A5​ в той части​ корейский.​В этой статье описаны​ не то же​ EXCEL — выводит нужное​ конец строки.​Текст​И на самом​ «(«)(1)​Сергей-К​ значение 1, а​ чтобы получить число​=ПСТР(A2:A10;4;2)​Определим число вхождений подстроки​ регистру…​

​СОВПАД​

Как посчитать возраст по дате рождения в Excel?

​ только текст, заключенный​ текста, которая является​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ синтаксис формулы и​ самое, что один​ количество правых символов​воды​    Обязательный. Текстовая строка, содержащая​ деле, должно быть​Case 3: substring​

​:​

​ аргумент число_знаков определен​ полных лет. Функция​=ПСТР(A2:A10;7;4)​ ZZ в слове​

​… и может начинать​позволяет сравнить две​ в двойные кавычки.​ серийным номером (в​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​ использование функций​ пробел. Используйте функцию Функция​ строки;​=ПСТР(A2;20;5)​ символы, которые требуется​ так: само число​ = TimeValue(Right(Split(s, «:»)(0),​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПРАВБ(ЗАМЕНИТЬ(C2;ПОИСК(«)»;C2);99;»»);2)​ числом, которое равно​

​ ТЕКСТ преобразует полученное​Вид заполненной таблицы данных:​ ZZigzzag (пусть слово​ просмотр текста с​

Особенности использования функции ПСТР в Excel

​ текстовые строки в​босс​

​ данном случае —​

​Аргументы функций ПОИСК и​

• ​ПОИСК​ СЖПРОБЕЛЫ() в MS​- Функция ПСТР() в MS​Так как начальная позиция​ извлечь.​ должно быть больше​ 2) & _​А с этой​ или больше общему​ значение в текстовую​
• ​Таким образом нам удалось​ находится в ячейке​ указанной позиции. На​ Excel. Если они​Excel предлагает большое количество​ «МДС0093″). Функция​ ПОИСКБ описаны ниже.​и​ EXCEL, чтобы избавиться​ EXCEL — выводит часть​ больше, чем длина​Начальная_позиция​ длины подстроки, в​»:» & Left(Split(s,​ хоть будет попроще​ числу знаков в​
• ​ строку.​ разрезать на части​A1​ рисунке ниже формула​ в точности совпадают,​ функций, с помощью​ПОИСК​Искомый_текст​ПОИСКБ​ от лишних пробелов.​ текста из середины​ строки (10), возвращается​    Обязательный. Позиция первого знака,​ которую входит искомый​ «:»)(1), 2))​ разобраться. Спасибо,​ исходной строке, функция​Для соединения (конкатенации) полученных​ текст в ячейках​).​ начинает просмотр с​

​ то возвращается значение​ которых можно обрабатывать​

​начинает поиск с​

​    Обязательный. Текст, который требуется​

• ​в Microsoft Excel.​Об извлечении чисел из​ строки.​ пустая строка.​ извлекаемого из текста.​ сегмент (слева или​Case 4: substring​AlexM​ ПСТР вернет всю​

​ строк используются символы​

1. ​ столбца A. Удалось​Формула для подсчета с​ четвертого символа, т.е.​ ИСТИНА, в противном​ текст. Область применения​ восьмого символа, находит​
2. ​ найти.​Функции​ текстовой строки​Используя комбинации этих функций​Часто текстовая строка может​ Первый знак в​ справа, который короче​ = TimeValue(Right(Split(s, «:»)(1),​,​ строку целиком.​
3. ​ «&». В результате​ отдельно каждую дату​ учетом регистра, см.​ c буквы «​ случае – ЛОЖЬ.​ текстовых функций не​
4. ​ знак, указанный в​Просматриваемый_текст​ПОИСК​см. здесь: Извлекаем в​ можно в принципе​

exceltable.com

выделить из строки подстроку из 10 цифр (Формулы/Formulas)

​ содержать несколько значений.​​ тексте имеет начальную​ выйдет, складываем при​ 2) & _​Hugo​Если аргумент начальная_позиция был​ вычислений получим:​

​ разделить на несколько​​ файл примера:​r​​ Данная текстовая функция​

​ ограничивается исключительно текстом,​​ аргументе​​    Обязательный. Текст, в котором​​И​ MS EXCEL число​ разобрать любую строку,​ Например, адрес компании:​ позицию 1 и​ этом максимальные возможные​

excelworld.ru

Выдернуть текст из середины текста (Формулы/Formulas)

​»:» & Left(Split(s,​​: Если всёж использовать​ указан числом из​Функция имеет следующую синтаксическую​ ячеек по столбцам:​=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;»ZZ»;»»)))/ДЛСТР(«ZZ»)​«. Но даже в​ учитывает регистр, но​ они также могут​искомый_текст​ нужно найти значение​
​ПОИСКБ​

​ из начала текстовой​​ имеющую определенную структуру.​​ "г.Москва, ул.Тверская, д.13",​​ так далее.​

​ длины сегментов), в​​ «:»)(2), 2))​ одну UDF:​ диапазона отрицательных чисел​

​ запись:​​ день, месяц и​

​Ответ: число вхождений равно​​ этом случае положение​​ игнорирует различие в​​ быть использованы с​, в следующей позиции,​

​ аргумента​​находят одну текстовую​ строки или здесь Извлекаем в​
​ Об этом смотри​
​ т.е. название города,​

​Число_знаков​​ ПОВТОР() и «внешнем»​End Select​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Substring(Substring(C2;»(«;2;2);»)»;1;1)​​ или 0 (нулем),​​=ПСТР(текст;начальная_позиция;число_знаков)​

​ год.​​ 1.​ символа считается относительно​​ форматировании.​​ ячейками, содержащими числа.​

​ и возвращает число​​искомый_текст​​ строку в другой​​ MS EXCEL число​ статью Разнесение в MS​ улицы и номер​​    Обязательный. Указывает, сколько знаков​​ *СИМВ() используем это​

​End Function​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Substring(Substring(C2;»(«;3;3);»)»;1;1)Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Substring(Substring(C2;»)»;2;2);» «;2;2)Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=Substring(Substring(C2;»)»;2;2);» «;5;5)​ функция ПСТР вернет​
​Описание аргументов:​​​
​Теперь без учета регистра:​
​ начала просматриваемого текста.​Если регистр для Вас​ В рамках данного​
​ 9. Функция​.​ и возвращают начальную​
​ из середины текстовой​
​ EXCEL текстовых строк​ дома. Если необходимо​
​ должна вернуть функция​ число, а во​и еще формулы​код:​
​ код ошибки #ЗНАЧ!.​текст – обязательный для​Пример 2. В столбце​
​=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(СТРОЧН(A1);»zz»;»»)))/ДЛСТР(«zz»)​Текстовая функция​
​ не играет большой​ урока мы на​ПОИСК​
​Начальная_позиция​ позицию первой текстовой​ строки.​ по столбцам.​
​ определить все компании​ ПСТР.​ «внутреннем» *СИМВ() -​ до кучи (все​
​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function Substring(Текст As​
​Если аргумент число_знаков задан​ заполнения аргумент, принимающий​
​ таблицы хранятся текстовые​В статье Подсчет вхождений​ПОИСК​
​ роли (так бывает​ примерах рассмотрим 15​
​всегда возвращает номер​    Необязательный. Номер знака в​
​ строки (считая от​Об извлечении названия файла​
​Еще раз отмечу, что​ в определенном городе,​
​Число_байтов​ это же число,​
​ массивные)​ String, Символ_разделитель As​ отрицательным числом, результатом​
​ ссылку на ячейку​
​ записи с наименованием​
​ символа показано, как​
​очень похожа на​ в большинстве случаев),​
​ наиболее распространенных функций​
​ знака, считая от​
​ аргументе​

​ первого символа второй​​ из полного пути​ перед использованием функций​ то нужно «разобрать»​
​    Обязательный. Указывает, сколько знаков​ умноженное на количество​
​1 скобкиКод200?'200px':''+(this.scrollHeight+5)+'px');">=ПСТР(C2;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(C2;СТОЛБЕЦ(1:1);1)=")";СТОЛБЕЦ(1:1);"");1)-2;2)2 скобкиКод200?'200px':''+(this.scrollHeight+5)+'px');">=ПСТР(C2;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(C2;СТОЛБЕЦ(1:1);1)=")";СТОЛБЕЦ(1:1);"");2)-4;4)1​
​ String, _​ выполнения функции ПСТР​ с текстом или​
​ и маркой товаров.​ подсчитать количество вхождений​ функцию​
​ то можно применить​ Excel из категории​ начала​
​просматриваемый_текст​ текстовой строки). Например,​
​см. Извлечение имени файла​ необходимо понять структуру​ адрес на несколько​
​ должна вернуть функция​ попавших в вышеуказанную​
​ времяКод200?'200px':''+(this.scrollHeight+5)+'px');">=ПСТР(C2;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(C2;СТОЛБЕЦ(1:1);1)=":";СТОЛБЕЦ(1:1);"");1)-2;5)2 времяКод200?'200px':''+(this.scrollHeight+5)+'px');">=ПСТР(C2;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(C2;СТОЛБЕЦ(1:1);1)=":";СТОЛБЕЦ(1:1);"");2)-2;5)​
​Начальный_Номер_фрагмента As Long,​

​ будет код ошибки​ текстовую строку, заключенную​ Разделить имеющиеся строки​
​ одного символа в​НАЙТИ​

excelworld.ru

Поиск подстроки в строке с разделителями (Формулы/Formulas)

​ формулу, просто проверяющую​​Текстовые​
​просматриваемого текста​, с которого следует​ чтобы найти позицию​ в MS EXCEL.​
​ текстовой строки, которую​ составляющих. Аналогичный подход​
​ ПСТРБ (в пересчете​ подстроку сегментов (минус​[scv]​
​ Конечный_Номер_фрагмента As Long)​
​ #ЗНАЧ!.​ в кавычки, из​ на подстроки с​
​ текстовой строке.​, основное их различие​

​ равенство двух ячеек.​​.​, включая символы, которые​
​ начать поиск.​

​ буквы «n» в​​Про разбор фамилии​ требуется разобрать. Например,​ потребуется, если необходимо​
​ на байты).​​ один). На практике​​: Приветствую всех!!​​ As String​SergeyKorotun​ которой будет извлечена​ наименованием и маркой​​Функция ПСТР в Excel​​ заключается в том,​Удаляет из текста все​Для объединения содержимого ячеек​ пропускаются, если значение​Функции​ слове «printer», можно​см. Разделяем пробелами Фамилию,​ извлечем номер дома​ разнести по столбцам​Если значение «начальная_позиция» больше,​ обычно просто берут​Есть ячейка с​’—————————————————————————————​: Нужно в строке​ подстрока определенной длины​ соответственно и записать​

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

​ текстом one |​​’ URL :​ найти подстроку из​
​ начиная с указанной​ полученные значения в​ подстроки из строки​ПОИСК​ одиночных между словами.​ с оператором конкатенации,​начальная_позиция​и​=ПОИСК(«н»;»принтер»)​Часто в русских текстовых​ Понятно, что потребуется​ артикул товара или​ то функция ПСТР​ длины строки.​ two | three​ http://www.planetaexcel.ru/tip.php?aid=54​ подряд идущих 10​ позиции первого символа;​ соответствующие столбцы таблицы.​ текста, переданной в​не чувствительна к​В случаях, когда наличие​ можно использовать текстовую​

excelworld.ru

​больше 1.​

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

Функция ПСТР (MID) – подробное описание

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

Синтаксис предельно прост:

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

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

1. Текст. Содержимым ячейки могут выступать значения в разных форматах как ссылки, так и текстовая строка. Впрочем, может использоваться и ячейка любого другого формата. Например, можно таким способом достать месяц из даты. Для этого нужно в поле «Текст» указать ссылку на ячейку с датой. Важно убедиться, что она была предварительно конвертирована в текстовый формат.
2. Первоначальная позиция. Это числовое значение того символа, с какого начинается извлечение подстроки.
3. Число знаков. Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов.

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

Функция ПСТРБ (новая функция)

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

В чем разница между ПСТР и ПСТРБ?

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

Функция ПСТР на английском

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

Функция ПСТР и VBA в Excel

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

Range(“A2”).Value = Mid(Range(“A1”), 17, 10)

Распространенные ошибки в использовании функции ПСТР

При использовании функции ПСТР возможны ошибки, если неправильно соблюдать синтаксис или нарушить некоторые основополагающие правила:

1. Нельзя писать в качестве аргумента этой функции отрицательное значение. В таком случае будет выдана ошибка #ЗНАЧ.
2. Важно следить, чтобы начальная позиция не была большим числом, чем длина искомой строки. В ином случае будет выдано пустое значение.

Как вырезать часть текста ячейки в Эксель

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР .

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

Сама таблица выглядит следующим образом.

1

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

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

После этого получаем такой результат.

2

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

=ПСТР(A2:A8;НАЙТИ(” “;A2:A8)+1;100)

Здесь мы снова видим, что использовали функцию НАЙТИ , с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки.

Так можете делать и вы.

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

3

Как рассчитать возраст по дате рождения в Эксель

Теперь приведем еще один пример, с каким сотрудники, часто использующие Эксель, сталкиваются довольно часто. Перед нами есть база данных, содержащая три колонки: Фамилия, имя, отчество, а также дата рождения. И перед нами стоит задача определить, сколько лет человеку в данный момент, с использованием ПСТР.

Вот так выглядит таблица, с которой мы будем работать.

4

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

Эта функция сначала определяет ту часть строчки, которая содержится до символа пробела. Это делается с помощью функции НАЙТИ аналогично приведенным выше примерам. Далее с использованием функции РАЗНДАТ мы определяем количество полных лет. В нашем случае мы автоматически отсекаем тот участок дней, который остался до следующего дня рождения в этом году. Именно поэтому данная функция сможет указать всегда правильный возраст.

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

4

Несколько особенностей использования ПСТР в Excel

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

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

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

1. Текст. Это обязательный аргумент, который нужно передавать функции. Представляет собой или ссылку на ячейку, или непосредственно строку, из которой нужно извлекать требуемую информацию. Важно обратить внимание, что в последнем случае ее нужно облачать в кавычки. Независимо от формата строки, из нее будет доставаться определенная информация. Какая именно – задается следующими аргументами.
2. Начальная позиция. Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
3. Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка.

Также важно понимать, что для функции ПТСРБ используется очень похожий синтаксис за тем лишь исключением, что вместо количества знаков после стартовой позиции задается количество байтов, начиная с этой точки.

Напоминаем, что синтаксис следующий:

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

Как говорится, повторение – мать учения. Поэтому давайте подведем небольшие промежуточные итоги:

1. Если на месте стартовой позиции задавать значение, которое по размеру больше исходной строки, то после всех операций, выполняемых функцией ПСТР вернется пустое значение.
2. Если применять единицу в качестве исходной позиции, а количество знаков указать такое, которое будет больше строки или равняться ей, то в качестве итога будет выведено все содержимое этой строки. Таким образом, можно использовать эту функцию в роли альтернативы, пусть и не такой удобной, ссылки на ячейку. В Excel ситуации бывают разные, поэтому иногда приходится выкручиваться из любой ситуации, в том числе, и такими причудливыми способами.
3. Будет возвращена ошибка #ЗНАЧ!, если использовать отрицательное значение в качестве начальной позиции. То же касается ситуации, если аргумент с числом знаков задается отрицательным значением. Важно запомнить навсегда, использовать нулевое или отрицательное значение в этом аргументе нельзя.

Примеры использования функции ПСТР

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

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

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

Сама таблица выглядит следующим образом:

5

В этом случае нужно применить формулу массива, а именно такую.

6

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день.
2. 1 – это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение.
3. 2 – это последняя позиция.

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

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

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

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

В результате, у нас получается следующая таблица.

7

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

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.

Функция ПОИСК() в EXCEL

history 12 апреля 2013 г.

Группы статей
• Подстановочные знаки (*, ?)
• Функции и Средства EXCEL учитывающие подстановочные знаки (*, ?)

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

ПОИСК ( искомый_текст ; просматриваемая_строка ;[нач_позиция])

Искомый_текст — текст, который требуется найти.

Просматриваемая_строка — текст, в которой ищется Искомый_текст .

Нач_позиция — позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент нач_позиция опущен, то предполагается значение 1.

В аргументе искомый_текст можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку; звездочка — любой последовательности знаков. Если нужно найти в тексте вопросительный знак или звездочку, следует поставить перед ними тильду (

Если искомый_текст не найден, возвращается значение ошибки #ЗНАЧ!

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

Примеры

Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал — лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке » Артикул #123# ID » последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция НАЙТИ() vs ПОИСК()

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

Связь с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР()

Функция ПОИСК() может быть использована совместно с функциями ЛЕВСИМВ() , ПРАВСИМВ() и ПСТР() .

Например, в ячейке А2 содержится фамилия и имя «Иванов Иван», то формула =ЛЕВСИМВ(A2;ПОИСК(СИМВОЛ(32);A2)-1) извлечет фамилию, а =ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(СИМВОЛ(32);A2)) — имя. Если между именем и фамилией содержится более одного пробела, то для работоспособности вышеупомянутых формул используйте функцию СЖПРОБЕЛЫ() .

Логические операции в Excel

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

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

Операторы сравнения

Каждое выражение содержит операторы сравнения. Они бывают следующими:

1. = – значение 1 равно значению 2.
2. > – значение 1 больше значения 2.
3. >= значение 1 или идентично значению 2, или больше него.
4. <> значение 1 или больше значения 2 или меньше него.

Как следствие, Excel выдает один из двух возможных результатов: истина (1) или ложь (2).

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

Функция «Истина»

Для использования этой функции не нужно указывать никаких аргументов, и она всегда возвращает «Истина» (что соответствует цифре 1 двоичной системы счисления).

Пример формулы – =ИСТИНА() .

Функция «Ложь»

Функция, полностью аналогичная предыдущей, только возвращаемый ею результат – «Ложь». Самая легкая формула, где можно использовать эту функцию, следующая =ЛОЖЬ().

Функция «И»

Задачей этой формулы является возвращение значения «Истина» при соответствии каждого из аргументов определенному значению или определенному критерию, которые описаны выше. Если вдруг возникает несоответствие одного из критериев требуемому, то возвращается значение «Ложь».

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

Синтаксис этой функции такой:

= И(Логическое значение1; [Логическое значение 2];…)

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

Функция «Или»

Проверяет несколько значений на предмет соответствия определенному критерию. Если какой-то из них соответствует, то функция возвращает истинное значение (1). Максимальное количество аргументов в данной ситуации также равняется 255, и необходимо обязательно указывать один параметр функции.

Если говорить про функцию ИЛИ , то в случае с ней таблица истинности будет следующей.

Синтаксис формулы следующий:

=ИЛИ(Логическое значение 1; [Логическое значение 2];…)

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

Функция «Не»

Ею возвращаются те значения, которые являются противоположными по отношению к изначально заданному. То есть, при передаче в качестве параметра функции значения «Истина», вернется «Ложь». Если же соответствия не обнаружено, то «Истина».

То, какой результат будет выдан, зависит от того, какой исходный аргумент принимается функцией. Если, например, применять функцию «И» вместе с функцией «НЕ», то таблица будет следующей.

При использовании же функции «Или» в сочетании с функцией «Не», то таблица обретет такой вид.

Синтаксис этой функции очень прост: =НЕ(принимаемое логическое значение).

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

Если говорить конкретно про эту функцию, то ее синтаксис будет несколько сложнее.

=ЕСЛИ(Логическое_выражение;[Значение_если_истина];[Значение_если_ложь])

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

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

Например, есть несколько кредитных карт, имеющих номера, начинающиеся первыми четырьмя цифрами, характеризующими платежную систему, обслуживающую карту. То есть, варианта два – Visa и Mastercard. Для проверки типа карты необходимо использовать такую формулу с применением двух вложенных ЕСЛИ .

=ЕСЛИ(ЛЕВСИМВ(A2;4)=”1111″; “Visa”;ЕСЛИ(ЛЕВСИМВ(A2;4)=”2222″;”Master Card”;”карта не определена”))

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

Аналогично можно добиться приличной вложенности и проверять содержимое ячейки или диапазона на предмет соответствия нескольким условиям.

Функция ЕСЛИОШИБКА

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

Эта формула имеет следующий синтаксис:

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

Как можно использовать функцию?

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

Как могут использоваться логические функции на практике

Задача 1

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

Изначально нужно сформировать такую таблицу.

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

Логическое выражение, содержащееся в первом аргументе функции, составлено с использованием операторов > и =. Простыми словами, изначально критерий следующий: при значении ячейки, большем или равном 8, выполняется формула, поставленная во втором аргументе. Выражаясь терминологическим языком, если первое условие оказывается истинным, то выполняется второй аргумент. Если же ложным – третий.

Сложность этой задачи может быть увеличена. Предположим, что перед нами стоит задача использовать логическую функцию И. В этом случае условие обретет следующий вид: в случае хранения продукции в течение большего, чем 8 месяцев срока, то его цену необходимо сбросить в два раза. Если же он находится на реализации больше 5 месяцев, то необходимо сбрасывать в 1,5 раза.

В этом случае необходимо в поле ввода формулы ввести такую строку.

=ЕСЛИ(И(C2>=8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))

Функция ЕСЛИ допускает использование текстовых строк в аргументах, если это требуется.

Задача 2

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

=ЕСЛИ(ИЛИ(D2 =10);”списан”;””)

Если при записи условия использовался логический оператор ИЛИ , то его необходимо расшифровывать следующим образом. Если в ячейке C2 находится число 10 или больше или если в ячейке D2 находится значение меньшее, чем 300, то необходимо выдать в соответствующей ячейке значение «списан».

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

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

Задача 3

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

Для начала необходимо построить следующую таблицу.

Наша задача – сравнить сумму всех оценок с проходным баллом, а кроме этого убедиться, чтобы оценка по математике была ниже 4. И в графе с результатом необходимо указать «принят» или «нет».

Нам необходимо ввести следующую формулу.

=ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);”принят”;”нет”)

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

Таким образом, с помощью функции ЕСЛИ можно решать множество различных задач, поэтому она является одной из наиболее часто встречаемых.

Задача 4

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

Для этого можно использовать ту же таблицу, которая была приведена выше.

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

В приведенном в первом аргументе выражении мы использовали функцию СРЗНАЧ , определяющую среднее арифметическое определенного набора данных. В нашем случае это диапазон D2:D7.

Задача 5

В этом случае, допустим, нам необходимо определить средние продажи. Для этого необходимо составить такую таблицу.

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

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

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

Функция СРЗНАЧЕСЛИ позволяет сравнить значение ячейки B9 с теми значениями, которые располагаются в диапазоне B2:B7, где перечисляются номера магазинов. Если данные совпадают, то формула подсчитывает среднее арифметическое диапазона C2:C7.

Выводы

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

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

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

Пример функция ПСТР для разделения текста на части в Excel

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

Примеры использования функции ПСТР в Excel

Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

Для заполнения номера дня используем следующую формулу (использовать в качестве формулы массива):

• A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
• 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
• 2 – номер последней позиции символа извлекаемой подстроки.

Аналогичным способом выделим номера месяца и годы для заполнения соответствующих столбцов с учетом, что номер месяца начинается с 4-го символа в каждой строке, а год – с 7-го. Используем следующие формулы:

Вид заполненной таблицы данных:

Таким образом нам удалось разрезать на части текст в ячейках столбца A. Удалось отдельно каждую дату разделить на несколько ячеек по столбцам: день, месяц и год.

Как вырезать часть текста ячейки в Excel?

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

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

Для заполнения столбца «Марка» используем следующую формулу массива:

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

В результате расчетов получим:

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

• текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
• начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
• число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

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

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

1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Функция ПСТР для разделения текста на части в Excel – пример использования

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

Функция ПСТР (MID) – подробное описание

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

Синтаксис предельно прост:

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

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

1. Текст. Содержимым ячейки могут выступать значения в разных форматах как ссылки, так и текстовая строка. Впрочем, может использоваться и ячейка любого другого формата. Например, можно таким способом достать месяц из даты. Для этого нужно в поле «Текст» указать ссылку на ячейку с датой. Важно убедиться, что она была предварительно конвертирована в текстовый формат.
2. Первоначальная позиция. Это числовое значение того символа, с какого начинается извлечение подстроки.
3. Число знаков. Это количество знаков, которые необходимо достать из строки. Если же нужно извлечь дату, то она всегда имеет размер в 10 символов.

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

Функция ПСТРБ (новая функция)

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

В чем разница между ПСТР и ПСТРБ?

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

Функция ПСТР на английском

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

Функция ПСТР и VBA в Excel

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

Range(“A2”).Value = Mid(Range(“A1”), 17, 10)

Распространенные ошибки в использовании функции ПСТР

При использовании функции ПСТР возможны ошибки, если неправильно соблюдать синтаксис или нарушить некоторые основополагающие правила:

1. Нельзя писать в качестве аргумента этой функции отрицательное значение. В таком случае будет выдана ошибка #ЗНАЧ.
2. Важно следить, чтобы начальная позиция не была большим числом, чем длина искомой строки. В ином случае будет выдано пустое значение.

Как вырезать часть текста ячейки в Эксель

А теперь давайте начнем обзор практических примеров, как возможно применение функции ПСТР .

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

Сама таблица выглядит следующим образом.

1

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

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

После этого получаем такой результат.

2

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

=ПСТР(A2:A8;НАЙТИ(” “;A2:A8)+1;100)

Здесь мы снова видим, что использовали функцию НАЙТИ , с помощью какой в этом примере ищем первоначальную позицию, содержащую пробел. Также мы добавили единицу к содержимому аргумента, чтобы перенести взор программы на первый символ марки товара. Чтобы упростить задачу поиска последнего символа мы просто решили написать число 100, которое гарантированно превышает длину строки.

Так можете делать и вы.

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

3

Как рассчитать возраст по дате рождения в Эксель

Теперь приведем еще один пример, с каким сотрудники, часто использующие Эксель, сталкиваются довольно часто. Перед нами есть база данных, содержащая три колонки: Фамилия, имя, отчество, а также дата рождения. И перед нами стоит задача определить, сколько лет человеку в данный момент, с использованием ПСТР.

Вот так выглядит таблица, с которой мы будем работать.

4

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

Эта функция сначала определяет ту часть строчки, которая содержится до символа пробела. Это делается с помощью функции НАЙТИ аналогично приведенным выше примерам. Далее с использованием функции РАЗНДАТ мы определяем количество полных лет. В нашем случае мы автоматически отсекаем тот участок дней, который остался до следующего дня рождения в этом году. Именно поэтому данная функция сможет указать всегда правильный возраст.

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

4

Несколько особенностей использования ПСТР в Excel

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

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

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

1. Текст. Это обязательный аргумент, который нужно передавать функции. Представляет собой или ссылку на ячейку, или непосредственно строку, из которой нужно извлекать требуемую информацию. Важно обратить внимание, что в последнем случае ее нужно облачать в кавычки. Независимо от формата строки, из нее будет доставаться определенная информация. Какая именно – задается следующими аргументами.
2. Начальная позиция. Этот аргумент также обязательный. Его задача – задать стартовую точку отсчета. Являет собой обязательно целое число, которое относится к положительным числам. То бишь, минимальное значение – 1. Если оказывается, что в аргумент была передана дробная часть, она отсекается.
3. Число знаков. И этот аргумент является обязательным. Таким образом, все аргументы, используемые в этой функции, необходимо использовать. Здесь есть такой нюанс. Если оказывается, что было в него передано число, которое больше длины строки, то возвращается вся строка.

Также важно понимать, что для функции ПТСРБ используется очень похожий синтаксис за тем лишь исключением, что вместо количества знаков после стартовой позиции задается количество байтов, начиная с этой точки.

Напоминаем, что синтаксис следующий:

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

Как говорится, повторение – мать учения. Поэтому давайте подведем небольшие промежуточные итоги:

1. Если на месте стартовой позиции задавать значение, которое по размеру больше исходной строки, то после всех операций, выполняемых функцией ПСТР вернется пустое значение.
2. Если применять единицу в качестве исходной позиции, а количество знаков указать такое, которое будет больше строки или равняться ей, то в качестве итога будет выведено все содержимое этой строки. Таким образом, можно использовать эту функцию в роли альтернативы, пусть и не такой удобной, ссылки на ячейку. В Excel ситуации бывают разные, поэтому иногда приходится выкручиваться из любой ситуации, в том числе, и такими причудливыми способами.
3. Будет возвращена ошибка #ЗНАЧ!, если использовать отрицательное значение в качестве начальной позиции. То же касается ситуации, если аргумент с числом знаков задается отрицательным значением. Важно запомнить навсегда, использовать нулевое или отрицательное значение в этом аргументе нельзя.

Примеры использования функции ПСТР

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

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

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

Сама таблица выглядит следующим образом:

5

В этом случае нужно применить формулу массива, а именно такую.

6

Какие аргументы использовались в этом случае?

1. А2:А10. Здесь перечисляется набор ячеек, представление дат в которых выполнено в текстовой форме. Из них и будет доставаться день.
2. 1 – это число, обозначающее первоначальную позицию, с которой будет осуществляться извлечение.
3. 2 – это последняя позиция.

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

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

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

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

В результате, у нас получается следующая таблица.

7

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

Таким образом, возможностей у функции ПСТР огромное количество. Ее можно использовать для обработки огромных массивов информации. А поскольку каждый год количество данных, которые нужно эффективно анализировать, постоянно увеличивается, необходимо искать качественные способы автоматизации. И хотя некоторые считают, что Эксель несколько устарел, в своей нише альтернатив этой программе нет. Хотя бы потому, что зная несколько формул, можно добиваться почти любого функционала электронных таблиц. Больших успехов вам и легкости в освоении этого ремесла.

Смотрите видео после статьи

видео может дополнять статью или содержать её полностью

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

Посмотрим применение функции ПСТР в Excel на практике, но прежде всего разберёмся с особенностями данной формулы.

Синтаксис ПСТР в Excel

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

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

• Текст
  Текстовая строка, часть которой требуется получить. Это может быть ссылка на ячейку (чаще всего), текстовая константа (имеет смысл только для обучения) или результат работы другой функции.
• Начальная позиция
  Считается слева и указывает функции ПСТР, откуда начинается нужный нам фрагмент строки (подстрока). Нумерация начинается с 1, а не с нуля!
• Число знаков
  Сколько символом нужно получить. Минимум 1. Если указать 0, то на выходе получим пустую строку (ничего).

Сама формула в обобщённом виде выглядит следующим образом:
ПСТР(текст; начальная_позиция; число_знаков)

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

На выходе вы получим подстроку или ошибку вида «#ЗНАЧ!», если какие-то параметры были заданы неверно. Например, позиция начала подстроки может быть указана с нуля или число символов в подстроке отрицательное. Про обработку ошибок мы писали ранее.

Можно запомнить следующие особенности функции ПСТР:

• Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).
• Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.
• Если значение «начальная_позиция» меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
• Если значение «число_знаков» отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
• Если значение «число_байтов» отрицательно, то функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

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

Как в Excel получить часть строки (подстроку) при помощи функции ПСТР

Лучше всего посмотрите видео и скачайте прикреплённый файл с примерами, чтобы была возможность потренироваться с получанием подстроки в Excel. Теория без практики мало чем поможет, потому что функций много и всего не запомнить.

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

Строка в ячейке B14: «1234567890»

Допустим, нам нужно получить цифру 3. В таком случае выражение будет выглядеть так: «=ПСТР(B14;3;1)». То есть мы указываем, что нужно взять один символ начиная с третьей позиции в тексте. В результате мы как раз и получим цифру 3. На практике этот подход можно использовать для получения различных знаков из предустановленного набора символов.

Остальные примеры, вполне типовые, смотрите в прикреплённом файле и на видео.

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

Если же Вы и так хорошо знаете программы, придумайте интересный пример получения подстроки при помощи функции ПСТР и опубликуйте его в комментариях в помощь менее опытным нашим читателям!