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

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

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

Для удобства поиска нужного материала у нас на канале есть удобный рубрикатор, а все видео предыдущих уроков также доступны на youtube (ссылка внизу поста и в описании канала)

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

⏩ Извлечение определенного количества символов из текста

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

=ПСТР(A2;1;5) где 5 - количество символов, которые нужно извлечь

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

⏩ Извлечение данных из крайней правой позицией

В данном примере ПСТР() используется для извлечения подстроки из ячейки A2 начиная с первого пробела и до конца строки

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

=ПСТР(A2;НАЙТИ(" ";A2)+1;ДЛСТР(A2)), где - исходный текст (A2), - начальная позиция (НАЙТИ(" ";A2)+1) - количество символов (ДЛСТР(A2)).

🔔 Формула имеет место если позиция находится справа, а текстовая строка содержит только один пробел

⏩ Извлечение имени файла из пути

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

Например, есть путь "C:\Documents\Test.xlsx"

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

=ПСТР(A2;НАЙТИ("*";ПОДСТАВИТЬ(A2;"\";"*";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"\";""))))+1;ДЛСТР(A2))

• ПОДСТАВИТЬ(A2;"\";"*";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"\";""))) заменяет последний символ обратного слеша в строке A2 на символ "*", чтобы пометить начало имени файла.
• НАЙТИ("*";ПОДСТАВИТЬ(A2;"\";"*";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"\";""))))+1 находит позицию символа "*", обозначающего начало имени файла.

Альтернативное решение:

=СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A2;"\";ПОВТОР(" ";100));99)) Формула позволяет извлечь имя файла из пути, предварительно удалив лишние пробелы, которые могут возникнуть при замене символов в пути.

• ПОДСТАВИТЬ(A2;"";ПОВТОР(" ";100)) заменяет каждый символ "" в пути к файлу на 100 пробелов. Это делается для создания отступа пробелов перед именем файла.
• ПРАВСИМВ() применяется к результату предыдущего шага. Возвращает правую часть строки, содержащую имя файла и пробелы, созданные на предыдущем шаге.
• С помощью параметра 99, выбирается последний символ перед именем файла.
• СЖПРОБЕЛЫ() удаляет лишние пробелы из полученной строки. Это позволяет получить чистое имя файла без пробелов.

⏩ Извлечение даты из текстовой строки

Например, требуется извлечь дату из текстовой строки формата: "Заказ №12345 от 01.05.2023"

Пример решения:

=ПСТР(A2;НАЙТИ("от";A2)+3;10) Формула используется для извлечения подстроки из ячейки A2, начиная с позиции, следующей сразу после первого вхождения текста "от" в ячейке, и имеющей длину 10 символов.

• НАЙТИ("от";A2) находит позицию первого вхождения текста "от" в содержимом ячейки A2. Эта функция возвращает числовое значение, представляющее позицию первого символа "от" в строке.
• к полученному значению добавляется число 3, чтобы перейти к позиции следующей за текстом "от". Таким образом, получаем начальную позицию для извлечения подстроки.
• ПСТР(A2;НАЙТИ("от";A2)+3;10) применяется к ячейке A2 и указывает на то, что нужно извлечь подстроку, начиная с позиции, определенной на предыдущем шаге, и имеющую длину 10 символов.

⏩ Извлечение первого слова

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

=ПСТР(A2;1;НАЙТИ(" ";A2)-1) Данная формула позволяет извлечь подстроку из ячейки A1, начиная с первого символа и заканчивая позицией перед первым пробелом

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

• НАЙТИ(" ";A1) находит позицию первого пробела в содержимом ячейки A1. Это число представляет позицию первого пробела в строке.
• от полученного значения вычитается 1, чтобы определить позицию перед первым пробелом. Это нужно для того, чтобы извлечь подстроку до этой позиции.

⏩ Извлечение числа

(двойное извлечение подстроки из исходной строки)

Пример решения:

=ПСТР(ПСТР(A2&"_0";МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"_0123456789"));15);1;НАЙТИ(" ";ПСТР(A2&"_0";МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A2&"_0123456789"));15))-1)

🔘 Механизм работы функции:

Внутренняя функция

Преобразует исходную строку A2 путем добавления "_0" в конец исходной строки и затем извлекает подстроку начиная с первого вхождения любой цифры из {0;1;2;3;4;5;6;7;8;9} в эту измененную строку. Результат этой для данного примера будет выглядеть как "150015 шт._0".

Внешняя функция

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

А какие примеры использования ПСТР() знаете вы? Поделитесь в комментариях 😉

Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel

Если вы нашли предоставленный материал полезным, будем благодарны за вашу поддержку