Extraia o primeiro nome do meio e o sobrenome do nome completo no Excel
Supondo que você tenha uma lista de nomes de usuário, agora você gostaria de dividir os nomes completos em nome, nome do meio e sobrenome em colunas separadas, como mostrado a seguir. Neste artigo, irei apresentar algumas fórmulas para lidar com esse trabalho no Excel.
- Extraia ou obtenha primeiros nomes de nomes completos no Excel
- Extraia ou obtenha sobrenomes de nomes completos no Excel
- Extraia ou obtenha nomes do meio de nomes completos no Excel
- Extraia ou obtenha nomes, nomes do meio e sobrenomes completos com um recurso incrível
Extraia ou obtenha primeiros nomes de nomes completos no Excel
Para extrair ou obter os primeiros nomes de nomes completos, as funções LEFT e SEARCH podem ajudá-lo, a sintaxe genérica é:
- name: O nome completo ou referência de célula da qual você deseja extrair primeiro.
Copie ou insira a seguinte fórmula em uma célula em branco de onde deseja obter o resultado:
Em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula, e todos os primeiros nomes foram extraídos conforme a imagem mostrada abaixo:
Explicação da fórmula:
PESQUISA ("", A2) -1: Esta função PESQUISA é usada para obter a posição do primeiro caractere de espaço, subtrair 1 significa que o número de caractere que você deseja é um a menos que a posição do espaço. Isso obterá o resultado: 5.
ESQUERDA (A2, PESQUISA ("", A2) -1) = ESQUERDA ("Jerry T. Stone", 5): Esta função ESQUERDA extrairá o texto com o nome completo “Jerry T. Stone” do lado esquerdo com 5 caracteres que são retornados pela função SEARCH.
Extraia ou obtenha sobrenomes de nomes completos no Excel
Para extrair os sobrenomes de nomes completos, a combinação das funções RIGHT, LEN, FIND, LEN e SUBSTITUTE pode lhe fazer um favor. A sintaxe genérica é:
- name: O nome completo ou referência de célula da qual você deseja extrair o sobrenome.
Copie ou insira a fórmula abaixo em uma célula em branco onde deseja obter os sobrenomes:
Em seguida, arraste a alça de preenchimento até as células para preencher esta fórmula, e todos os sobrenomes foram extraídos dos nomes completos, veja a captura de tela:
Explicação da fórmula:
1. LEN (A2) -LEN (SUBSTITUIR (A2, "", "")): Esta parte é usada para obter o número de caracteres de espaço na célula A2.
- LEN (A2): Esta função LEN retorna o número total de caracteres na célula A2. Ele retornará: 14.
- SUBSTITUIR (A2, "", ""): Esta função SUBSTITUTE é usada para substituir todos os caracteres de espaço por nada. E você obterá o resultado como este: “Jerry T. Stone".
- LEN (SUBSTITUIR (A2, "", ""): Obtenha o comprimento total do nome completo na célula A2 sem os espaços.
- LEN (A2) -LEN (SUBSTITUIR (A2, "-", "")): Subtraia o comprimento do nome completo sem espaço do comprimento total do nome completo para obter o número do espaço e obterá 2.
2. SUBSTITUIR (A2, "", "*", LEN (A2) -LEN (SUBSTITUIR (A2, "", ""))) = SUBSTITUIR (A2, "", "*", 2): Esta função SUBSTITUTE é usada para substituir a última ocorrência do espaço que retornou pela fórmula da primeira parte com um caractere *. E você obterá este resultado: “Jerry T. * Stone".
3. ENCONTRAR ("*", SUBSTITUIR (A2, "", "*", LEN (A2) -LEN (SUBSTITUIR (A2, "", "")))) = ENCONTRAR ("*", "Jerry T. *Pedra" ): A função FIND retornará a posição do caractere * na string de texto que é retornada pela função SUBSTITUTE, e você obterá o número 9.
4. LEN (A2) -FIND ("*", SUBSTITUIR (A2, "", "*", LEN (A2) -LEN (SUBSTITUIR (A2, "", "")))) = LEN (A2) - 9: Esta parte da fórmula obterá quantos caracteres existem após o último espaço. Isso obterá o número 5. Isso será reconhecido como o argumento num_chars na função RIGHT.
5. RIGHT (A2, LEN (A2) -FIND ("*", SUBSTITUTE (A2, "", "*", LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))) = DIREITO (A2, 5): Por fim, a função RIGHT é usada para extrair 5 caracteres que são retornados pela fórmula na etapa 4 do lado direito da string de texto na célula A2.
Nota: Se a sua lista de nomes completos contém apenas o nome e o sobrenome, você pode aplicar a seguinte fórmula simples para obter o sobrenome conforme necessário:
Extraia ou obtenha nomes do meio de nomes completos no Excel
Se você precisar extrair os nomes do meio dos nomes completos, esta fórmula é criada pelas funções MID e SEARCH. A sintaxe genérica é:
- name: O nome completo ou referência de célula da qual você deseja extrair o nome do meio.
Insira ou copie a seguinte fórmula em uma célula em branco para gerar o resultado:
Em seguida, arraste a alça de preenchimento até as células para aplicar esta fórmula, e todos os nomes do meio são extraídos também, 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, aplicar a função MID para extrair o texto no meio da string de texto.
1. PESQUISA ("", A2) + 1: Esta função PESQUISAR obterá a posição do primeiro espaço, adicionando 1 meio para iniciar a extração do próximo caractere. Isso obterá o número 7. Esta parte é reconhecida como o argumento start_num na função MID.
2. PESQUISA ("", A2, PESQUISA ("", A2) +1) - PESQUISA ("", A2) -1: Esta parte é reconhecida como o argumento num_chars na função MID.
- PESQUISA ("", A2, PESQUISA ("", A2) +1): Esta parte da fórmula é usada para obter a posição do segundo espaço, ela obterá o número 9.
- PESQUISA ("", A2): Esta função PESQUISA obterá a posição do primeiro espaço na célula A2. Ele receberá o número 6.
- SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1 = 9-6-1: Subtrair a posição do primeiro espaço da posição do segundo espaço e, em seguida, subtrair 1 do resultado significa remover um espaço inicial. E o resultado é 2.
3. MEIO (A2, PESQUISA ("", A2) + 1, PESQUISA ("", A2, PESQUISA ("", A2) +1) - PESQUISA ("", A2) -1) = MÉDIO (A2,7,2 , XNUMX): Por fim, a função MID irá extrair 2 caracteres no meio da célula A2 que começa no sétimo caractere.
Nota: Se houver apenas nomes e sobrenomes nos nomes completos, a fórmula acima obterá um valor de erro conforme a imagem mostrada abaixo:
Para corrigir esse problema, você deve incluir a fórmula acima na função IFERROR, aplique a seguinte fórmula:
Extraia ou obtenha nomes, nomes do meio e sobrenomes completos com um recurso incrível
Se você sente dor com as fórmulas acima, aqui, vou recomendar uma ferramenta fácil - Kutools for Excel, Com o seu Nomes de divisão recurso, você pode extrair todos os primeiros, médios e sobrenomes em células separadas de uma vez. Clique para baixar Kutools para Excel para avaliação gratuita!
Funções relativas usadas:
- LEFT:
- A função LEFT extrai o número fornecido de caracteres do lado esquerdo de uma string fornecida.
- RIGHT:
- A função RIGHT é usada para extrair um número específico de caracteres do lado direito da string de texto
- MID:
- A função MID é usada para encontrar e retornar um número específico de caracteres do meio de uma determinada sequência de texto.
- LEN:
- A função LEN retorna o número de caracteres em uma string de texto.
- FIND:
- A função FIND é usada para encontrar uma string dentro de outra string e retorna a posição inicial da string dentro de outra.
- SEARCH:
- A função SEARCH pode ajudá-lo a encontrar a posição de um caractere específico ou substring da string de texto fornecida
- SUBSTITUTE:
- A função SUBSTITUTE do Excel substitui o texto ou caracteres em uma sequência de texto por outro texto ou caracteres.
Mais artigos:
- Extraia a inicial e o sobrenome do nome completo
- Supondo que você tenha uma lista de nomes completos, agora você gostaria de extrair a inicial do nome e o sobrenome ou o nome e o primeiro caractere do sobrenome do nome completo, conforme as imagens abaixo mostradas. Como você poderia lidar com essas tarefas na pasta de trabalho do Excel?
- Inverter ou inverter o nome e o sobrenome na lista do Excel
- Se você tiver uma lista de nomes completos formatados como sobrenome e nome, agora você deseja inverter o sobrenome e o nome para o primeiro e o último, conforme a imagem mostrada abaixo. Neste artigo, irei apresentar algumas fórmulas para lidar com esse trabalho no Excel.
- Extraia várias linhas de uma célula
- Se você tiver uma lista de strings de texto que são separadas por quebras de linha (que ocorre pressionando as teclas Alt + Enter ao inserir o texto), e agora, você deseja extrair essas linhas de texto em várias células, como mostrado na imagem abaixo. Como você poderia resolver isso com uma fórmula no Excel?
- Extraia a enésima palavra da string de texto no Excel
- Se você tiver uma lista de strings de texto ou sentenças, agora, você deseja extrair a enésima palavra específica da lista, conforme a imagem mostrada abaixo. Neste artigo, vou apresentar alguns métodos para resolver este trabalho no Excel.
As melhores ferramentas de produtividade para escritório
Kutools for Excel - ajuda você a se destacar da multidão
Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.
- Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
- Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
- Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
- Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do 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 (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
