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. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Можно сделать интереснее, если нужно выделить подстроки в виде октетов из IPv4-адрес (например, можно встретить формат 090.088.112.223). Будем применять формулу =ПОДСТАВИТЬ(), которая имеет 4-ый необязательный параметр для замены вхождения по счёту (используем как счётчик).

Для этого нужно отделить сначала правую часть, и превратить её в число. Для первого октета формула будет сложнее, чем =ЛЕВБ() (чтобы можно было отследить этапы замены), где 1 — счётчик вхождений точки:

=ЗНАЧЕН(ЛЕВБ(ПОДСТАВИТЬ(A1;".";ПОВТОР(" ";15);1);15))

Для выбора остальных октетов нужно будет ещё отбросить и левую часть. Теперь первый счётчик имеет значение вхождения точки 2 (сначала нужно отсечь правую часть за 2-ой точкой), а второй счётчик имеет значение точки 1 (затем нужно отсечь левую часть до 1-ой точки):

=ЗНАЧЕН(ПРАВБ(ПОДСТАВИТЬ(
  СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(A1;".";ПОВТОР(" ";15);2);15));
".";ПОВТОР(" ";15);1);3))

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

Задача схожа с «Excel — как вытащить из относительной ссылки содержимое между двух последних «/»?».

Работа с текстовыми строками является важной составляющей обработки информации с помощью 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. Когда же может понадобиться умение решать именно такую задачу? Прежде всего, когда у нас есть набор символов, содержащийся в одной строке, и нам нужно брать оттуда определенные знаки.