Separe o nome e o sobrenome no Excel (divida os nomes usando fórmulas)

Assistir ao vídeo - Divida os nomes no Excel (em nome, nome do meio e sobrenome)

O Excel é uma ferramenta incrível quando se trata de fatiar e cortar dados de texto.

Existem muitas fórmulas úteis, bem como funcionalidades que você pode usar para trabalhar com dados de texto no Excel.

Uma das perguntas mais comuns que recebo sobre a manipulação de dados de texto é - “Como separar nomes e sobrenomes (ou nomes, nomes do meio e sobrenomes) no Excel?“.

Existem algumas maneiras fáceis de dividir nomes no Excel. O método escolhido dependerá de como seus dados estão estruturados e se você deseja que o resultado seja estático ou dinâmico.

Então, vamos começar e ver maneiras diferentes de dividir nomes no Excel.

Dividir Nomes Usando Texto em Colunas

A funcionalidade Texto para colunas no Excel permite que você divida rapidamente os valores de texto em células separadas em uma linha.

Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja separar o nome e o sobrenome e obtê-los em células separadas.

Abaixo estão as etapas para separar o nome e o sobrenome usando Texto para colunas:

1. Selecione todos os nomes na coluna (A2: A10 neste exemplo)
2. Clique na guia ‘Dados’
3. No grupo ‘Ferramentas de dados’, clique na opção ‘Texto para colunas’.
4. Faça as seguintes alterações no Assistente de conversão de texto em coluna:
   1. Etapa 1 de 3: Selecione Delimitado (isso permite que você use o espaço como separador) e clique em Avançar
   2. Etapa 2 de 3: Selecione a opção Espaço e clique em Avançar
   3. Etapa 3 de 3: defina B2 como a célula de destino (caso contrário, substituirá os dados existentes)
5. Clique em Terminar

As etapas acima dividiriam instantaneamente os nomes em nome e sobrenome (com os nomes na coluna B e o sobrenome na coluna C).

Nota: No caso de já existir algum dado nas células (aquelas onde a saída de Texto para Colunas é esperada), o Excel mostrará um aviso avisando que já existem alguns dados nas células. Você pode optar por substituir os dados ou cancelar o Texto nas colunas e removê-lo manualmente primeiro.

Uma vez feito isso, você pode excluir os dados do nome completo, se desejar.

Algumas coisas que você deve saber ao usar Texto em colunas para separar nomes e sobrenomes no Excel:

1. O resultado disso é estático. Isso significa que, caso você tenha novos dados ou os dados originais tenham algumas alterações, será necessário fazer tudo de novo para dividir os nomes.
2. Se você deseja apenas o nome ou apenas o sobrenome, pode pular as colunas que não deseja na etapa 3 da caixa de diálogo ‘Assistente de texto para coluna’. Para fazer isso, selecione a coluna na visualização que deseja ignorar e, em seguida, selecione a opção ‘Não importar coluna (ignorar)’.
3. Caso você não especifique a célula de destino, o Texto para coluna sobrescreverá a coluna atual

A opção Texto para colunas é mais adequada quando você tem dados consistentes (por exemplo, todos os nomes têm nome e sobrenome apenas ou todos os nomes têm nome, nome do meio e sobrenome).

Neste exemplo, mostrei como separar nomes que têm espaço como delimitador. Caso o delimitador seja uma vírgula ou uma combinação de vírgula e espaço, você ainda pode usar as mesmas etapas. Nesse caso, você pode especificar o delimitador na Etapa 2 de 3 do assistente. Já existe uma opção para usar a vírgula como delimitador, ou você pode selecionar a opção "Outro" e especificar um delimitador personalizado também.

Embora Text to Columns seja uma maneira rápida e eficiente de dividir nomes, é adequado apenas quando você deseja que a saída seja um resultado estático. No caso de você ter um conjunto de dados que pode se expandir ou mudar, é melhor usar fórmulas para separar os nomes.

Separe o nome, o nome do meio e o sobrenome usando fórmulas

As fórmulas permitem que você divida e divida os dados do texto e extraia o que deseja.

Nesta seção, compartilharei várias fórmulas que você pode usar para separar os dados de nome (com base em como seus dados são estruturados).

Abaixo estão as três fórmulas que você pode usar para separar o nome, o nome do meio e o sobrenome (explicadas em detalhes posteriormente nas seções a seguir).

Fórmula para obter o primeiro nome:

= ESQUERDA (A2, PESQUISA ("", A2) -1)

Fórmula para obter o nome do meio:

= MID (A2, SEARCH ("", A2) + 1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1)) - SEARCH ("", A2))

Fórmula para obter o sobrenome:

= RIGHT (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Obtenha o primeiro nome

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja separar rapidamente o nome em uma célula e o sobrenome em uma célula.

A fórmula abaixo fornecerá o primeiro nome:

= ESQUERDA (A2, PESQUISA ("", A2) -1)

A fórmula acima usa a função SEARCH para obter a posição do caractere de espaço entre o nome e o sobrenome. A função LEFT então usa este número de posição de espaço para extrair todo o texto antes dele.

Este é um uso bastante simples de extrair uma parte do valor do texto. Uma vez que tudo o que precisamos fazer é identificar a posição do primeiro caractere de espaço, não importa se o nome tem qualquer nome do meio ou não. A fórmula acima vai funcionar muito bem.

Agora, vamos avançar um pouco mais com cada exemplo.

Pegue o Sobrenome

Digamos que você tenha o mesmo conjunto de dados e, desta vez, precise obter o sobrenome.

A fórmula abaixo extrairá o sobrenome do conjunto de dados acima:

= DIREITA (A2, LEN (A2) -SEARCH ("", A2))

Novamente, bastante direto.

Desta vez, encontramos primeiro a posição do caractere de espaço, que é então usada para descobrir o número de caracteres restantes após o espaço (que seria o sobrenome).

Isso é obtido subtraindo o valor da posição do caractere de espaço com o número total de caracteres do nome.

Esse número é então usado na função RIGHT para buscar todos esses caracteres à direita do nome.

Embora essa fórmula funcione muito bem quando há apenas o nome e o sobrenome, ela não funcionaria se você também tiver um nome do meio. Isso ocorre porque contabilizamos apenas um caractere de espaço (entre o nome e o sobrenome). Ter um nome do meio adiciona mais caracteres de espaço ao nome.

Para buscar o sobrenome quando você também tem um nome do meio, use a fórmula abaixo:

= RIGHT (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Agora, isso começou a se tornar um pouco complexo … não é?

Deixe-me explicar como isso funciona.

A fórmula acima encontra primeiro o número total de caracteres de espaço no nome. Isso é feito obtendo o comprimento do nome com e sem o caractere de espaço e, em seguida, subtraindo aquele sem espaço daquele com espaço. Isso dá o número total de caracteres de espaço.

A função SUBSTITUTE é então usada para substituir o último caractere de espaço por um símbolo ‘@’ (você pode usar qualquer símbolo - algo que é improvável de ocorrer como parte do nome).

Uma vez que o símbolo @ foi substituído no lugar do último caractere de espaço, você pode encontrar facilmente a posição deste símbolo @. Isso é feito usando a função SEARCH.

Agora tudo que você precisa fazer é extrair todos os caracteres à direita deste símbolo @. Isso é feito usando a função RIGHT.

Obtenha o nome do meio

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja extrair o nome do meio.

A seguinte fórmula fará isso:

= MID (A2, SEARCH ("", A2) + 1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1)) - SEARCH ("", A2))

A fórmula acima usa a função MID, que permite especificar uma posição inicial e o número de caracteres a serem extraídos dessa posição.

A posição inicial é fácil de encontrar usando a função SEARCH.

A parte difícil é descobrir quantos caracteres extrair após esta posição inicial. Para conseguir isso, você precisa identificar quantos caracteres existem desde a posição inicial até o último caractere de espaço.

Isso pode ser feito usando a função SUBSTITUTE e substitua o último caractere de espaço por um símbolo ‘@’. Feito isso, você pode usar facilmente a função SEARCH para encontrar a posição deste último caractere de espaço.

Agora que você tem a posição inicial e a posição do último espaço, pode buscar facilmente o nome do meio usando a função MID.

Um dos benefícios de usar uma fórmula para separar os nomes é que o resultado é dinâmico. Portanto, caso seu conjunto de dados se expanda e mais nomes sejam adicionados a ele, ou se alguns nomes mudam no conjunto de dados original, você não precisa se preocupar com os dados resultantes.

Separar Nomes Usando Localizar e Substituir

