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

Получить слово до первого пробела достаточно просто:
=ПСТР(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 КиБ, 4 216 скачиваний)