Lost your password?


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

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

Получить слово до первого пробела достаточно просто:
=ПСТР(A1;1;НАЙТИ(" ";A1)-1)
=MID(A1,1,FIND(" ",A1)-1)

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

На мой взгляд, выглядит несколько закручено, хотя все не так уж сложно:

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

Но есть проблема - если второго пробела нет, то формула выдаст ошибку #ЗНАЧ!(#VALUE!). Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. А если надо не второе слово, а третье, пятое? Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*2);999);" ";"")
=SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(A1," ",REPT(" ",999)),1,999*2),999)," ","")

На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
- она не нуждается в проверке на отсутствие пробелов
- изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.

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

Чтобы получить 5-е слово - меняем на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*5);999);" ";"")
=SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(A1," ",REPT(" ",999)),1,999*5),999)," ","")

Т.е. число - это позиция слова(или слов) между пробелами. А что будет, если мы укажем число больше, чем есть пробелов в строке?
 
А это как раз ТО, К ЧЕМУ ШЛИ - СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг число будет больше, чем есть пробелов в строке - то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать, например, *999 - в большинстве случаев получим как раз последнее слово.

Как это работает:
для примера возьмем текст "мама мыла раму" и формулу по получению второго слова от начала:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));1;999*2);999);" ";"")
=SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(A1," ",REPT(" ",999)),1,999*2),999)," ","")

  • Сначала при помощи функции ПОДСТАВИТЬ(SUBSTITUTE) мы заменяем все пробелы в тексте на 999 пробелов(999 получаем при помощи функции ПОВТОР(REPT). Число может быть и меньше 999, но не должно быть меньше длины исходной строки. В итоге мы получим очень длинную строку, в которой каждое слово будет отделено от другого 999 пробелами. Что-то вроде такого(пробелов я поставил меньше, конечно):
    "мама____________________________мыла____________________________раму"
  • Далее при помощи функции ПСТР(MID) мы берем все слова от начала строки, до символа на позиции 999*2. Т.е. из текста выше мы получим слова "мама" и "мыла" и по 999 символов после каждого:
    "мама____________________________мыла____________________________"
  • Затем при помощи функции ПРАВСИМВ(RIGHT) получаем 999 символов справа от строки. Т.е. только наше слово и куча пробелов после него
    "мыла____________________________"
  • И напоследок та же функция ПОДСТАВИТЬ(SUBSTITUTE) убирает более не нужные нам пробелы, заменяя их все на пустую строку - "".

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

Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова именно по пробелам, то лучше дополнить еще одной функцией - СЖПРОБЕЛЫ(TRIM), чтобы отсечь лишние пробелы в начале и в конце строки и оставить только одиночные пробелы между словами:

  • слова от начала строки:
    =ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));1;999*1);999);" ";"")
    =SUBSTITUTE(RIGHT(MID(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",999)),1,999*1),999)," ","")
  • слова с конца строки:
    =ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));999*1);1;999);" ";"")
    =SUBSTITUTE(MID(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",999)),999*1),1,999)," ","")
  • "вытянуть" определенное количество слов:
    =СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));999*3);1;999*2))
    =TRIM(MID(RIGHT(" "&SUBSTITUTE(A1," ",REPT(" ",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);"/";"")
    В этой формуле в качестве разделителя используется слеш "/".


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

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

    Зная эти основные моменты достаточно будет в своем файле просто скопировать формулу выше и подставить в неё ссылку на нужную ячейку и указать требуемый разделитель.

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

      Слово после последнего пробела.xls (29,5 KiB, 4 015 скачиваний)


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

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

    Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
    Обсуждение: 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 Яндекс.Метрика
    © 2024 Excel для всех   Войти