Fórmula genérica
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Resumo
Para obter a última palavra de uma string de texto, você pode usar uma fórmula baseada nas funções TRIM, SUBSTITUTE, RIGHT e REPT.
No exemplo mostrado, a fórmula em C5 é:
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Explicação
Essa fórmula tira vantagem do fato de que TRIM removerá qualquer número de espaços à esquerda. Procuramos por quebras de linha e "inundamos" o texto com espaços onde encontramos uma. Então, voltamos e pegamos o texto da direita.
Trabalhando de dentro para fora, usamos a função SUBSTITUTE para encontrar todas as quebras de linha (caractere 10) no texto e substituir cada uma por 200 espaços:
SUBSTITUTE(B5,CHAR(10),REPT(" ",200))
Após a substituição, fica assim (com hífens marcando espaços para facilitar a leitura):
line one----------line two----------line three
Com 200 espaços entre cada linha de texto.
A seguir, a função RIGHT extrai 200 caracteres, começando da direita. O resultado será assim:
-------line three
Finalmente, a função TRIM remove todos os espaços à esquerda e retorna a última linha.
Nota: 200 é um número arbitrário que representa a linha mais longa que você espera encontrar em uma célula. Se você tiver linhas mais longas, aumente esse número conforme necessário.
Versão Mac
O Mac Excel usa um caractere de quebra de linha diferente dentro das células, caractere 13, então use esta versão da fórmula:
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(13),REPT(" ",200)),200))
