Você está definitivamente no caminho certo com as funções RIGHT() e SUBSTITUTE() .
Vamos percorrer o processo de generalização e combinação das fórmulas.
Para isso, trabalharemos com a frase What to do? in A1 , sua fórmula RIGHT() em B1 e sua fórmula SUBSTITUTION() em C1 . Assim, suas fórmulas seriam =RIGHT(A1,3) e =SUBSTITUTE(B1,"?","") :
Etapa1
Precisamosgeneralizar:
<1> RIGHT(A1,3)
O 3 , que é a duração da última palavra, incluindo qualquer pontuação seguinte, precisa ser modificado para funcionar com qualquer frase. Uma maneira de fazer isso é perceber que o comprimento da última palavra pode ser calculado a partir do tamanho da frase e do índice baseado em um do último espaço.
Para o nosso exemplo, o tamanho é 11 e o índice do último espaço é 8,
What to do?
↑ ↑
12345678 11
e 11 - 8 → 3
O comprimento pode ser calculado com:
<2> LEN(A1)
O índice é um pouco mais complicado, pois FIND() só pode ser usado para encontrar o índice do primeiro espaço, não outro qualquer. No entanto, podemos usar SUBSTITUTE() para ajudar, pois faz nos permitir substituir qualquer espaço específico. Se mudarmos o último espaço para um caractere que não existe em outro lugar na sentença, poderíamos então usar FIND() para encontrá-lo e, assim, o índice.
A fórmula para fazer a substituição é
<3> SUBSTITUTE(A1," ","§",2)
em que 2 é o número da ocorrência do último espaço, ou seja, o segundo espaço, e esperamos que § não exista.
Podemos então usar <3> para encontrar o índice do último espaço assim:
<4> FIND("§",SUBSTITUTE(A1," ","§",2))
Mas como resolvemos o 2 ? Isso é um pouco complicado. Observe que o número de ocorrência do último espaço na sentença é o mesmo que o contagem dos espaços na sentença.
A contagem pode ser calculada removendo os espaços da sentença. Isso reduz seu comprimento pela contagem. Deste e do tamanho original, podemos obter a contagem:
<5> LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Conectar isso de volta a <4> leva à fórmula do índice do último espaço:
<6> FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
Subtrair isso de <2> leva à duração da última palavra:
<7> LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
Conectar <7> de volta a <1> leva ao generalizado
<8> RIGHT(A1,LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Etapa 2
Precisamos generalizar:
<9> SUBSTITUTE(B1,"?","")
Agora, como SUBSTITUTE() retorna a string de destino não modificada, se a string de pesquisa não puder ser encontrada, podemos "encadear" mais de uma função para remover vários caracteres de pontuação possíveis. Por exemplo, para também remover um ! , faríamos isso:
<10> SUBSTITUTE(<9>,"!","")
Conectando <9> em <10> dá:
<11> SUBSTITUTE(SUBSTITUTE(B1,"?",""),"!","")
Como há apenas três caracteres de pontuação no final de uma frase, ? , ! e . (espero), <10> pode ser totalmente generalizado para isso:
<12> SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"?",""),"!",""),".","")
Etapa 3
Combinar as duas fórmulas é uma questão simples de substituir o B1 em <12> por <8> , o que leva à solução:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"?",""),"!",""),".","")
Notas:
Se não houver pontuação no final da frase (que é o caso da frase 21 vista na captura de tela), as funções SUBSTITUTE() não farão nada e a última palavra será retornada corretamente.
Se as frases em seu conjunto de dados puderem terminar em outro caractere, digamos, como : , a fórmula poderá ser facilmente modificada para atender a isso.
Por fim, como há sempre um espaço entre o número da sentença e a primeira palavra, não há necessidade de verificação de erros na fórmula. No entanto, irá retornar um erro se não houver uma sentença.
