Os datasets contendo nomes de pessoas geralmente têm o nome e o sobrenome em uma única célula, separados por um espaço. Embora isso não seja necessariamente um problema, pode ser mais útil ter o nome e o sobrenome em colunas diferentes. Dessa forma, é possível realizar análises adicionais nos dados com mais facilidade, como ordenar e filtrar apenas pelo sobrenome.
1. Separar Nomes e Sobrenomes com Texto para Colunas
Quando você precisa dividir o nome e o sobrenome em seus dados ocasionalmente, “Texto para Colunas” deve ser o seu método preferido. A função “Texto para Colunas” permitirá que você divida os nomes em um novo intervalo ou substitua os dados atuais. O que você preferir.
Isso é mais adequado para casos de uso únicos, pois você precisará executar novamente a divisão do texto em colunas para cada novo nome adicionado à lista.
Veja como usar “Texto para Colunas” para separar nome e sobrenome:
- Selecione o intervalo de nomes que você deseja dividir.
- Vá para a guia “Dados”.
- Clique no comando “Texto para Colunas” na seção “Ferramentas de Dados”.
- Selecione a opção “Delimitado” para dividir seus nomes.
- Desmarque a opção padrão “Tabulação” e selecione “Espaço”.
- Selecione uma célula para o “Destino”.
- Clique em “Concluir”.
O Excel dividirá os nomes com base no espaço que os separa e posicionará os resultados na localização desejada.
2. Separar Nomes e Sobrenomes com Fórmulas de Texto
Outra maneira de obter o nome e o sobrenome do seu conjunto de nomes completos é usar uma combinação de funções de texto para analisar cada nome.
Você pode usar as funções “ESQUERDA” e “DIREITA” para obter o número desejado de caracteres do início ou do fim de qualquer valor de texto. Você pode usá-las em combinação com a função “LOCALIZAR” para obter a posição do caractere de espaço e a função “NÚM.CARACT” para obter o número total de caracteres no nome.
Obter o Nome
= ESQUERDA ( B3, ENCONTRAR ( " ", B3 ) - 1 )
A fórmula acima extrairá o nome da descrição completa do nome na célula B3. A função “LOCALIZAR” encontrará a posição do primeiro caractere de espaço. Ao subtrair 1 desse resultado, você obterá a posição do último caractere do nome. A função “ESQUERDA” então usa esse valor para retornar esses primeiros caracteres do nome.
Obter o Sobrenome
= DIREITA ( B3, NÚM.CARACT ( B3 ) - ENCONTRAR ( " ", B3 ) )
A fórmula acima obterá o sobrenome da descrição completa do nome na célula B3. A função “NÚM.CARACT” obtém a contagem total de caracteres do nome completo. A função “LOCALIZAR” obtém a posição do caractere de espaço que separa o nome e o sobrenome. Ao subtrair o número total de caracteres do nome da posição do caractere de espaço, você obtém o número de caracteres do sobrenome. Essa informação é então usada na função “DIREITA” para extrair o sobrenome.
3. Separar Nomes e Sobrenomes com a Função FILTERXML
Uma maneira astuta de dividir o texto com base no caractere de espaço é usar a função “FILTERXML”. Essa função foi projetada para permitir a extração de partes específicas de dados XML com base no nome do nó.
Mas você pode usá-la para extrair os nomes, criando primeiro os dados XML a partir do seu nome.
= TRANSPOR ( FILTERXML ( "<t><s>" & SUBSTITUIR ( B3," ","</s><s>")&"</s></t>","//s"))
A fórmula acima dividirá o nome completo na célula B3 em nome e sobrenome.
A função “SUBSTITUIR” é usada para substituir o espaço por um texto como o acima. Isso cria uma estrutura de dados XML e você pode usar a função “FILTERXML” para extrair cada um dos nomes.
Obter apenas o Nome
= FILTERXML ( "<t><s>" & SUBSTITUIR ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )
A versão anterior da fórmula FILTERXML retornará apenas o nome. O filtro //s[1] retornará apenas o primeiro elemento em uma tag s.
Obter apenas o Sobrenome
= FILTERXML ( "<t><s>" & SUBSTITUIR ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )
Da mesma forma, a fórmula anterior usa o filtro //s[2] para retornar o segundo elemento em uma tag s, que é o sobrenome.
4. Separar Nomes e Sobrenomes com a Função TEXTSPLIT
Se você tem uma versão mais recente do Excel, pode evitar as complexas soluções com fórmulas porque existe uma função dedicada para dividir o texto.
Você pode usar a função “TEXTOSPLIT” para dividir um valor de texto com base em um caractere delimitador.
Isso significa que você pode usar o caractere de espaço como delimitador para separar o nome e o sobrenome.
= TEXTOSPLIT ( B3, " " )
A fórmula acima dividirá o nome na célula B3 com base no caractere de espaço. É uma das maneiras mais simples que se pode obter!
5. Separar Nomes e Sobrenomes com Preenchimento Relâmpago
“Preenchimento Relâmpago” é uma maneira prática de transformar seus dados com base em exemplos. Você fornece alguns exemplos dos resultados que deseja na coluna adjacente e o “Preenchimento Relâmpago” determinará o padrão e preencherá o restante.
Isso pode facilmente separar seus nomes quando você fornece os resultados dos exemplos.
Veja como funciona:
- Digite alguns nomes por conta própria nas células à direita do nome completo correspondente.
- Isso deve ativar o “Preenchimento Relâmpago” e você verá os resultados sugeridos em cinza claro. Pressione “Enter” para aceitar esses resultados e inseri-los nas células.
- Se o “Preenchimento Relâmpago” não ativar automaticamente após as duas ou três primeiras entradas, você precisará ativá-lo manualmente.
- Selecione a primeira célula vazia abaixo dos seus exemplos de nomes.
- Vá para a guia “Dados” e clique no comando “Preenchimento Relâmpago” na seção “Ferramentas de Dados”.
- Repita o processo na próxima coluna para obter os sobrenomes.
6. Separar Nomes e Sobrenomes com Power Query
Power Query é uma ferramenta extraordinária para transformar seus dados de praticamente qualquer maneira que você possa imaginar. Dividir seus nomes é uma tarefa simples para o Power Query!
Power Query será certamente o caminho a seguir para sua tarefa de obter nome e sobrenome em células separadas se você quiser um processo repetível para importar e transformar seus dados no Excel.
Antes de usar o Power Query, você precisará adicionar seus dados de nomes a uma Tabela Excel.
Veja como usar o Power Query para dividir os nomes em sua tabela:
- Vá para a guia “Dados”.
- Clique no comando “Da Tabela/Intervalo”.
- Isso abrirá o editor do Power Query.
- Clique com o botão direito do mouse no cabeçalho da coluna contendo os nomes a serem divididos e escolha “Dividir Coluna”.
- Escolha a opção “Por Delimitador” no submenu.
- Selecione “Espaço” no menu suspenso “Selecionar ou inserir delimitador”.
- Pressione o botão “OK”.
Agora você terá duas novas colunas com o nome e o sobrenome separados.
7. Separar Nomes e Sobrenomes com Power Pivot
Se você está analisando dados que incluem um nome completo, você pode querer dividir os nomes usando o Power Pivot.
O modelo de dados do Power Pivot pode conter um grande número de linhas em memória, então é perfeito para trabalhar com qualquer enorme conjunto de dados.
Depois de carregar seu conjunto de dados no modelo de dados, você pode adicionar outras colunas com cálculos usando a linguagem de fórmulas DAX.
A linguagem de fórmulas DAX tem muitas funções em comum com o Excel e, neste caso, as funções usadas serão exatamente as mesmas das funções de texto vistas anteriormente.
Você pode adicionar seus dados ao modelo de dados. Selecione uma célula dentro da tabela que contém seus nomes e vá para a guia “Power Pivot”, então clique na opção “Adicionar ao Modelo de Dados”.
Isso abrirá o editor do Power Pivot com a tabela carregada no modelo.
Você poderá adicionar novas colunas clicando no cabeçalho “Adicionar Coluna” à direita dos seus dados.
= ESQUERDA ( Nomes[Nome Completo], LOCALIZAR ( " ", Nomes[Nome Completo] ) - 1 )
A fórmula acima obterá o nome para toda a coluna.
= DIREITA ( Nomes[Nome Completo], NÚM.CARACT ( Nomes[Nome Completo] ) - LOCALIZAR ( " ", Nomes[Nome Completo] ) )
A fórmula acima retornará o sobrenome para toda a coluna.
Agora você poderá usar essas colunas calculadas em uma Tabela Dinâmica.
8. Separar Nomes e Sobrenomes com VBA
Talvez você precise separar os nomes com frequência e queira uma solução de um clique para isso.
VBA pode ser a melhor solução para você.
Você pode adicionar uma macro à sua planilha que dividirá os nomes em colunas adjacentes.
Isso pode ser executado adicionando a macro à Barra de Ferramentas de Acesso Rápido para que ela esteja sempre disponível para ser usada com um único clique.
Veja como:
- Vá para a guia “Desenvolvedor” e clique no comando “Visual Basic”.
- Crie um novo módulo no editor do Visual Basic e adicione o seguinte código:
Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer
colCount = Selection.Columns.Count
If colCount <> 1 Then
MsgBox ("Select a single column!")
End
End If
For Each rng In Selection
arrNames = Split(rng.Value, " ")
rng.Offset(0, 1).Value = arrNames(0)
rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub
Este código primeiro verifica se você selecionou apenas um intervalo com uma única coluna. Se você selecionou várias colunas, ele avisará para selecionar uma única coluna e interromperá a execução.
Quando uma única coluna é selecionada, o código percorrerá o intervalo selecionado e dividirá cada célula com base no caractere de espaço.
A primeira e a segunda parte dos nomes divididos são inseridas nas células diretamente à direita da sua seleção original.
Depois de selecionar seus nomes e executar o código, você terá o nome e o sobrenome divididos no Excel.
9. Separar Nomes e Sobrenomes com Office Scripts
Se você usa principalmente o Excel online, precisará usar o Office Scripts para criar um código personalizado para separar seus nomes.
Você pode criar um script do Office semelhante:
- Vá para a guia “Automatizar” e clique no comando “Novo Script” para abrir o “Editor de Código” para Office Scripts.
- Adicione o seguinte código ao editor:
function main(workbook: ExcelScript.Workbook) { //getselected range let rng = workbook.getSelectedRange(); let rows = rng.getRowCount(); let cols = rng.getColumnCount(); if (cols != 1) { return; }; //loop through selected cells for (let i = 0; i < rows; i++) { for (let j = 0; j < cols; j++) { //split name based on space let txtName = rng.getCell(i, j).getValue().toString(); let arrName = txtName.split(" "); //enter names in adjacent cells rng.getCell(i, j + 1).setValue(arrName[0]); rng.getCell(i, j + 2).setValue(arrName[1]); }; }; };
Adicione o código ao “Editor de Código” e pressione o botão “Salvar”.
- Agora você pode selecionar um intervalo de coluna única de nomes em sua planilha do Excel e pressionar o botão “Executar”.
- O código verificará se sua seleção é uma única coluna e interromperá a execução do código caso contrário.
Caso contrário, o código continuará e percorrerá suas células selecionadas para dividir os nomes. O nome e o sobrenome são adicionados nas colunas à direita da sua seleção.
Conclusões
Muitos conjuntos de dados incluirão um campo de nome. Às vezes, isso virá como um nome completo em uma única célula e será necessário separar o nome em seus componentes de nome e sobrenome. “Texto para Colunas” ou “Preenchimento Relâmpago” resolverão o problema com alguns passos simples e são perfeitos para usos únicos.
Uma solução com fórmula representa uma solução mais dinâmica, mais adequada quando o conjunto de dados de origem adicionará ou modificará nomes. A fórmula que você usará dependerá das funções disponíveis em sua versão do Excel. Para conjuntos de dados maiores ou conjuntos de dados externos que precisam ser importados para o Excel primeiro, a solução Power Query ou Power Pivot funcionará melhor. Você já precisou separar nomes? Como você resolveu? Deixe-me saber nos comentários abaixo!
Pubblicato in Excel
Seja o primeiro a comentar