Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Как получить слово после последнего пробела

Получить слово до первого пробела достаточно просто:
=ПСТР(A1;1;НАЙТИ(" ";A1)-1)
Но куда чаще сложности возникают с получением слова(символа), находящегося на определенной позиции между пробелом. Я беру в качестве примера пробел, но на самом деле это может быть абсолютно любой символ. Например, для получения второго слова(т.е. слова между первым пробелом и третьим), можно составить такую формулу:
=ПСТР(A1;НАЙТИ(" ";A1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1)+1)-НАЙТИ(" ";A1)-1)
На мой взгляд, выглядит несколько закручено, хотя все предельно просто:

  • НАЙТИ(" ";A1)+1 - ищем позицию первого пробела;
  • НАЙТИ(" ";A1;НАЙТИ(" ";A1)+1) - ищем позицию второго пробела и затем из этой позиции вычитаем позицию первого пробела(-НАЙТИ(" ";A1)).

Но есть проблема - если второго пробела нет, то формула выдаст ошибку #ЗНАЧ!. Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*2);999);" ";"")
На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
она не нуждается в проверке на отсутствие пробелов;
изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.
Разберем самое главное: чтобы получить первое слово от начала строки, нужно в блоке 999*2 заменить 2 на 1:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*1);999);" ";"")
Чтобы получить 5-е - на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*5);999);" ";"")
И ТО, К ЧЕМУ ШЛИ - СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг пробелов будет меньше, чем указанное число - то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать *999 - в большинстве случаев получим как раз последнее слово.
Как это работает: при помощи функции ПОДСТАВИТЬ мы заменяем все пробелы в тексте на 999 пробелов(число может быть меньше 999, но не должно быть меньше длины исходной строки). Далее при помощи функции ПСТР мы выдергиваем первые 999 символов, помноженные на число, обозначающее необходимое нам слово(999*1 - первое). По сути только то количество слов, которое указано(в данном случае одно - 999*1). Затем функция ПРАВСИМВ возвращает нам только последнее слово - т.е. нужное нам. А далее та же ПОДСТАВИТЬ убирает лишние теперь пробелы, заменяя их все на пустую строку - "".
Вроде бы достигли того, что нам нужно было. Но вдруг необходимо получить второе слово с конца строки? Как тогда быть? Считать пробелы? А если у нас их штук 50 хотя бы? Можно использовать некую модификацию приведенной выше формулы, но которая как раз возвращает слово с конца строки:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));999*1);1;999);" ";"")
Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова по пробелам, то лучше дополнить еще одной функцией - СЖПРОБЕЛЫ:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));1;999*1);999);" ";"")
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));999*1);1;999);" ";"")

Так же можно "вытянуть" определенное количество слов:
=СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));999*3);1;999*2))
3 — третье слово с конца строки.
2 — количество слов.

Остается еще добавить, что вместо пробелов могут быть и другие символы. Например, очень часто встречается ситуация, когда надо из текста получить не одно слово в конкретной позиции, а конкретную строку из текста, разнесенного в одной ячейке на строки:
Разбиение на строки
Тогда для получения второй строки(ТЦ Таганка и ТЦ Опус) можно применить такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(СИМВОЛ(10)&ПОДСТАВИТЬ(C2;СИМВОЛ(10);ПОВТОР(СИМВОЛ(10);999));1;999*2);999);СИМВОЛ(10);"")
СИМВОЛ(10) здесь означает перенос строки. Обычно эти переносы делаются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужное место строки и нажимаем Alt+Enter.
я для получения месяцев(Август 2015 г. и Сентябрь 2015 г.) - такую:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ("/"&ПОДСТАВИТЬ(C2;"/";ПОВТОР("/";999));999*1);1;999);"/";"")
Если лень прописывать внутри формулы один символ несколько раз, его можно записать в ячейку(скажем, G1) и в формуле указать ссылку на эту ячейку:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(G1&ПОДСТАВИТЬ(C2;G1;ПОВТОР(G1;999));999*1);1;999);G1;"")
Теперь для изменения символа надо будет изменить его один раз в ячейке и формула "вытащит" нужное слово/строку, опираясь именно на этот символ.

Скачать пример:

  Tips_All_WordFromEndString.xls (29,5 KiB, 1 981 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 10 комментариев
  1. Bладимир:

    дмитрий извените за дополнение. Просто всплывал такой вопрос вот и решил добавить к вашему уроку.
    =СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ($A8;" ";ПОВТОР(" ";999));999*3);1;999*2)) здесь 3 - третье слово с конца, 2 - к-во слов

  2. Владимир, спасибо, добавлю в статью.

  3. Ольга:

    Большое человеческое спасибище! Сэкономили кучу времени :)

  4. Юрий:

    Здравствуйте, может сможете мне помочь.
    необходимо из предложения переносить только последнее слово, при условии что перед ним стоит символ "-"
    К примеру "Шла Саша по шоссе и -сломала -ногу" перенести в ячейку слово "ногу", если "-" нет то ячейка должна оставаться пустая.

    И возможно ли перенести в этом примере 2 слово с конца при наличии перед ним "-"

    Заранее вам благодарен за ответ.

  5. Юрий:

    Вот примерно так в идеале должно получиться
    Шла Саша по шоссе и -сломала -ногу Шла Саша по шоссе и
    ногу
    сломала

    Шла Саша по шоссе и сломала -ногу Шла Саша по шоссе и сломала
    ногу

    Шла Саша по шоссе и сломала ногу Шла Саша по шоссе и сломала ногу

  6. Юрий:

    Блин криво получилось

  7. Как это не парадоксально, но в статье нет формулы, которая отвечает на вопрос заголовка статьи: "Как получить слово после последнего пробела?"
    для тех, кто слабо разбирается в формулах будет затруднительно разобраться и додуматься, что нужно одну из формул преобразовать для этого
    возможно, автору следует привести эту формулу и выделить, например, полужирным, чтобы она сразу бросалась в глаза

    • Роман:

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

      • Ну вообще, если читать не через строку, то ответ приведен:
        Чтобы получить 5-е - на 5:
        =ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*5);999);" ";"")
        Если вдруг пробелов будет меньше, чем указанное число - то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать *999 - в большинстве случаев получим как раз последнее слово.

        Т.е. если указать
        =ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*999);999);" ";"")
        то последнее слово точно получим.
        А ниже еще одна формула:
        =ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));999*1);1;999);" ";"")
        она только и делает, что возвращает последнее слово. Об этом тоже написано. Так что Роман - ответы все есть. Но я учту и постараюсь описать более доступно.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти