É muito comum este tipo de situação principalmente quando os dados são provenientes de importação, seja de qualquer origem, banco de dados, formulários da internet etc.
Caso na origem o nome e sobrenome não estão separados em campos distintos o Excel pode dar uma ajuda nesta tarefa, para isso devemos utilizar algumas funções de texto.
1.Devemos identificar onde começa e termina o nome, neste caso o primeiro espaço do nome completo e esse ponto. Ou seja, começa no “J” e termina no “ “ ( primeiro espaço em branco). E o sobrenome começa no primeiro caractere depois do primeiro espaço em branco, ou seja, no “C”.
2.Para aplicarmos em uma lista de nomes e não apenas em um único nome devemos utilizar algumas formulas.
Como não sabemos em que posição está o primeiro espaço em branco, vamos utilizar a função LOCALIZAR(texto_procurado, no_texto, [núm_inicial]), localiza um texto_procurado em um segundo no_texto e retornam o número da posição inicial da primeira cadeia de texto do primeiro caractere da segunda cadeia de texto. Ou seja, irá retornar a posição do primeiro espaço em branco.
=LOCALIZAR(” “;A2;1) o resultado será 5.
Com a posição do espaço em branco que é cinco, devemos agora extrair parte do texto que deve começar na posição 1 e terminar na posição 5, para isso vamos utilizar a função EXT.TEXTO(texto;núm_inicial;núm_caract), que retorna um número específico de caracteres da seqüência de caracteres texto, começando na posição núm_inicial, com base no número de caracteres núm_caract.. Com não queremos o espaço em branco no nome devemos colocar 5-1, quer ficaria assim:
=EXT.TEXTO(A2;1;LOCALIZAR(” “;A2;1)-1)
Agora para obter o sobrenome devemos utilizar a função EXT.TEXTO() e a função LOCALIZAR() porém alterando a posição núm_inicial;e núm_caract. Mas como os nomes da lista de vários nomes e cada um com um tamanho diferente devemos definir a quantidade de caracteres que tem cada nome depois extrair o número certo de caracteres. Para isso vamos usar a função NÚM.CARACT(texto) que retorna o número de caracteres em uma seqüência de caracteres de texto.
=NÚM.CARACT(A1)
Juntando as funções temos:
=EXT.TEXTO(A2;LOCALIZAR(” “;A2;1)+1;NÚM.CARACT(A2)-LOCALIZAR(” “;A2;1))
A2: O nome completo;
LOCALIZAR(” “;A2;1)+1: Posição inicial para começar a extrair, 5+1(espaço em branco)
NÚM.CARACT(A2): quantidade total de caracteres do nome completo,
LOCALIZAR(” “;A2;1): quantidade de caracteres a ser desconsiderada,(nome).
Fonte: http://www.senhorexcel.com.br/2009/08/como-eu-faco-para-separar-o-nome-e.html
