Как выполнить обратный поиск строк в Excel без использования VBA?

У меня есть таблица Excel, содержащая список строк. Каждая строка состоит из нескольких слов, но количество слов в каждой строке отличается.

Используя встроенные функции Excel (нет VBA), существует ли способ изолировать последнее слово в каждой строке?

Примеры:

  Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
                  Aziz! Light! -> Light!

Ответы

Ответ 1

Этот тест проверен и работает (на основе оригинального сообщения Brad):

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
     LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Если ваши исходные строки могут содержать трубку "|" символ, затем замените оба в приведенном выше символе другим символом, который не будет отображаться в вашем источнике. (Я подозреваю, что оригинал Брэда был сломан, потому что непечатный персонаж был удален в переводе).

Бонус: как это работает (справа налево):

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) – Количество пробелов в исходной строке
SUBSTITUTE(A1," ","|", ... ) – Заменяет только конечное пространство на |
FIND("|", ... ) – Находит абсолютное положение заменяемого | (это было конечное пространство)
Right(A1,LEN(A1) - ... )) – Возвращает все символы после этого |

EDIT:, чтобы учесть случай, когда исходный текст не содержит пробелов, добавьте следующее в начало формулы:

=IF(ISERROR(FIND(" ",A1)),A1, ... )

делает всю формулу сейчас:

=IF(ISERROR(FIND(" ",A1)),A1, RIGHT(A1,LEN(A1) - FIND("|",
    SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Или вы можете использовать синтаксис =IF(COUNTIF(A1,"* *") другой версии.

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

=IF(ISERROR(FIND(" ",B2)),B2, RIGHT(B2,LEN(B2) - FIND("|",
    SUBSTITUTE(B2," ","|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))))))

Ответ 2

Это техника, которую я использовал с большим успехом:

=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

Чтобы получить первое слово в строке, просто измените с ПРАВО на LEFT

=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

Кроме того, замените A1 ячейкой, содержащей текст.

Ответ 3

Более надежная версия ответа Джерри:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))

Это работает независимо от длины строки, ведущих или конечных пробелов или что-то еще, и все еще довольно короткое и простое.

Ответ 4

Я нашел этот в Google, протестирован в Excel 2003, и он работает для меня:

=IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)

[edit] У меня недостаточно комментариев для комментариев, поэтому это кажется лучшим местом... Ответ на BradC также не работает с конечными пробелами или пустыми ячейками...
[2nd edit] на самом деле, он не работает ни для одного слова...

Ответ 5

=RIGHT(A1,LEN(A1)-FIND("`*`",SUBSTITUTE(A1," ","`*`",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Ответ 6

Это очень чистый и компактный и хорошо работает.

{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}

Это не ошибка ловушки для пробелов или одного слова, но это легко добавить.

Edit:
Это обрабатывает конечные пробелы, одно слово и пустые ячеистые сценарии. Я не нашел способ сломать его.

{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}

Ответ 7

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(7),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(7),
LEN(TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1)," ",""))+1))+1)

Это очень надежное средство - оно работает для предложений без пробелов, ведущих/конечных пробелов, нескольких пробелов, нескольких ведущих/конечных пробелов... и я использовал char (7) для разделителя, а не для вертикальной полосы "|" на всякий случай, это желаемый текстовый элемент.

Ответ 8

Чтобы добавить к ответам Джерри и Джо, если вы хотите найти текст ПЕРЕД последним словом, которое вы можете использовать:

=TRIM(LEFT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))))-LEN(TRIM(A1))))

С "My little cat" в A1 вы получите "My little" (где Джо и Джерри будут давать "cat"

Точно так же, как Джерри и Джо изолируют последнее слово, это просто получает все слева от него (затем обрезает его обратно)

Ответ 9

Представьте, что строка может быть отменена. Тогда это действительно легко. Вместо работы над строкой:

"My little cat" (1)

вы работаете с

"tac elttil yM" (2)

С =LEFT(A1;FIND(" ";A1)-1) в A2 вы получаете "My" с (1) и "tac" с (2), который обращается "cat", последним словом в (1).

Есть несколько VBA вокруг, чтобы изменить строку. Я предпочитаю публичную функцию VBA ReverseString.

Установите вышеуказанное, как описано. Затем с вашей строкой в ​​A1, например, "My little cat", и эта функция в A2:

=ReverseString(LEFT(ReverseString(A1);IF(ISERROR(FIND(" ";A1));
  LEN(A1);(FIND(" ";ReverseString(A1))-1))))

вы увидите "cat" в A2.

В приведенном выше методе предполагается, что слова разделяются пробелами. Предложение IF предназначено для ячеек, содержащих отдельные слова = без пробелов в ячейке. Примечание: TRIM и CLEAN исходная строка также полезна. В принципе, он отменяет всю строку из A1 и просто находит первый пробел в инвертированной строке, которая находится рядом с последним (обратным) словом (т.е. "tac "). LEFT выбирает это слово, а другой разворот строки восстанавливает исходный порядок слова (" cat"). -1 в конце оператора FIND удаляет пробел.

Идея состоит в том, что легко извлечь первое (!) слово в строке с LEFT и FIND с первым пустым. Однако для последнего (!) Слова функция RIGHT является неправильным выбором, когда вы пытаетесь это сделать, потому что, к сожалению, FIND не имеет флага для направления, которое вы хотите проанализировать в своей строке.

Поэтому вся строка просто меняется на противоположную. LEFT и FIND работают нормально, но извлеченная строка отменяется. Но он не имеет большого значения, как только вы знаете, как изменить строку. Первый оператор ReverseString в формуле выполняет эту работу.

Ответ 10

=LEFT(A1,FIND(IF(
 ISERROR(
  FIND("_",A1)
 ),A1,RIGHT(A1,
  LEN(A1)-FIND("~",
   SUBSTITUTE(A1,"_","~",
    LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))
   )
  )
 )
),A1,1)-2)

Ответ 11

Я перевел на PT-BR, так как мне это тоже нужно.

(Обратите внимание, что я изменил пространство на \, потому что мне было нужно имя файла только для строк пути.)

=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|", SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))

Ответ 12

Другой способ достичь этого - ниже

=IF(ISERROR(TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14))))),TRIM(D14),TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14)))))

введите описание изображения здесь

Ответ 13

Скопируйте в столбец, выберите этот столбец и HOME > Редактирование > Найти и выбрать, Заменить:

Найти что:

Replace All.

После звездочки есть пробел.

Ответ 14

У меня также была такая задача, и когда я закончил, используя вышеописанный метод, мне потребовался новый метод: почему бы вам не сделать это:

  • Верните строку ( "строка одна" станет "eno gnirts" ).
  • Используйте старый добрый Find (который жестко запрограммирован для слева направо).
  • Снова верните его в читаемую строку.

Как это звучит?