Extrair primeiro, meio e sobrenome do nome completo no Excel
Suponha que você tenha uma lista de nomes de usuários e agora deseja dividir os nomes completos em primeiro, meio e sobrenomes em colunas separadas, conforme mostrado na captura de tela a seguir. Neste artigo, vou apresentar algumas fórmulas para lidar com essa tarefa no Excel.
- Extrair ou obter primeiros nomes dos nomes completos no Excel
- Extrair ou obter sobrenomes dos nomes completos no Excel
- Extrair ou obter nomes do meio dos nomes completos no Excel
- Extrair ou obter primeiro, meio e sobrenome dos nomes completos com um recurso incrível
Extrair ou obter primeiros nomes dos nomes completos no Excel
Para extrair ou obter os primeiros nomes dos nomes completos, as funções ESQUERDA e PROCURAR podem ajudá-lo. A sintaxe genérica é:
- nome: O nome completo ou referência de célula do qual você deseja extrair o primeiro nome.
Por favor, copie ou insira a seguinte fórmula em uma célula em branco onde você deseja obter o resultado:
Em seguida, arraste a alça de preenchimento para baixo até as células onde deseja aplicar esta fórmula, e todos os primeiros nomes serão extraídos conforme mostrado na captura de tela abaixo:
Explicação da fórmula:
PROCURAR(" ",A2)-1: Esta função PROCURAR é usada para obter a posição do primeiro caractere de espaço; subtrair 1 significa que o número de caracteres desejados é um a menos que a posição do espaço. Isso retornará o resultado: 5.
ESQUERDA(A2,PROCURAR(" ",A2)-1)= ESQUERDA("Jerry T. Stone",5): Esta função ESQUERDA extrairá o texto no nome completo “Jerry T. Stone” a partir do lado esquerdo com 5 caracteres, que são retornados pela função PROCURAR.
Extrair ou obter sobrenomes dos nomes completos no Excel
Para extrair os sobrenomes dos nomes completos, a combinação das funções DIREITA, COMP, LOCALIZAR, COMP e SUBSTITUIR pode ajudá-lo. A sintaxe genérica é:
- nome: O nome completo ou referência de célula do qual você deseja extrair o sobrenome.
Por favor, copie ou insira a fórmula abaixo em uma célula em branco onde você deseja obter os sobrenomes:
E então, arraste a alça de preenchimento para baixo até as células para preencher esta fórmula, e todos os sobrenomes serão extraídos dos nomes completos, veja a captura de tela:
Explicação da fórmula:
1. COMP(A2)-COMP(SUBSTITUIR(A2," ","")): Esta parte é usada para obter o número de caracteres de espaço na célula A2.
- COMP(A2): Esta função COMP retorna o número total de caracteres na célula A2. Ela retornará: 14.
- SUBSTITUIR(A2," ",""): Esta função SUBSTITUIR é usada para substituir todos os caracteres de espaço por nada. E você obterá o resultado assim: “JerryT.Stone”.
- COMP(SUBSTITUIR(A2," ","")): Obtém o comprimento total do nome completo na célula A2 sem os espaços.
- COMP(A2)-COMP(SUBSTITUIR(A2,"-","")): Subtraia o comprimento do nome completo sem espaço do comprimento total do nome completo para obter o número de espaços, e isso resultará em 2.
2. SUBSTITUIR(A2," ","*",COMP(A2)-COMP(SUBSTITUIR(A2," ","")))= SUBSTITUIR(A2," ","*",2): Esta função SUBSTITUIR é usada para substituir a última ocorrência do espaço, que foi retornada pela primeira parte da fórmula, por um caractere *. E você obterá este resultado: “Jerry T.*Stone”.
3. LOCALIZAR("*",SUBSTITUIR(A2," ","*",COMP(A2)-COMP(SUBSTITUIR(A2," ",""))))= LOCALIZAR("*","Jerry T.*Stone" ): A função LOCALIZAR retornará a posição do caractere * na string de texto que foi retornada pela função SUBSTITUIR, e você obterá o número 9.
4. COMP(A2)-LOCALIZAR("*",SUBSTITUIR(A2," ","*",COMP(A2)-COMP(SUBSTITUIR(A2," ",""))))= COMP(A2)-9: Esta parte da fórmula obtém quantos caracteres existem após o último espaço. Isso retornará o número 5. Este será reconhecido como o argumento num_chars na função DIREITA.
5. DIREITA(A2,COMP(A2)-LOCALIZAR("*",SUBSTITUIR(A2," ","*",COMP(A2)-COMP(SUBSTITUIR(A2," ","")))))=DIREITA(A2, 5): Por fim, a função DIREITA é usada para extrair 5 caracteres que são retornados pela fórmula na etapa 4 a partir do lado direito da string de texto na célula A2.
Nota: Se sua lista de nomes completos contiver apenas o primeiro e o último nome, você pode aplicar a seguinte fórmula simples para obter o sobrenome conforme necessário:
Extrair ou obter nomes do meio dos nomes completos no Excel
Se você precisar extrair os nomes do meio dos nomes completos, esta fórmula criada pelas funções MEIO e PROCURAR pode ser usada. A sintaxe genérica é:
- nome: O nome completo ou referência de célula do qual você deseja extrair o nome do meio.
Por favor, insira ou copie a seguinte fórmula em uma célula em branco para exibir o resultado:
Em seguida, arraste a alça de preenchimento para baixo até as células para aplicar esta fórmula, e todos os nomes do meio também serão extraídos, veja a captura de tela:
Explicação da fórmula:
Para extrair o nome do meio, primeiro você deve encontrar a posição de ambos os espaços entre o nome completo e, em seguida, aplique a função MEIO para extrair o texto no meio da string de texto.
1. PROCURAR(" ", A2) + 1: Esta função PROCURAR obterá a posição do primeiro espaço; adicionar 1 significa começar a extração a partir do próximo caractere. Isso retornará o número 7. Esta parte é reconhecida como o argumento início_num dentro da função MEIO.
2. PROCURAR(" ", A2, PROCURAR(" ", A2)+1) - PROCURAR(" ", A2)-1: Esta parte é reconhecida como o argumento num_caract na função MEIO.
- PROCURAR(" ", A2, PROCURAR(" ", A2)+1): Esta parte da fórmula é usada para obter a posição do segundo espaço; ela retornará o número 9.
- PROCURAR(" ", A2): Esta função PROCURAR obterá a posição do primeiro espaço na célula A2. Ela retornará o número 6.
- PROCURAR(" ", A2, PROCURAR(" ", A2)+1) - PROCURAR(" ", A2)-1=9-6-1: Subtraia a posição do primeiro espaço da posição do segundo espaço e, em seguida, subtraia 1 do resultado para remover um espaço inicial. E o resultado será 2.
3. MEIO(A2, PROCURAR(" ", A2) + 1, PROCURAR(" ", A2, PROCURAR(" ", A2)+1) - PROCURAR(" ", A2)-1)=MEIO(A2,7,2): Por fim, a função MEIO extrairá 2 caracteres no meio da célula A2, começando no sétimo caractere.
Nota: Se houver apenas primeiro e último nome nos nomes completos, a fórmula acima retornará um valor de erro conforme mostrado na captura de tela abaixo:
Para corrigir esse problema, você deve colocar a fórmula acima na função SEERRO; por favor, aplique a seguinte fórmula:
Extrair ou obter primeiro, meio e sobrenome dos nomes completos com um recurso incrível
Se você está tendo dificuldades com as fórmulas acima, aqui eu vou recomendar uma ferramenta fácil – Kutools para Excel, com seu recurso Dividir Nomes, você pode extrair todos os primeiros, do meio e sobrenomes em células separadas de uma só vez. Clique para baixar Kutools para Excel para teste gratuito!
Funções relacionadas usadas:
- ESQUERDA:
- A função ESQUERDA extrai o número dado de caracteres do lado esquerdo de uma string fornecida.
- DIREITA:
- A função DIREITA é usada para extrair um número específico de caracteres do lado direito da string de texto.
- MEIO:
- A função MEIO é usada para encontrar e retornar um número específico de caracteres do meio de uma string de texto fornecida.
- COMP:
- A função COMP retorna o número de caracteres em uma string de texto.
- LOCALIZAR:
- A função LOCALIZAR é usada para encontrar uma string dentro de outra string e retorna a posição inicial da string dentro de outra.
- PROCURAR:
- A função PROCURAR pode ajudá-lo a encontrar a posição de um caractere ou substring específica de uma string de texto fornecida.
- SUBSTITUIR:
- A função SUBSTITUIR do Excel substitui texto ou caracteres dentro de uma string de texto por outro texto ou caracteres.
Mais artigos:
- Extrair iniciais e sobrenome do nome completo
- Suponha que você tenha uma lista de nomes completos e agora deseja extrair as iniciais e sobrenome ou primeiro nome e primeira letra do sobrenome do nome completo, conforme mostrado nas capturas de tela abaixo. Como você poderia lidar com essas tarefas na pasta de trabalho do Excel?
- Inverter ou Reverter Primeiro e Último Nomes Em Lista do Excel
- Se você tem uma lista de nomes completos formatados como sobrenome e primeiro nome, e agora quer inverter os sobrenomes e primeiros nomes para primeiro e sobrenome, conforme mostrado na captura de tela abaixo. Este artigo, vou introduzir algumas fórmulas para lidar com essa tarefa no Excel.
- Extrair Múltiplas Linhas De Uma Célula
- Se você tem uma lista de strings de texto separadas por quebras de linha (que ocorrem ao pressionar as teclas Alt + Enter ao inserir o texto), e agora deseja extrair essas linhas de texto em várias células, conforme mostrado na captura de tela abaixo. Como você poderia resolver isso com uma fórmula no Excel?
- Extrair a N-ésima Palavra Da String De Texto No Excel
- Se você tem uma lista de strings de texto ou frases, e agora deseja extrair a palavra específica n-ésima da lista, conforme mostrado na captura de tela abaixo. Este artigo, vou introduzir alguns métodos para resolver essa tarefa no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.