O Excel tem um conjunto de funções TEXT que podem fazer maravilhas. Você pode fazer todos os tipos de fatias de texto e operações de dados usando essas funções.
Uma das tarefas comuns para pessoas que trabalham com dados de texto é extrair uma substring no Excel (ou seja, obter o psrt do texto de uma célula).
Infelizmente, não há função de substring no Excel que possa fazer isso facilmente. No entanto, isso ainda pode ser feito usando fórmulas de texto, bem como alguns outros recursos embutidos do Excel.
Vamos primeiro dar uma olhada em algumas das funções de texto que usaremos neste tutorial.
Funções Excel TEXT
O Excel possui uma gama de funções de texto que tornariam realmente fácil extrair uma substring do texto original no Excel. Aqui estão as funções de texto do Excel que usaremos neste tutorial:
- Função RIGHT: Extrai o número especificado de caracteres da direita da string de texto.
- Função LEFT: extrai o número especificado de caracteres da esquerda da string de texto.
- Função MID: Extrai o número especificado de caracteres da posição inicial especificada em uma string de texto.
- Função FIND: Encontra a posição inicial do texto especificado na string de texto.
- Função LEN: Retorna o número de caracteres na string de texto.
Extraia uma substring no Excel usando funções
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Estes são alguns ids de e-mail aleatórios (mas de super-heróis) (exceto o meu) e, nos exemplos abaixo, mostrarei como extrair o nome de usuário e o nome de domínio usando as funções de texto no Excel.
Exemplo 1 - Extração de nomes de usuário de IDs de e-mail
Ao usar funções de texto, é importante identificar um padrão (se houver). Isso torna muito fácil construir uma fórmula. No caso acima, o padrão é o sinal @ entre o nome de usuário e o nome de domínio, e vamos usá-lo como uma referência para obter os nomes de usuário.
Esta é a fórmula para obter o nome de usuário:
= ESQUERDA (A2, ENCONTRAR ("@", A2) -1)
A fórmula acima usa a função LEFT para extrair o nome de usuário, identificando a posição do sinal @ no id. Isso é feito usando a função FIND, que retorna a posição do @.
Por exemplo, no caso de [email protected], FIND (“@”, A2) retornaria 11, que é sua posição na string de texto.
Agora usamos a função LEFT para extrair 10 caracteres da esquerda da string (um a menos que o valor retornado pela função LEFT).
Exemplo 2 - Extraindo o nome de domínio de IDs de e-mail
A mesma lógica usada no exemplo acima pode ser usada para obter o nome de domínio. Uma pequena diferença aqui é que precisamos extrair os caracteres à direita da string de texto.
Aqui está a fórmula que fará isso:
= DIREITA (A2, LEN (A2) -FIND ("@", A2))
Na fórmula acima, usamos a mesma lógica, mas ajustamos para ter certeza de que estamos obtendo a string correta.
Tomemos novamente o exemplo de [email protected]. A função FIND retorna a posição do sinal @, que é 11 neste caso. Agora, precisamos extrair todos os caracteres após o @. Assim, identificamos o comprimento total da string e subtraímos o número de caracteres até o @. Ele nos dá o número de caracteres que cobrem o nome de domínio à direita.
Agora podemos simplesmente usar a função RIGHT para obter o nome de domínio.
Exemplo 3 - Extração do nome de domínio de IDs de e-mail (sem .com)
Para extrair uma substring do meio de uma string de texto, você precisa identificar a posição do marcador antes e depois da substring.
Por exemplo, no exemplo abaixo, para obter o nome de domínio sem a parte .com, o marcador seria @ (que está logo antes do nome de domínio) e. (que está logo após).
Aqui está a fórmula que extrairá apenas o nome de domínio:
= MID (A2, FIND ("@", A2) + 1, FIND (".", A2) -FIND ("@", A2) -1)
A função Excel MID extrai o número especificado de caracteres da posição inicial especificada. Neste exemplo acima, FIND ("@", A2) +1 especifica a posição inicial (que está logo após @), e FIND (".", A2) -FIND ("@", A2) -1 identifica o número de caracteres entre o '@' e a '.‘
Atualizar: Um dos leitores William19 mencionou que a fórmula acima não funcionaria caso houvesse um ponto (.) No id do e-mail (por exemplo, [email protected]). Então, aqui está a fórmula para lidar com esses casos:
= MID (A1, FIND ("@", A1) + 1, FIND (".", A1, FIND ("@", A1)) - FIND ("@", A1) -1)
Usando texto em colunas para extrair uma substring no Excel
O uso de funções para extrair uma substring no Excel tem a vantagem de ser dinâmico. Se você alterar o texto original, a fórmula atualizará automaticamente os resultados.
Se isso for algo de que você não precisa, usar o recurso Texto para colunas pode ser uma maneira rápida e fácil de dividir o texto em substrings com base em marcadores específicos.
Aqui está como fazer isso:
- Selecione as células onde você tem o texto.
- Vá para Dados -> Ferramentas de Dados -> Texto para Colunas.
- Na etapa 1 do Assistente de texto para coluna, selecione Delimitado e pressione Avançar.
- Na Etapa 2, marque a opção Outro e digite @ na caixa à direita. Este será o nosso delimitador que o Excel usaria para dividir o texto em substrings. Você pode ver a visualização dos dados abaixo. Clique em Avançar.
- Na Etapa 3, a configuração geral funciona bem neste caso. No entanto, você pode escolher um formato diferente se estiver dividindo números / datas. Por padrão, a célula de destino é onde você tem os dados originais. Se você quiser manter os dados originais intactos, mude para alguma outra célula.
- Clique em Concluir.
Isso lhe dará instantaneamente dois conjuntos de substrings para cada id de e-mail usado neste exemplo.
Se quiser dividir ainda mais o texto (por exemplo, dividir batman.com em batman e com), repita o mesmo processo com ele.
Usando FIND e REPLACE para extrair texto de uma célula no Excel
FIND e REPLACE podem ser uma técnica poderosa quando você está trabalhando com texto no Excel. Nos exemplos abaixo, você aprenderá a usar FIND e REPLACE com caracteres curinga para fazer coisas incríveis no Excel.
Veja também: Saiba tudo sobre caracteres curinga no Excel.
Vamos pegar os mesmos exemplos de ids de e-mail.
Exemplo 1 - Extração de nomes de usuário de IDs de e-mail
Aqui estão as etapas para extrair nomes de usuário de IDs de e-mail usando a funcionalidade Localizar e substituir:
- Copie e cole os dados originais. Como Find and Replace funciona e altera os dados nos quais é aplicado, é melhor fazer um backup dos dados originais.
- Selecione os dados e vá para Home -> Editing -> Find & Select -> Replace (ou use o atalho de teclado Ctrl + H).
- Na caixa de diálogo Localizar e substituir, digite o seguinte:
- Descubra o que: @ *
- Substitua por: (deixe em branco)
- Clique em Substituir tudo.
Isso removerá instantaneamente todo o texto antes do @ nos ids de e-mail. Você terá o resultado conforme mostrado abaixo:
Como isso funciona ?? - No exemplo acima, usamos uma combinação de @ e *. Um asterisco (*) é um caractere curinga que representa qualquer número de caracteres. Portanto, @ * significaria uma string de texto que começa com @ e pode ter qualquer número de caracteres depois dela. Por exemplo, em [email protected], @ * seria @ batman.com. Quando substituímos @ * por um espaço em branco, ele remove todos os caracteres após @ (incluindo @).
Exemplo 2 - Extraindo o nome de domínio de IDs de e-mail
Usando a mesma lógica, você pode modificar os critérios "Encontrar o quê" para obter o nome de domínio.
Aqui estão as etapas:
- Selecione os dados.
- Vá para Home -> Editing -> Find & Select -> Replace (ou use o atalho de teclado Ctrl + H).
- Na caixa de diálogo Localizar e substituir, digite o seguinte:
- Descubra o que: * @
- Substitua por: (deixe em branco)
- Clique em Substituir tudo.
Isso removerá instantaneamente todo o texto antes do @ nos ids de e-mail. Você terá o resultado conforme mostrado abaixo:
