ПСТР в Excel (функция получения подстроки)

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Excel выбор функция

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

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

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

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

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

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

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

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

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

Глядеть видео
ПСТР в Excel (функция получения подстроки)

Прикреплённые документы

Вы сможете просмотреть хоть какой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, потому для закрытия документа пожалуйста не используйте клавишу «Вспять» браузера.

Справка по функции ПСТР в Excel.pdf

Файлы для загрузки

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

Формула пстр в excel

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

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

Функция ПСТР() vs ПРАВСИМВ() и ЛЕВСИМВ()

Пусть в ячейке А2 введена строчка 1-ый канал – наилучший.

Формула =ПСТР(A2;8;5) , извлекающая из строчки слово канал, стопроцентно эквивалентна формуле =ЛЕВСИМВ(ПРАВСИМВ(A2;ДЛСТР(A2)-8+1);5) . Где число 8 – это позиция первого знака, извлекаемого из строчки, а 5 – число извлекаемых символов.

Интересно почитать: Функция match в excel на русском

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

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

Функция ПСТР() vs ПРАВСИМВ() и ЛЕВСИМВ()

Пусть в ячейке А2 введена строчка 1-ый канал – наилучший.

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Excel строковые функции

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

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

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

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

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

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

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

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

Вид начальной таблицы:

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

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

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

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

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

Синтаксис

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

Последующий неотклонимый аргумент «исходная позиция» — это позиция первого знака, извлекаемого из текста. Исходная позиция – «1» .

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

Броско, что функция ПСТР создана для языков с однобайтовой шифровкой, а ПСТРБ — для языков с двухбайтовой шифровкой.

Языки, которые поддерживают двухбайтовую шифровку: японский, китайский и корейский.

Пример

Кодом телефонного узла служит набор чисел, который размещен по середине значения ячейки J2.

Вводим функцию ПСТР .

Нас интересует текст в ячейке J2, исходной позицией будет 5 (беря во внимание дефис), количество извлекаемых символов – 3.

Заключение

Как видно, функция ПСТР ординарна в применении.

В последующей статье разберем еще одну текстовую функцию Excel: ДЛСТР .

К слову, получите безвозмездно мое PDF управление по 6 самым всераспространенным ошибкам в формуле Excel, с которыми отчаянно борются почти все!

Дарю 7-ми дневное обучение (педагогический процесс, в результате которого учащиеся под руководством учителя овладевают знаниями, умениями и навыками) в моей академии!

Если для вас приглянулась данная статья, то непременно поделитесь ею и подпишитесь!