Extraindo o nome do meio do nome completo no Excel. Combine palavras de células diferentes em texto do Excel. Este vídeo mostra em detalhes como dividir o texto em colunas
Se você tiver um intervalo de células contendo nomes e sobrenomes de pessoas, muitas vezes será necessário trabalhar apenas com o nome ou apenas com o sobrenome. Por exemplo, pode ser necessário armazenar o nome e o sobrenome separadamente para importação posterior no banco de dados no qual seu certificado de contador profissional está armazenado. Ou você pode precisar classificar os nomes.
A solução é usar a função FIND() para encontrar o espaço que separa as duas partes do nome e depois usar a função LEFT() ou RIGHT() para selecionar a parte desejada. Para destacar o lado esquerdo (assumindo que o sobrenome esteja lá), use a seguinte fórmula:
= ESQUERDA (A2, ENCONTRAR ("", A2)-1)
Observe que a fórmula subtrai um do número de caracteres necessários para realçar para evitar o aparecimento de um espaço. Você pode usar esta fórmula para qualquer exemplo mais geral para extrair a primeira palavra de qualquer string. Para destacar um nome, você precisa construir uma fórmula semelhante usando a função RIGHT():
=DIREITA(A2,COMPRIMENTO(A2)-ENCONTRAR("",A2))
Para extrair o número necessário de caracteres, a fórmula pega o comprimento da palavra e subtrai dela a posição do espaço. Você pode usar esta fórmula para qualquer exemplo mais geral para extrair a segunda palavra de qualquer string. Na Fig. A Figura 4.13 mostra os resultados do trabalho.
Aqui na coluna D você vê o resultado da combinação das duas fórmulas anteriores para alterar a ordem das palavras. Observe que essas fórmulas gerarão um erro se a linha contiver apenas uma palavra. Para evitar isso, use a função IFERROR():
=SEERRO(ESQUERDA(A2,ENCONTRAR("",A2)-1),A2) . Neste caso, se a célula não contiver espaço, o valor da célula A2 será simplesmente exibido.
Se o nome completo da pessoa com quem você está trabalhando contiver sobrenome, nome e nome do meio, você não poderá aplicar as fórmulas acima. Nesse caso, a primeira fórmula para destacar o sobrenome funcionará bem, mas selecionar o lado direito não funcionará desta forma. Existem várias maneiras de sair desta situação; veremos uma solução usando as funções SEARCH() e FIND().
Por exemplo, recebemos o seguinte nome: Bulgakov Viktor Kirsanovich. Supondo que o nome esteja na célula A2, a fórmula =FIND(" ";A2) retornará 9, a posição do primeiro espaço. Para encontrar a posição do próximo espaço, você deve especificar a posição do primeiro espaço mais 1: =FIND(" ";A2;FIND(" ";A2)+1).
Agora você pode aplicar esta construção para encontrar a parte mais à direita (neste caso é o nome do meio): =RIGHT(A2,LENGTH(A2)-FIND(" ";A2;FIND(" ",A2)+1)) .
Para selecionar a parte central, você precisa determinar a diferença na posição do primeiro e do segundo espaços e usar a fórmula para selecionar qualquer parte da linha: =PSTR(A2;FIND(" ";;A2)+1; FIND(" ";A2; FIND(" "; A2)+1-FIND(" ";A2)) .
Excel para Office 365 Excel para Office 365 para Mac Excel 2019 Excel 2016 Excel 2019 para Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 para Mac Excel para Mac 2011 Excel Online Excel para iPad Excel para iPhone Excel para tablets Android Excel para telefones Android Excel Mobile Excel Starter 2010 Menos
Digamos que você queira criar uma coluna de nome totalmente qualificado concatenando duas outras colunas, nome e sobrenome. Para combinar nomes e sobrenomes, use a função CONCATENATE ou o operador E comercial (&).
Importante: Esta função no Excel 2016Excel Mobile e Excel Online foi substituída pela função UNION. Embora a função CONCATENATE ainda esteja disponível para compatibilidade com versões anteriores, COMBINES deverá ser usado a partir de agora. Isso ocorre porque CONCATENATE pode não estar disponível em versões futuras do Excel.
ExemploCopie o seguinte em uma folha de papel em branco.
|
|
|
Muitas vezes, na elaboração de um contrato, fatura, proposta comercial ou outro documento, trabalhamos com o sobrenome, nome e patronímico do cliente. Às vezes é necessário separar o nome completo e obter apenas o primeiro nome para poder abordar o cliente no texto do documento; outras vezes é necessário fazer uma coluna com o signatário, deixando o sobrenome e as iniciais;
Claro, você pode escrever uma fórmula que extrairá os dados necessários do seu nome completo, mas mesmo uma pessoa experiente precisará de 5 a 7 minutos para fazer isso. E se você não souber antecipadamente a localização do sobrenome no texto? É muito mais fácil e conveniente usar funções prontas do complemento VBA-Excel.
Encontre o sobrenome no nome completoApós a instalação do complemento, uma nova função estará disponível = SOBRENOME(NOME COMPLETO), onde
- Nome completo - texto ou link para uma célula que contém o nome completo
O objetivo desta função é extrair apenas o sobrenome de uma célula. Além disso, a ordem não é importante para ela. O sobrenome pode aparecer no início, no final ou no meio do texto.
Encontre o nome no nome completoUma função semelhante para o nome =NAME(nome completo) . Extrai um nome de um texto com nome completo, não importa onde esteja.
A última função desta série é a função =PATERNICAL(FULL NAME) para extrair o nome do meio.
Uma das tarefas comuns na elaboração de documentos é converter o texto do formato Sobrenome Nome Patronímico para o texto Sobrenome I. O. Você pode resolvê-lo usando a seguinte fórmula:
SOBRENOME(A1)&" "&ESQUERDA(NOME(A1))&". "&ESQUERDA(NOMEPATER(A1))&"."
É um pouco longo, mas acredite, seria muito mais curto e claro se não utilizássemos as funções do add-on.
Outra vantagem é que a localização do sobrenome no texto não é importante para nós; a função determinará tudo automaticamente;
A função recebe como parâmetro uma string de texto no formato “Sobrenome primeiro nome patronímico”, e corta o nome e o patronímico, deixando apenas as iniciais - no formato “Sobrenome I. O.”
Esta função pode ser usada como UDF (Função Definida pelo Usuário) nas células da planilha do Excel.
(ver exemplo em anexo)
Descrição dos recursos da função - no fórum: programmersforum.ru/showpost.php?p=757147&postcount=6
Não existe um algoritmo formal exato para dividir um nome completo em partes.
É impossível separar formalmente um sobrenome de um nome próprio sem conhecer a genealogia e o idioma do falante nativo. Você só pode usar uma variedade de heurísticas.
Além disso, diferentes autoridades (em momentos diferentes) tinham opiniões diferentes sobre o aspecto que as iniciais deveriam ter num determinado caso.
Os documentos do Serviço Federal de Migração são uma coisa, os registradores do Banco Central são outra e a preparação de documentos comerciais é outra.
Assim, alguns serviços exigem que 2 a 3 nomes abreviados complexos do Sudeste Asiático não sejam abreviados. Mas alguns não.
Sim, com a questão de onde as iniciais devem estar à direita ou à esquerda - não há ambigüidade. Não, claro que existem regras da língua russa... Mas prevalecem as regras de rotatividade de negócios de uma ou outra estrutura (ou simplesmente como quer a pata traseira esquerda do escriturário que aceita documentos)...
Muitos podem ter uma pergunta: de onde vem todo esse zoológico e por que tudo isso é necessário? Regra geral, todos estes Don Pedros são cidadãos estrangeiros que recebem um passaporte russo com base em documentos previamente emitidos fora da jurisdição da Federação Russa. As situações jurídicas são diferentes. E muitas vezes uma transliteração da língua nativa para o cirílico é escrita no novo passaporte russo.
Os passaportes russos de -ogly e -kyzy são agora emitidos no Tartaristão. Conheci um cliente cujo nome, segundo seu passaporte de 2007, era Mustafa Oleg ogly...
Opção Comparar texto Iniciais da função pública (s As String , Opcional ToLeft As Boolean = False ) Dim sv As Variant , sФ As String , sИ As String , sО As String , i As Long , k As Long Application.Volatile True If InStr(s , "." ) > 0 Ou Len(Trim$(s)) = 0 Then Initials = s "Iniciais especificadas explicitamente ou string vazia Exit Function End If "Normalizar string de entrada s = Replace(Application.Trim(s), Chr( 30), "-" ) s = Substituir(Substituir(s, " -" , "-" ), "- " , "-" ) s = Substituir(Substituir(s, "" " , """ ), " "" , """ ) " O "Henry Alexander; O"Henry Alexander; N"Homo; D" Treville sv = Split(s) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) Se eu< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "бей, бек, заде, зуль, ибн, кызы, оглы, оль, паша, уль, хан, шах, эд, эль i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Case "паша" , "хан" , "шах" , "шейх" i = i - 1 Case Else Select Case Right$(sv(i), 3) Case "вич" , "вна" If i >= 2 Then "Finalização padrão para patronímicos russos sО = СropWord(sv(i)) Else "Tipo de nome Bosan Slavich sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) Se k > 0 Então selecione Case Mid$(sv(i), k + 1) Case "ogly" , "kyzy" , "zade" , "ugli" , "uul " , "ool" "Variante de nasab "-ogly" e "-zade" tipo Mahmud-ogly sО = UCase(Left$(sv(i), 1)) & "." Então sИ = sО sО = vbNullString End If End Selecione ElseIf i > 2 Então selecione Case sv(i - 1) Case "ibn" , "bin" , "bin" sО = UCase(Left$(sv(i), 1) ) & "." " Usertal Alisher bin Suleiman i = i - 2 End Select Else " Ben Eduard sИ = UCase(Left$(sv(i), 1)) If Len(sv(i)) > 1 Then sИ = sИ & " ." i = i - 1 Fim Se Fim Selecionar Fim Selecionar Caso sv(0) Caso "de" , "del" , "dos" , "sept" , "van" , "von" , "tsu" Se i > = 2 Então sФ = sv(0) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "De Nikolai If Len(sИ) > 0 Então sФ = sv(0 ) & " " & StrConv(sv(1), vbProperCase) Else sФ = StrConv(sv(0), vbProperCase): sИ = СropWord(sv(1)) End If End If Case Else If Len(sФ) = 0 Then "More não definiu o sobrenome sФ = StrConv(sv(0), vbProperCase) If Len(sИ) = 0 Then sИ = СropWord(sv(1)) End If End Select If ToLeft Then Initials = sИ & sО & " " & sФ Else Iniciais = sФ & " " & sИ & sО Função Final Função Pública СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1) ) & "." : k = InStr(s, "-" ) Se k > 0 Então ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." Função End If End
Uma lista no Excel pode ser corrigida usando fórmulas - substituindo nomes e nomes do meio por iniciais, combinando palavras de células em uma frase, inserindo palavras em uma lista do Excel.
Temos uma tabela onde o sobrenome, nome e patronímico estão escritos em células diferentes. Precisamos colocá-los em uma célula. Reescrever manualmente a lista leva muito tempo. Mas, na tabela do Excel, existe uma função especial. Existem duas opções.
Primeira opção.
Nós temos essa lista.
Precisamos escrever nosso nome completo na célula D1 em uma frase. Escrevemos a fórmula nesta célula (D1). Clique na célula (torne-a ativa).
Vá para a aba “Fórmulas” na seção “Biblioteca de Funções”, selecione “Texto” e selecione a função “CONCATENAR”. Na janela que aparece, indicamos os endereços das células que precisamos combinar em uma frase. Aconteceu assim. 
O nome completo é escrito sem espaços. Para corrigir isso, a fórmula precisa ser melhorada. Entre os endereços das células, após o ponto e vírgula, escreva " ". O resultado é a seguinte fórmula.
=CONCATENAR(A1;" ";B1;" ";C1)
Aconteceu assim.
Agora copie a fórmula na coluna.
Segunda opçao.
Em vez da função CONCATENATE, você pode simplesmente pressionar o botão E comercial (&). A fórmula ficará assim.
=A2 e B2 e C1
O resultado é o mesmo da primeira opção. Se não houver espaços entre as palavras, insira um espaço (" ").
A fórmula será assim. =A2&" "&B2&" "&C2
Você pode combinar não apenas palavras, mas também números. Você pode fazer uma frase a partir de dados de células no Excel.
Você pode definir fórmulas nas células obrigatórias do formulário. Por exemplo, temos uma lista de clientes com endereços. 
Precisamos fazer uma proposta. Escrevemos a fórmula na célula.
=A2&" "&B2&" "&C2&" "&"mora em"&" "&"g."&" "&D2&" "&"st."&" "&E2&" "&"d."&" "&F2& " ."
Esta foi a proposta. 
Utilizamos este princípio para elaborar quaisquer propostas.
Se o texto nas células já foi escrito, mas precisamos inserir palavras adicionais antes das existentes, isso pode ser feito por meio de uma fórmula. Nós temos essa lista. 
Precisamos inserir a palavra “Inquilino” antes dos sobrenomes. Na célula da nova coluna escrevemos a fórmula.
="Inquilino"&" "&A8
Copie esta fórmula na coluna. O resultado é a lista a seguir. 
A primeira coluna pode ser ocultada ou o valor de uma nova coluna sem fórmulas pode ser copiado, e a primeira coluna e a segunda com fórmulas podem ser excluídas.
Para outra forma de adicionar texto, números, símbolos ao texto em uma célula, consulte o artigo “Adicionar texto a células com texto do Excel”.
Usando fórmulas, você pode converter uma lista onde o nome, o nome do meio e o sobrenome são escritos por extenso em uma lista com sobrenome e iniciais. Por exemplo, a célula diz: 
Na próxima coluna escrevemos a seguinte fórmula.
=CONCATENAR(ESQUERDA(ESPAÇO(A1),ENCONTRAR(" ",ESPAÇO(A1),1)),PSTR(ESPAÇO(A1),ENCONTRAR(" ",ESPAÇO(A1),1)+1,1);" .";PSTR(ESPAÇO(A1);FIND(" ";ESPAÇO(A1); ENCONTRAR(" ";ESPAÇO(A1);1)+1)+1;1);."")
Ocorrido.
Se houver espaços extras entre as palavras, você poderá removê-los. Leia mais sobre isso no artigo “Como remover espaços extras no Excel”. Usando os mesmos métodos, você pode remover espaços entre números em uma fórmula, porque Espaços extras podem levar a um erro no cálculo ou a fórmula não será contabilizada.
Você pode mover os dados em uma linha das últimas células para a primeira ou inverter a linha. Por exemplo, nas células está escrito: na primeira célula está Ivanov, na segunda - Maria. Precisamos escrever Maria na primeira cela, Ivanova na segunda. Como fazer isso rapidamente em uma mesa grande, veja o artigo "".