Вы определенно находитесь на правильном пути с функциями RIGHT() и SUBSTITUTE() .
Давайте пройдемся по процессу обобщения и объединения формул.
Для этого мы будем работать с предложением What to do? в A1 ваша RIGHT() формула в B1 , и ваша SUBSTITUTION() формулы в C1 . Таким образом, ваши формулы будут =RIGHT(A1,3) и =SUBSTITUTE(B1,"?",""):
Шаг 1
Нам нужно обобщить:
<1> RIGHT(A1,3)
3 , который является длиной последнего слова, включая любую следующую пунктуацию, должен быть изменен для работы с любым предложением. Один из способов сделать это состоит в том, чтобы понять, что длина последнего слова может быть вычислена из длины предложения и основанного на единице индекса последнего пробела.
Для нашего примера длина равна 11, а индекс последнего пробела равен 8,
What to do?
↑ ↑
12345678 11
и 11 - 8 → 3
Длина может быть рассчитана с помощью:
<2> LEN(A1)
Индекс немного сложнее, поскольку FIND() может использоваться только для поиска индекса первого пробела, но не любого другого. Тем не менее, мы можем использовать SUBSTITUTE() , чтобы помочь , как это позволяет заменить любое конкретное пространство. Если мы изменим последний пробел на символ, которого нет в другом месте предложения, мы могли бы затем использовать FIND() чтобы найти его, и, следовательно, индекс.
Формула для замены:
<3> SUBSTITUTE(A1," ","§",2)
где 2 - номер вхождения последнего пробела, то есть 2-го пробела, и, надеюсь, § не существует.
Затем мы можем использовать <3> чтобы найти индекс последнего пробела следующим образом:
<4> FIND("§",SUBSTITUTE(A1," ","§",2))
Но как мы решаем 2? Это немного сложно. Обратите внимание, что номер вхождения последнего пробела в предложении совпадает с количеством пробелов в предложении.
Количество может быть рассчитано путем удаления пробелов из предложения. Это уменьшает его длину на счет. Из этой и исходной длины мы можем получить количество:
<5> LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Включение этого обратно в <4> приводит к формуле для индекса последнего пробела:
<6> FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
Вычитание этого из <2> приводит к длине последнего слова:
<7> LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
Подключение <7> обратно к <1> приводит к обобщенному
<8> RIGHT(A1,LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Шаг 2
Нам нужно обобщить:
<9> SUBSTITUTE(B1,"?","")
Теперь, так как SUBSTITUTE() возвращает неизмененную целевую строку, если в ней не может быть найдена строка поиска, мы можем "связать" более одной этой функции, чтобы удалить несколько возможных знаков пунктуации. Например, чтобы также удалить ! мы бы сделали это:
<10> SUBSTITUTE(<9>,"!","")
Подключение <9> к <10> дает:
<11> SUBSTITUTE(SUBSTITUTE(B1,"?",""),"!","")
Поскольку есть только три знаки препинания в конце ? ! и . (Я надеюсь), <10> можно полностью обобщить на это:
<12> SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"?",""),"!",""),".","")
Шаг 3
Объединение двух формул - это простой вопрос замены B1 в <12> на <8> , что приводит к решению:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"?",""),"!",""),".","")
Заметки:
Если в конце предложения нет знаков препинания (как в случае предложения 21, показанного на скриншоте), функции SUBSTITUTE() фактически ничего не делают, и последнее слово корректно возвращается.
Если предложения в вашем наборе данных могут оканчиваться каким-либо другим символом, скажем, как : формула может быть легко изменена для удовлетворения этого.
Наконец, поскольку между номером предложения и первым словом всегда есть пробел, нет необходимости проверять ошибки в формуле. Однако она возвращает ошибку , если нет приговора.
