Взять символы из ячейки Excel по позиции можно функцией =ПСТР(A2;5;5): она вернёт 5 символов, начиная с пятого. Чтобы взять всё с N-го символа до конца строки, укажите количество с запасом: =ПСТР(A2;5;ДЛСТР(A2)). Символы с начала строки берёт ЛЕВСИМВ, с конца — ПРАВСИМВ, а извлечь символы по позиции сразу во всём диапазоне без формул можно процедурами !SEMTools.
Взять символы из ячейки по их позиции - одна из насущных задач. Часто нужные отрезки текста находятся в конце или начале ячейки, или же это может быть их определенное количество с определенной позиции - например, с четвертого символа по десятый, или с пятого и до конца строки.
Распишу подробно, как решать такие задачи.
Извлечь символы по их позиции в начале ячейки
Эта тема довольно интересная и поэтому я ей посвятил отдельную статью которую можно посмотреть по ссылке: извлечь первые N символов ячейки Excel.
Извлечь символы по их позиции в конце ячейки
Этой теме я также посвятил отдельную статью, поскольку можно извлекать символы в конце не только просто по их количеству, но и по различным условиям. Смотрите подробную информацию здесь: извлечь последние N символов из ячеек Excel.
Пример ниже демонстрирует режим, при котором обрабатываются 2 несвязанных диапазона сразу, и результат выводится в соседние с ними столбцы. Подробнее о режимах работы можно почитать в статье: Как пользоваться !SEMTools.
Взять N символов по их позиции в середине ячейки
Это та самая ситуация, когда вы знаете, какую по порядку позицию занимает символ в строке и сколько символов нужно взять от этой позиции. Для того чтобы решить эту задачу лучше всего подойдёт функция ПСТР, читайте соответствующую статью по ссылке.
Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков). Чтобы взять символы «с 4-го по 10-й», количество считается как 10 − 4 + 1 = 7:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Код | С 5-го, 5 символов | С 5-го до конца | С 4-го по 10-й |
| 2 | АБВ-12345-RU | 12345=ПСТР(A2;5;5) | 12345-RU=ПСТР(A2;5;ДЛСТР(A2)) | -12345-=ПСТР(A2;4;7) |
| 3 | ЯКУ-00077-KZ | 00077=ПСТР(A3;5;5) | 00077-KZ=ПСТР(A3;5;ДЛСТР(A3)) | -00077-=ПСТР(A3;4;7) |
Если количество знаков больше, чем осталось до конца строки, ПСТР просто вернёт остаток — поэтому ДЛСТР(A2) как «количество с запасом» безопасна. Результат всегда текст: «00077» сохранит ведущие нули.
В английском Excel: =MID(A2,5,5)
Собрать символы из разных позиций
Части строки можно склеить в нужном порядке амперсандом: =ПСТР(A2;5;5)&"-"&ЛЕВСИМВ(A2;3) превратит «АБВ-12345-RU» в «12345-АБВ». Если позиция заранее неизвестна и зависит от разделителя, найдите её функцией ПОИСК: =ПСТР(A2;ПОИСК("-";A2)+1;5).
А так это можно сделать с !SEMTools, можно просто удалить первые 10 символов ячейки:
Почему ПСТР возвращает не то
- Результат сдвинут на символ. В начале ячейки пробел или невидимый символ. Используйте ПСТР(СЖПРОБЕЛЫ(A2);5;5).
- Ошибка #ЗНАЧ!. Начальная позиция меньше 1 или количество знаков отрицательное — чаще всего из-за ПОИСК, который не нашёл разделитель. Оберните формулу в ЕСЛИОШИБКА.
- Из числа извлекаются не те цифры. Отображаемый формат (например, «000123») не меняет значение 123. Используйте ПСТР(ТЕКСТ(A2;"000000");…).
- Извлечённые цифры не суммируются. ПСТР возвращает текст. Добавьте два минуса: =--ПСТР(A2;5;5).
Символы по позиции — частые вопросы
Как взять символы с определённой позиции?
Функцией =ПСТР(A1;позиция;количество), например =ПСТР(A1;5;3) — три символа с пятого.
Как взять всё после N-го символа?
=ПСТР(A1;N+1;ДЛСТР(A1)) или удалите первые N символов: =ПРАВСИМВ(A1;ДЛСТР(A1)-N).
Как взять символы с 4-го по 10-й?
=ПСТР(A1;4;7) — количество = 10 − 4 + 1.
Как взять символ, позиция которого заранее неизвестна?
Найдите позицию функцией ПОИСК или НАЙТИ и подставьте её в ПСТР: =ПСТР(A1;ПОИСК("-";A1)+1;5).
Извлечь символы по позиции — с начала, с конца или из середины строки — сразу во всех выделенных ячейках, с выводом в соседний столбец или на месте, можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как взять символы по позиции в Excel
| Задача | Формула |
|---|---|
| Первые N символов | =ЛЕВСИМВ(A1;N) |
| Последние N символов | =ПРАВСИМВ(A1;N) |
| N символов с позиции P | =ПСТР(A1;P;N) |
| Всё с позиции P до конца | =ПСТР(A1;P;ДЛСТР(A1)) |
| Позиция по разделителю | =ПСТР(A1;ПОИСК("-";A1)+1;N) |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: удалить первые и последние символы и удалить текст до символа.