Ao trabalhar com dados de texto no Excel, poderá ter a necessidade de remover o texto antes ou depois de um carácter específico ou string de texto.
Por exemplo, se tiver o nome e dados de designação de pessoas, poderá querer remover a designação depois da vírgula e manter apenas o nome (ou vice-versa, onde manter a designação e remover o nome).
Por vezes, pode ser feito com uma fórmula simples ou uma rápida Localizar e Substituir, e por vezes, necessita de fórmulas ou soluções mais complexas.
Neste tutorial, mostrar-lhe-ei como remover o texto antes ou depois de um carácter específico no Excel (usando diferentes exemplos).
Por isso, vamos começar com alguns exemplos simples.
Este Tutorial Cobre:
Remove Text After a Character Using Find and Replace
Se quiser remover rapidamente todo o texto após uma cadeia de texto específica (ou antes de uma cadeia de texto), pode fazê-lo usando caracteres Find and Replace e wild card.
Se quiser remover a designação após o caractere vírgula e manter o texto antes da vírgula, deverá ter um conjunto de dados como mostrado abaixo.
p>
Below são os passos para o fazer:
- Copiar e colar os dados da coluna A à coluna B (isto é para manter os dados originais também)
- Com as células da coluna B seleccionadas, clique na guia Home
- No grupo Edição, clique na opção Procurar & Seleccione a opção
- Nas opções que aparecem no menu drop-down, clique na opção Substituir. Isto abrirá a caixa de diálogo Encontrar e Substituir
- No campo ‘Encontrar o quê’, introduza ,* (i.e, vírgula seguida de um asterisco)
- Deixe o campo ‘Replace with’ vazio
- Click on the Replace All button
Os passos acima referidos encontrariam a vírgula no conjunto de dados e removeriam todo o texto após a vírgula (incluindo a vírgula).
P>Desde que isto substitui o texto das células seleccionadas, é uma boa ideia copiar o texto para outra coluna e depois realizar esta operação de procura e substituição, ou criar uma cópia de segurança dos seus dados para que tenha os dados originais intactos
Como é que isto funciona?
* (sinal de asterisco) é um wildcard que pode representar qualquer número de caracteres.
Quando o utilizo depois da vírgula (no campo ‘Find what’) e depois clico no botão ‘Replace All’, encontra a primeira vírgula na célula e considera-a compatível.
Isto porque o sinal de asterisco (*) é considerado compatível com toda a cadeia de texto que segue a vírgula.
Então quando carrega no botão Substituir tudo, ele substitui a vírgula e todo o texto que se segue.
Caso queira remover todos os caracteres antes da vírgula, altere a entrada do campo find find, tendo o sinal asterisco antes da vírgula (*, em vez de ,*)
Remove Text Using Formulas
Se precisar de mais controlo sobre como encontrar e substituir o texto antes ou depois de um caractere específico, é melhor usar as fórmulas de texto incorporadas no Excel.
P>Se pretender remover todo o texto após a vírgula, deverá ter o conjunto de dados abaixo.
Below é a fórmula para o fazer:
=LEFT(A2,FIND(",",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 utilizado pela função ESQUERDA para extrair todos os caracteres antes da vírgula. Uma vez que não quero vírgula como parte do resultado, subtraí 1 do valor resultante da fórmula Find.
Este foi um cenário simples.
P>P>Passemos por um ligeiramente complexo.
P>Suponha que tenho este conjunto de dados abaixo onde quero remover todo o texto após a segunda vírgula.
Aqui está a fórmula que o fará:
=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",2))-1)
P>P>Posto que este conjunto de dados tem múltiplas vírgulas, não posso usar a função FIND para obter a posição da primeira vírgula e extrair tudo à esquerda da mesma.
P>Tenho de descobrir de alguma forma a posição da segunda vírgula e depois extrair tudo o que está à esquerda da segunda vírgula.
Para o fazer, utilizei a função SUBSTITUIR para alterar a segunda vírgula para um ponto de exclamação. Isto dá-me agora um carácter ú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 ESQUERDA 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 eu preciso de extrair todo o texto antes da última vírgula.
Conjunto de dados com múltiplas vírgulas
Neste caso, preciso de alguma forma de descobrir a posição da última ocorrência da vírgula e depois extrair tudo à esquerda da mesma.
Below é 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 estes dois valores, dá-me o número total de vírgulas na célula.
Assim, dá-me 3 para a célula A2 e 2 para a célula A4.
Este valor é então utilizado dentro da fórmula SUBSTITUTO para substituir a última vírgula por um ponto de exclamação. E depois pode usar a função esquerda extrair tudo o que está à esquerda do ponto de exclamação (que é onde costumava estar a última vírgula)
Como se pode ver nos exemplos, usando uma combinação de fórmulas de texto permite lidar com muitas situações diferentes.
Também, uma vez que o resultado está ligado aos dados originais, ao alterar os dados originais o resultado seria automaticamente actualizado.
Remove Text Using Flash Fill
Flash Fill é uma ferramenta que foi introduzida no Excel 2013 e está disponível em todas as versões depois disso.
Funciona identificando padrões quando se introduzem manualmente os dados e depois extrapolados para lhe dar os dados para toda a coluna.
Então, se quiser remover o texto antes ou depois de um carácter específico, basta mostrar à fada flash como seria o resultado (introduzindo-o manualmente algumas vezes), e o preenchimento flash compreenderia automaticamente o padrão e dar-lhe-ia os resultados.
Deixe-me mostrar-lhe isto com um exemplo.
Below Tenho o conjunto de dados onde quero remover todo o texto após a vírgula.
Aqui estão os passos para o fazer utilizando o Flash Fill:
- Na célula B2, que é a coluna adjacente dos nossos dados, introduzir manualmente ‘Jeffery Haggins’ (que é o resultado esperado)
- Na célula B3, introduzir ‘Tim Scott’ (que é o resultado esperado para a segunda célula)
- Seleccionar o intervalo B2:B10
- Clique no separador Home
- No grupo de Edição, clique na opção Preencher
- Clique no Flash Fill
Os passos acima dar-lhe-iam o resultado como mostrado abaixo:
Também pode usar o atalho de teclado Flash Fill Control + E após seleccionar as células na coluna de resultados (Coluna B no nosso exemplo)
Flash Fill é uma ferramenta maravilhosa e, na maioria dos casos, é capaz de reconhecer o padrão e dar-lhe o resultado certo. mas em alguns casos, pode não ser capaz de reconhecer o padrão correctamente e pode dar-lhe os resultados errados.
Por isso, certifique-se de verificar os resultados do Flash Fill
E tal como removemos todo o texto após um caractere específico usando o Flash Fill, pode usar os mesmos passos para remover o texto antes de um caractere específico. basta mostrar o Flash Fill como o resultado deve parecer-se com o meu Internet manualmente na coluna adjacente, e ele faria o resto.
Remove Text Using VBA (Custom Function)
Todo o conceito de remover o texto antes ou depois de um caractere específico depende de encontrar a posição desse caractere.
Como se viu acima, encontrar a última ocorrência desse caractere bom significa usar uma mistura de fórmulas.
Se isto é algo que precisa de fazer com bastante frequência, pode simplificar este processo criando uma função personalizada usando VBA (chamada Funções Definidas pelo Utilizador).
Após criada, pode reutilizar esta função uma e outra vez. É também muito mais simples e fácil de usar (uma vez que a maior parte do trabalho pesado é feito pelo código VBA no back end).
Below the VBA code that you can use to create the custom function in Excel:
Function LastPosition(rCell As Range, rChar As String)'This function gives the last position of the specified character'This code has been developed by Sumit Bansal (https://trumpexcel.com)Dim rLen As IntegerrLen = Len(rCell)For i = rLen To 1 Step -1If Mid(rCell, i - 1, 1) = rChar ThenLastPosition = i - 1Exit FunctionEnd IfNext iEnd Function
P>Tem de colocar o código VBA num módulo regular no Editor VB, ou na Pasta de Trabalho Macro Pessoal. Uma vez lá, pode utilizá-lo como qualquer outra função de folha de trabalho regular na caderneta de trabalho.
Esta função leva 2 argumentos:
- A referência da célula para a qual pretende encontrar a última ocorrência de um caracter ou string de texto específico
- A string de caracter ou string de texto cuja posição precisa de encontrar
P>Ponha agora que tem o conjunto de dados abaixo e pretende remover todo o texto após a última vírgula e ter apenas o texto antes da última vírgula.
Below é 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. Caso queira encontrar a posição de algum outro caracter ou cadeia de texto, deve usar isso como segundo argumento na função.
Como pode ver, esta é muito mais curta e fácil de usar, em comparação com a fórmula de texto longo que utilizámos na secção anterior
Se colocar o código VBA num módulo de uma pasta de trabalho só poderá usar esta função personalizada nessa pasta de trabalho específica. Se quiser usar isto em todas as pastas de trabalho do seu sistema, precisa de copiar e colar este código na Pasta de Trabalho Macro Pessoal.
Por isso estes são alguns dos mails que pode usar para remover rapidamente o texto antes ou depois de um carácter específico no Excel.
Se for uma coisa simples de uma só vez, então pode fazer isso usando encontrar e substituir. e se for um pouco mais complexo, então precisa de usar uma combinação de fórmulas Excel embutidas, ou mesmo criar a sua própria fórmula personalizada usando VBA.
Espero que tenha achado útil este tutorial.
Outros tutoriais Excel de que possa gostar:
- Contar caracteres numa célula (ou intervalo de células) usando fórmulas em Excel
- Como remover o primeiro carácter de uma corda em Excel
- Como remover vírgula em Excel (de Texto e Números)
- Como Extrair a Primeira Palavra de uma Cadeia de Texto em Excel
- Separar Nome e Apelido no Excel (Dividir Nomes Usando Fórmulas)