Eu amo a flexibilidade que vem com ‘Find and Replace’ - porque você pode usar caracteres curinga nele.

Deixe-me primeiro explicar o que é um caractere curinga.

Um caractere curinga é algo que você pode usar em vez de qualquer texto. Por exemplo, você pode usar um símbolo de asterisco (*) e ele representará qualquer número de caracteres no Excel. Para dar um exemplo, se eu quiser encontrar todos os nomes que começam com o alfabeto A, posso usar A * em localizar e substituir. Isso encontrará e selecionará todas as células onde o nome começa com A.

Se você ainda não entendeu, não se preocupe. Continue lendo e os próximos exemplos deixarão claro o que são caracteres curinga e como usá-los para separar nomes rapidamente (ou quaisquer valores de texto no Excel).

Em todos os exemplos cobertos abaixo, certifique-se de criar uma cópia de backup do conjunto de dados. Localizar e substituir altera os dados nos quais é usado. É melhor copiar e colar os dados primeiro e, em seguida, usar Localizar e substituir no conjunto de dados copiado.

Obtenha o primeiro nome

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter apenas o primeiro nome.

Abaixo estão as etapas para fazer isso:

1. Copie os dados do nome na coluna A e cole-os na coluna B.
2. Com os dados da Coluna B selecionados, clique na guia Página Inicial
3. No grupo Editing, clique em Find & Select.
4. Clique em Substituir. Isso abrirá a caixa de diálogo ‘Localizar e substituir’.
5. Na caixa de diálogo ‘Localizar e substituir’, digite o seguinte
   1. Encontre o que: * (caractere de espaço seguido pelo símbolo de asterisco)
   2. Substituir por: deixe em branco
6. Clique em Substituir tudo.

As etapas acima fornecerão o primeiro nome e removerão tudo após o primeiro nome.

Isso funciona mesmo se você tiver nomes com um nome do meio.

Dica Profissional: O atalho de teclado para abrir a caixa de diálogo Localizar e substituir é Control + H (segure a tecla de controle e, em seguida, pressione a tecla H).

Pegue o Sobrenome

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter apenas o sobrenome.

Abaixo estão as etapas para fazer isso:

1. Copie os dados do nome na coluna A e cole-os na coluna B.
2. Com os dados da Coluna B selecionados, clique na guia Página Inicial
3. No grupo Editing, clique em Find & Select.
4. Clique em Substituir. Isso abrirá a caixa de diálogo ‘Localizar e substituir’.
5. Na caixa de diálogo ‘Localizar e substituir’, digite o seguinte
   1. Encontre o que: * (símbolo de asterisco seguido por um caractere de espaço)
   2. Substituir por: deixe em branco
6. Clique em Substituir tudo.

As etapas acima forneceriam o sobrenome e removeriam tudo antes do nome.

Isso funciona mesmo se você tiver nomes com um nome do meio.

Remova o nome do meio

Caso você queira apenas se livrar do nome do meio e tenha apenas o nome e o sobrenome, você pode fazer isso usando Localizar e Substituir.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja remover o nome do meio deles.

Abaixo estão as etapas para fazer isso:

1. Copie os dados do nome na coluna A e cole-os na coluna B.
2. Com os dados da Coluna B selecionados, clique na guia Página Inicial
3. No grupo Editing, clique em Find & Select.
4. Clique em Substituir. Isso abrirá a caixa de diálogo ‘Localizar e substituir’.
5. Na caixa de diálogo ‘Localizar e substituir’, digite o seguinte
   1. Encontre o que: * (caractere de espaço seguido pelo símbolo de asterisco seguido pelo caractere de espaço)
   2. Substitua por: (basta inserir um caractere de espaço aqui)
6. Clique em Substituir tudo.

As etapas acima removeriam o nome do meio de um nome completo. Caso alguns nomes não possuam nenhum nome do meio, eles não seriam alterados.

Separar nomes usando Flash Fill

O preenchimento em Flash foi introduzido no Excel 2013 e torna realmente fácil modificar ou limpar um conjunto de dados de texto.

E quando se trata de separar dados de nomes, é o caminho certo para o Flash Fill.

A coisa mais importante a saber ao usar o Flash Fill é que é necessário um padrão que o Flash Fill possa identificar. Depois de identificar o padrão, ele o ajudará facilmente a dividir os nomes no Excel (você obterá mais clareza sobre isso ao examinar alguns exemplos abaixo).

