Ao trabalhar com dados de texto no Excel, pode ser necessário remover o texto antes ou depois de um caractere ou string de texto específico.
Por exemplo, se você tem o nome e os dados de designação de pessoas, pode querer remover a designação após a vírgula e apenas manter o nome (ou vice-versa, onde você mantém a designação e remove o nome).
Às vezes, isso pode ser feito com uma fórmula simples ou um Localizar e substituir rápido e, às vezes, precisa de fórmulas ou soluções alternativas mais complexas.
Neste tutorial, vou mostrar como remover o texto antes ou depois de um caractere específico no Excel (usando diferentes exemplos).
Então, vamos começar com alguns exemplos simples.
Remover texto após um caractere usando localizar e substituir
Se quiser remover rapidamente todo o texto após uma sequência de texto específica (ou antes de uma sequência de texto), você pode fazer isso usando Localizar e Substituir e caracteres curinga.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja remover a designação após o caractere de vírgula e manter o texto antes da vírgula.
Abaixo estão as etapas para fazer isso:
- Copie e cole os dados da coluna A na coluna B (isso também mantém os dados originais)
- Com as células da coluna B selecionadas, clique na guia Home
- No grupo Editing, clique na opção Find & Select
- Nas opções que aparecem no menu suspenso, clique na opção Substituir. Isso abrirá a caixa de diálogo Localizar e Substituir
- No campo ‘Encontrar o quê’, digite ,* (ou seja, vírgula seguida por um sinal de asterisco)
- Deixe o campo ‘Substituir por’ vazio
- Clique no botão Substituir tudo
As etapas acima localizariam a vírgula no conjunto de dados e removeriam todo o texto após a vírgula (incluindo a vírgula).
Como isso substitui o texto das células selecionadas, é uma boa ideia copiar o texto em outra coluna e, em seguida, executar esta operação de localizar e substituir ou criar uma cópia de backup de seus dados para que você tenha os dados originais intactos
Como é que isso funciona?
* (sinal de asterisco) é um caractere curinga que pode representar qualquer número de caracteres.
Quando eu o uso após a vírgula (no campo "Encontrar o que") e, em seguida, clico no botão Substituir tudo, ele encontra a primeira vírgula na célula e a considera uma correspondência.
Isso ocorre porque o sinal de asterisco (*) é considerado uma correspondência para toda a sequência de texto que segue a vírgula.
Portanto, quando você clica no botão substituir tudo, ele substitui a vírgula e todo o texto que se segue.
Observação: Este método funciona bem, então você só tem uma vírgula em cada célula (como em nosso exemplo). No caso de você ter várias vírgulas, este método sempre encontrará a primeira vírgula e removerá tudo depois dela. Portanto, você não pode usar este método se quiser substituir todo o texto após a segunda vírgula e manter o primeiro como está.Caso você queira remover todos os caracteres antes da vírgula, altere a entrada do campo find what colocando o sinal de asterisco antes da vírgula (*, em vez de *)
Remover texto usando fórmulas
Se você precisa de mais controle sobre como localizar e substituir texto antes ou depois de um caractere específico, é melhor usar as fórmulas de texto embutidas no Excel.
Suponha que você tenha o conjunto de dados abaixo Onde deseja remover todo o texto após a vírgula.
Abaixo está a fórmula para fazer isso:
= ESQUERDA (A2, ENCONTRAR (",", A2) -1)
A fórmula acima usa a função FIND para encontrar a posição da vírgula na célula.
Este número de posição é então usado pela função LEFT para extrair todos os caracteres antes da vírgula. Já que não quero a vírgula como parte do resultado, subtraí 1 do valor resultante da fórmula Encontrar.
Este era um cenário simples.
Vamos pegar um um pouco complexo.
Suponha que eu tenha o conjunto de dados abaixo onde desejo remover todo o texto após a segunda vírgula.
Aqui está a fórmula que fará isso:
= ESQUERDA (A2, ENCONTRAR ("!", SUBSTITUIR (A2, ",", "!", 2)) - 1)
Como esse conjunto de dados tem várias vírgulas, não posso usar a função FIND para obter a posição da primeira vírgula e extrair tudo à esquerda dela.
Preciso descobrir de alguma forma a posição da segunda vírgula e extrair tudo o que está à esquerda da segunda vírgula.
Para fazer isso, usei a função SUBSTITUTE para alterar a segunda vírgula para um ponto de exclamação. Agora, isso me dá um caráter único na célula. Agora posso usar a posição do ponto de exclamação para extrair tudo à esquerda da segunda vírgula.
Esta posição do ponto de exclamação é usada na função LEFT para extrair tudo antes da segunda vírgula.
Até agora tudo bem!
Mas e se houver um número inconsistente de vírgulas no conjunto de dados.
Por exemplo, no conjunto de dados abaixo, algumas células têm duas vírgulas e algumas células têm três vírgulas, e preciso extrair todo o texto antes da última vírgula.
Nesse caso, preciso descobrir de alguma forma a posição da última ocorrência da vírgula e extrair tudo à esquerda dela.
Abaixo está a fórmula que fará isso
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))) - 1)
A fórmula acima usa a função LEN para encontrar o comprimento total do texto na célula, bem como o comprimento do texto sem a vírgula.
Quando subtraio esses dois valores, obtenho o número total de vírgulas na célula.
Então, isso me daria 3 para a célula A2 e 2 para a célula A4.
Esse valor é então usado na fórmula SUBSTITUTE para substituir a última vírgula por um ponto de exclamação. E então você pode usar a função da esquerda para extrair tudo o que está à esquerda do ponto de exclamação (que é onde a última vírgula costumava estar)
Como você pode ver nos exemplos, o uso de uma combinação de fórmulas de texto permite lidar com muitas situações diferentes.
Além disso, como o resultado está vinculado aos dados originais, quando você altera os dados originais, o resultado é atualizado automaticamente.
Remover texto usando Flash Fill
Flash Fill é uma ferramenta que foi introduzida no Excel 2013 e está disponível em todas as versões posteriores.
Funciona identificando padrões quando você insere manualmente os dados e, em seguida, extrapola para fornecer os dados de toda a coluna.
Então, se você quiser remover o texto antes ou depois de um caractere específico, você só precisa mostrar a fada do flash como seria o resultado (inserindo-o manualmente algumas vezes), e o preenchimento do flash automaticamente entenderia o padrão e lhe daria os resultados.
Deixe-me mostrar isso com um exemplo.
Abaixo, tenho o conjunto de dados onde desejo remover todo o texto após a vírgula.
Aqui estão as etapas para fazer isso usando o Flash Fill:
- Na célula B2, que é a coluna adjacente de nossos dados, insira manualmente ‘Jeffery Haggins’ (que é o resultado esperado)
- Na célula B3, digite 'Tim Scott' (que é o resultado esperado para a segunda célula)
- Selecione o intervalo B2: B10
- Clique na guia Home
- No grupo de edição, clique na opção suspensa Preencher
- Clique em Flash Fill
As etapas acima dariam o resultado conforme mostrado abaixo:
Você também pode usar o atalho de teclado Flash Fill Control + E depois de selecionar as células na coluna de resultado (coluna B em nosso exemplo)
O Flash Fill é uma ferramenta maravilhosa e, na maioria dos casos, é capaz de reconhecer o padrão e fornecer o resultado certo. mas, em alguns casos, pode não ser capaz de reconhecer o padrão corretamente e pode fornecer resultados errados.
Portanto, certifique-se de verificar novamente os resultados do Flash Fill
E assim como removemos todo o texto após um caractere específico usando o preenchimento flash, você pode usar as mesmas etapas para remover o texto antes de um caractere específico. apenas mostre o preenchimento instantâneo como o resultado deve se parecer com a minha Internet manualmente na coluna adjacente, e ele fará o resto.
Remover texto usando VBA (função personalizada)
Todo o conceito de remover o texto antes ou depois de um caractere específico depende de encontrar a posição desse caractere.
Como visto acima, achar que a última ocorrência desse caractere é boa significa usar uma mistura de fórmulas.
Se isso for algo que você precisa fazer com frequência, simplifique o processo criando uma função personalizada usando o VBA (chamado Funções definidas pelo usuário).
Uma vez criado, você pode reutilizar esta função novamente e novamente. Também é muito mais simples e fácil de usar (já que a maior parte do trabalho pesado é feito pelo código VBA no back-end).
Abaixo do código VBA que você pode usar para criar a função personalizada no Excel:
Função LastPosition (rCell As Range, rChar As String) 'Esta função fornece a última posição do caractere especificado' Este código foi desenvolvido por Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
Você precisa colocar o código VBA em um módulo regular no Editor VB ou na Pasta de Trabalho Pessoal de Macros. Assim que estiver lá, você pode usá-lo como qualquer outra função de planilha regular na pasta de trabalho.
Esta função leva 2 argumentos:
- A referência de célula para a qual você deseja encontrar a última ocorrência de um caractere específico ou string de texto
- O caractere ou string de texto cuja posição você precisa encontrar
Suponha que agora você tenha o conjunto de dados abaixo e deseja remover todo o texto após a última vírgula e apenas o texto antes da última vírgula.
Abaixo está a fórmula que fará isso:
= LEFT (A2, LastPosition (A2, ",") - 1)
Na fórmula acima, especifiquei para encontrar a posição da última vírgula. No caso de você querer encontrar a posição de algum outro caractere ou string de texto, você deve usar isso como o segundo argumento na função.
Como você pode ver, isso é muito mais curto e fácil de usar, em comparação com a fórmula de texto longo que usamos na seção anterior
Se você colocar o código VBA em um módulo em uma pasta de trabalho, só poderá usar essa função personalizada nessa pasta de trabalho específica. Se você deseja usar isso em todas as pastas de trabalho em seu sistema, você precisa copiar e colar este código na pasta de trabalho macro pessoal.
Portanto, esses são alguns dos e-mails que você pode usar para remover rapidamente o texto antes ou depois de um caractere específico no Excel.
Se for algo simples, você pode fazer isso usando localizar e substituir. e se for um pouco mais complexo, você precisará usar uma combinação de fórmulas do Excel embutidas ou até mesmo criar sua própria fórmula personalizada usando o VBA.
Espero que você tenha achado este tutorial útil.
