Fórmula genérica
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Resumo
Se você precisar extrair o sobrenome de um nome completo, pode fazer isso com esta fórmula bastante complexa que usa várias funções. Na forma genérica da fórmula (acima), nome é um nome completo, com um espaço separando o primeiro nome de outras partes do nome.
No exemplo, a célula ativa contém esta fórmula:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Explicação
Basicamente, essa fórmula usa a função RIGHT para extrair caracteres começando da direita. As outras funções que constituem a parte complexa desta fórmula têm apenas uma função: calculam quantos caracteres precisam ser extraídos.
Em um nível superior, a fórmula substitui o último espaço no nome por um asterisco "*" e, em seguida, usa FIND para determinar a posição do asterisco no nome. A posição é usada para calcular quantos caracteres extrair com RIGHT.
Como a função substitui apenas o último espaço? Esta é a parte inteligente.
Apertem os cintos, a explicação fica um pouco técnica.
A chave para esta fórmula é esta parte:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
O que faz a substituição real do último espaço por "*".
SUBSTITUTE tem um quarto argumento (opcional) que especifica qual "instância" do texto de localização deve ser substituída. Se nada for fornecido para este argumento, todas as instâncias serão substituídas. No entanto, se, digamos, o número 2 for fornecido, apenas a segunda instância será substituída. No snippet acima, a instância é calculada usando o segundo SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Aqui, o comprimento do nome sem espaços é subtraído do comprimento real do nome. Se houver apenas um espaço no nome, ele produzirá 1. Se houver dois espaços, o resultado será 2 e assim por diante.
No nome de exemplo em B4, há dois espaços no nome, então obtemos:
15 - 13 = 2
E dois é usado como o número da instância:
SUBSTITUTE(B4," ","*",2)
que substitui o segundo espaço com "*". O nome fica assim:
"Susan Ann * Chang"
A função FIND então assume o controle para descobrir onde o "*" está no nome:
FIND("*", "Susan Ann*Chang")
O resultado é 10 (o * está na 10ª posição), que é subtraído do comprimento total do nome:
LEN(B4)-10
Como o nome tem 15 caracteres, temos:
15-10 = 5
O número 5 é usado por RIGHT assim:
=RIGHT(B4,5)
O que resulta em "Chang"
Como você pode ver, é muito trabalhoso calcular aquele 5 simples!
Tratamento de espaços inconsistentes
Espaços extras causarão problemas com esta fórmula. Uma solução é usar a função TRIM primeiro para limpar as coisas e, em seguida, usar a fórmula de análise.