Obtenha o nome ou o sobrenome do nome completo

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter apenas o primeiro nome.

1. Na célula adjacente, digite manualmente o primeiro nome do nome completo. Neste exemplo, eu digitaria Rick.
2. Na segunda célula, digite manualmente o primeiro nome do nome da célula adjacente. Enquanto você digita, o Flash Fill mostra uma lista do primeiro nome automaticamente (em cinza).
3. Ao ver os nomes em cinza, dê uma olhada rápida para se certificar de que está mostrando os nomes corretos. Se estiverem corretas, pressione a tecla Enter e o Flash Fill preencherá automaticamente o restante das células com o primeiro nome.

O Flash Fill precisa que você dê a ele um padrão que ele possa seguir ao fornecer os dados modificados. Em nosso exemplo, quando você digita o primeiro nome na primeira célula, o Flash Fill não consegue descobrir o padrão.

Mas assim que você começa a inserir o nome na segunda célula, o Flash Fill entende o padrão e mostra uma sugestão. Se a sugestão estiver correta, basta pressionar a tecla Enter.

E se não estiver correto, você pode tentar inserir manualmente em mais algumas células e verificar se o Flash Fill é capaz de discernir o padrão ou não.

Às vezes, você pode não ver o padrão em cinza (conforme mostrado na etapa 2 acima). Se for esse o caso, siga as etapas abaixo para obter o resultado do Preenchimento Flash:

1. Insira o texto manualmente em duas células.
2. Selecione ambas as células
3. Passe o cursor na parte inferior direita da seleção. Você notará que o cursor muda para um ícone de mais
4. Clique duas vezes nele (botão esquerdo do mouse). Isso preencherá todas as células. Neste momento, os resultados provavelmente estão incorretos e não o que você esperava.
5. No canto inferior direito dos dados resultantes, você verá um pequeno ícone de preenchimento automático. Clique neste ícone de preenchimento automático
6. Clique em Flash Fill

As etapas acima forneceriam o resultado do Preenchimento Flash (com base no padrão que ele deduziu).

Você também pode usar o Flash Fill para obter o sobrenome ou o nome do meio. Nas primeiras duas células, digite o sobrenome (ou o nome do meio) e o preenchimento instantâneo será capaz de entender o padrão

Reorganizar o nome usando o preenchimento do Flash

O Flash Fill é uma ferramenta inteligente e também pode decifrar padrões ligeiramente complexos

Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja reorganizar o nome de Rick Novak para Novak, Rick (onde o sobrenome vem primeiro seguido por uma vírgula e, em seguida, o nome).

Abaixo estão as etapas para fazer isso:

1. Na célula adjacente, digite manualmente Novak, Rick
2. Na segunda célula, digite manualmente Connor, Susan. Enquanto você digita, o Flash Fill mostra uma lista dos nomes no mesmo formato (em cinza).
3. Ao ver os nomes em cinza, dê uma olhada rápida para se certificar de que está mostrando os nomes corretos. Se estiverem corretos, pressione a tecla Enter e o Flash Fill preencherá automaticamente o restante das células com os nomes no mesmo formato.

Remova o nome do meio (ou apenas pegue o nome do meio)

Você também pode usar o Flash Fill para se livrar do nome do meio ou obter apenas o nome do meio.

Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter apenas o nome e o sobrenome e não o nome do meio.

Abaixo estão as etapas para fazer isso:

1. Na célula adjacente, digite manualmente Rick Novak
2. Na segunda célula, digite manualmente Susan Connor. Enquanto você digita, o Flash Fill mostra uma lista dos nomes no mesmo formato (em cinza).
3. Ao ver os nomes em cinza, dê uma olhada rápida para se certificar de que está mostrando os nomes corretos. Se estiverem corretos, pressione a tecla Enter e o Flash Fill preencherá automaticamente o restante das células com os nomes sem o nome do meio.

Da mesma forma, se você deseja obter apenas os nomes do meio, digite o nome do meio nas duas primeiras células e use o Preenchimento Flash para obter o nome do meio de todos os nomes restantes.

Os exemplos mostrados neste tutorial usam nomes ao manipular os dados de texto. Você pode usar os mesmos conceitos para trabalhar também com outros formatos de dados (como endereços, nomes de produtos, etc.)