Técnicas de substring no Excel: extraia e formate texto

Extraindo texto por posição usando funções de substring do Excel

Muitas vezes queremos extrair partes específicas de uma string de texto, como uma palavra ou uma sequência de caracteres, com base na posição. O Excel oferece várias funções nativas para ajudar você a extrair trechos específicos de texto. As mais comuns são LEFT(), RIGHT(), MID(), TEXTBEFORE() e TEXTAFTER(). Vamos ver cada uma delas. 

Usando a função LEFT() no Excel

Você pode usar a função LEFT() para extrair caracteres do início de uma string de texto. A sintaxe é:

=LEFT(text, [num_chars])

Aqui: 

• text se refere ao endereço da célula onde está a string original.

• num_chars é o número de caracteres a serem extraídos. O padrão é 1.

Vamos a um exemplo. Tenho alguns códigos de produto e quero extrair os três primeiros caracteres.

=LEFT(A2,3)

Busque os três primeiros caracteres com a função LEFT(). Imagem do autor.

Usando a função RIGHT() no Excel

A função RIGHT() no Excel extrai caracteres do final de uma string de texto. A sintaxe é:

=Right(text, [num_chars])

Aqui: 

• text se refere ao endereço da célula onde está a string original.

• num_chars é o número de caracteres a serem extraídos. O padrão é 1.

Veja o exemplo: uso a função RIGHT() para extrair os três últimos caracteres.

=RIGHT(A2, 3)

Busque os três últimos caracteres com a função RIGHT(). Imagem do autor.

Usando a função MID() no Excel

A função MID() extrai caracteres do meio de uma string de texto. A sintaxe é:

=MID(text, start_num, num_chars)

Aqui:

• text se refere ao endereço da célula onde está a string original.

• num_chars é o número de caracteres a serem extraídos. O padrão é 1.

• start_num é a posição inicial dentro da string.

Neste exemplo, uso a fórmula MID() para extrair o valor do meio da string a partir do quarto caractere.

=MID(A2,4,3)

Busque três caracteres do meio com a função MID(). Imagem do autor. 

Pronto. =MID(A2,4,3) extrai três caracteres do texto na célula A2, começando no 4º caractere.

Usando a função FIND() no Excel

A função FIND() no Excel localiza uma substring dentro de uma string de texto e retorna a posição do primeiro caractere dessa substring. A sintaxe é:

FIND(find_text, within_text, [start_num]) 

Aqui:

• find_text é o texto que queremos procurar.

• within_text é o texto que contém o que queremos encontrar.

• start_num indica a posição para começar a busca, com padrão 1.

Vamos considerar um conjunto de dados simples para mostrar como a função FIND() funciona. Neste exemplo, tenho o nome DataCamp na coluna Text e quero localizar a posição da substring Camp. Isso retorna 5 porque Camp começa no quinto caractere do texto.

=FIND("Camp", A2)

Usando a função FIND() para obter a posição do texto. Imagem do autor.

Usando a função SUBSTITUTE() no Excel

A função SUBSTITUTE() substitui uma substring específica por outra. A sintaxe é:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Aqui:

• text contém o texto que você quer alterar.

• old_text é o texto que você quer substituir.

• new_text é o texto pelo qual você vai substituir o anterior.

• instance_num especifica qual ocorrência do texto você quer alterar. Se não informar, todas as ocorrências serão substituídas. 

Para entender, veja um conjunto simples de strings com delimitadores específicos. A coluna Text lista nomes e idades separados por ponto e vírgula. Para limpar, digito a fórmula:

=SUBSTITUTE(A2, ";" , ",")

Aqui:

• A2 é a célula com o texto original.

• ";" é o texto antigo que vamos trocar.

• "," é o novo texto para substituir.

Usando a função SUBSTITUTE para trocar o texto. Imagem do autor.

E pronto. Ao aplicar a fórmula na célula B2, o Excel substitui o ponto e vírgula por vírgula.

Extraindo texto por delimitador usando funções de substring do Excel

Delimitadores são caracteres específicos que separam duas strings de texto, como ponto e vírgula ou vírgula. No Excel, você pode usar as funções TEXTBEFORE() e TEXTAFTER() para extrair texto quando há delimitadores.  

Usando a função TEXTBEFORE() no Excel 

Como o nome sugere, a função TEXTBEFORE() extrai o texto que está antes de um caractere ou substring específicos. A sintaxe é:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Aqui: 

• text é o texto original.

• delimiter geralmente é uma vírgula ou um ponto e vírgula.

• instance_num é a ocorrência desejada do delimitador. O padrão é 1.

• match_mode indica se a busca pelo delimitador deve diferenciar maiúsculas de minúsculas (TRUE, padrão) ou não (FALSE).

• match_end determina se o fim do texto deve ser tratado como delimitador. Se TRUE, a função retorna o texto original se o delimitador não for encontrado.

• if_not_found define um valor personalizado para retornar se o delimitador não for encontrado.

Agora, vamos ver um exemplo prático. Tenho uma coluna Text com dados de funcionários, incluindo nome, departamento e filial. Quero extrair apenas nomes e departamentos.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Usando TEXTBEFORE() para extrair dados. Imagem do autor 

Como funciona: 

• A2 contém o texto do qual quero extrair.

• "," é o delimitador que separa o texto nos dados originais. 

• 2 indica que a função vai considerar a segunda ocorrência do delimitador.

• 1 ignora diferenças de maiúsculas e minúsculas, se houver.

• 1 (no final) retorna o texto original se o delimitador não existir.

Usando a função TEXTAFTER() no Excel

A função TEXTAFTER() é semelhante à TEXTBEFORE() — a única diferença está no resultado. TEXTAFTER() extrai o texto que vem após um delimitador. A sintaxe é:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Aqui: 

• text se refere ao texto original.

• delimiter é uma vírgula ou ponto e vírgula. 

• instance_num é a ocorrência desejada do delimitador. O padrão é 1.

• match_mode indica se a busca deve diferenciar maiúsculas de minúsculas (TRUE, padrão) ou não (FALSE).

• match_end determina se o fim do texto deve ser tratado como delimitador. Se TRUE, a função retorna o texto original se o delimitador não for encontrado.

• if_not_found define um valor personalizado caso o delimitador não seja encontrado.

Aqui, tenho uma coluna Text com detalhes de funcionários, incluindo nomes, departamentos e filiais. Quero extrair as localidades e os IDs.

=TEXTAFTER(A2,",",2,1,1)

Usando a função TEXTAFTER() para extrair dados. Imagem do autor 

Como funciona: 

• A2 contém o texto do qual quero extrair.

• ","  é o delimitador que separa o texto nos dados originais. 

• 2 indica que a função vai considerar a segunda ocorrência do delimitador. 

• 1 ignora diferenças de maiúsculas e minúsculas, se houver.

• 1 (no final) retorna o texto original se o delimitador não existir.

Casos de uso específicos de substring no Excel

Agora que você conhece o básico de substrings, é hora de ver métodos avançados. Eles permitem manipulações de texto mais complexas, deixando seu processamento de dados ainda mais eficiente. 

Lidando com comprimentos variados de primeiros nomes

Tenho um conjunto de dados com o nome completo, mas quero extrair apenas o primeiro nome.

Uma tabela com nomes completos. Imagem do autor.

Você pode pensar que é simples — usar a função LEFT() para pegar o primeiro nome. Então, tentei com a fórmula:

=LEFT(A2,4)

Usando LEFT() para buscar o primeiro nome. Imagem do autor.

Mas, em vez de obter o primeiro nome, peguei apenas os quatro primeiros caracteres, porque defini num_char como 4, já que Jane tem quatro caracteres. Ao copiar a fórmula, não tive o resultado desejado para outros nomes.

Preciso de algo mais flexível. Para extrair o primeiro nome independentemente do tamanho, você pode combinar LEFT() com FIND(). Uso FIND() para achar a posição do primeiro espaço no nome completo, que separa nome e sobrenome. Em seguida, uso LEFT() para extrair até essa posição. 

=LEFT(A2,FIND(" ",A2)-1)

Combine FIND() e LEFT() para extrair o primeiro nome. Imagem do autor.

Vamos dissecar a fórmula:

• A célula A2 contém o nome completo.

• FIND(" ", A2, 1) encontra a posição do primeiro espaço no texto da célula A2, começando do primeiro caractere.

• -1 é usado para remover o espaço.

• LEFT(A2, ...) extrai a quantidade de caracteres especificada a partir da esquerda.

Extraindo o domínio de um endereço de e-mail

Vamos a outro exemplo para ver se LEFT() e FIND() resolvem outro cenário. Tenho um conjunto de endereços de e-mail dos quais preciso extrair o domínio.

Dados contendo endereços de e-mail. Imagem do autor.

Para isso, crio outra coluna, Email Domain, e digito a fórmula abaixo em uma célula, copiando para as demais:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Extraia o domínio com RIGHT(), LEN() e FIND(). Imagem do autor.

Pronto — tenho todos os domínios. Entenda como o Excel chega ao resultado:

• FIND("@", A2) localiza a posição do símbolo @ no e-mail.

• LEN(A2) calcula o tamanho total do endereço.

• LEN(A2) - FIND("@", A2) calcula quantos caracteres vêm após o @.

• Por fim, RIGHT(A2, LEN(A2) - FIND("@", A2)) extrai essa quantidade de caracteres a partir da direita.

Extraindo códigos de produto combinando LEFT(), MID() e RIGHT()

Até aqui, mostrei como LEFT(), MID() e RIGHT() funcionam separadamente. Mas você consegue muito mais combinando essas funções. Elas podem extrair partes diferentes de uma string com base em padrões específicos.

Tenho aqui uma lista de códigos de produto e quero extrair um trecho específico de cada um. 

• Da primeira seção, quero um caractere.
• Da segunda seção, quero todos os quatro caracteres.
• Da última seção, quero os três últimos caracteres.

Uma lista de códigos de produto. Imagem do autor.

Para isso, combino as funções LEFT(), RIGHT() e MID() e uso a fórmula:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Combine MID(), LEFT() e RIGHT() para extrair dados. Imagem do autor.

Como você vê, a fórmula combinada extraiu os caracteres desejados de cada seção dos códigos. Veja como:

• LEFT(A2, 1) extrai o primeiro caractere.

• MID(A2, FIND("-", A2) + 1, 4) encontra o primeiro hífen, avança um caractere e então extrai quatro caracteres.

• "-" adiciona um hífen.

• RIGHT(A2, 3) extrai os três últimos caracteres.

• & concatena os trechos. 

Usando SUBSTITUTE() com MID() para extração dinâmica de texto 

Agora, veja como combinar MID() com SUBSTITUTE() para extrair partes específicas de uma string e depois substituir caracteres ou substrings dentro do trecho extraído.

Por exemplo, tenho Order Details de clientes e quero substituir o ID do pedido pelo texto Order confirmed.

Uma tabela com detalhes de pedidos. Imagem do autor.

Uso a seguinte fórmula:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

SUBSTITUTE() e MID() para substituir texto. Imagem do autor.

É isso! Você vê que a fórmula extrai trechos específicos e faz a substituição como eu queria.

• MID(A2, 1, 19) extrai os 19 primeiros caracteres do texto em A2.

• SUBSTITUTE(text, old_text, new_text) substitui ocorrências de old_text por new_text.

Mais um exemplo: tenho a coluna Product Detail e quero extrair o tamanho do produto que está entre colchetes.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Extraindo o tamanho do produto entre colchetes. Imagem do autor.

E deu certo — extraiu todos os detalhes.

• FIND("[", A2) encontra a posição inicial do colchete.

• FIND("]", A2) encontra a posição final do colchete.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) extrai o texto entre os dois colchetes.

• SUBSTITUTE(...,"[", "") remove o colchete de abertura do texto extraído.

Limpando entradas de dados

Às vezes, os dados chegam desorganizados, mas isso não significa que você precise usá-los assim. Aqui tenho uma lista de números de telefone em vários formatos e quero padronizá-los, limpando as inconsistências.

Números de telefone sem formatação. Imagem do autor.

Para padronizar, crio outra coluna chamada Clean data. Digito a fórmula abaixo na segunda célula e copio para as demais onde quero ver os resultados:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Aqui: 

• SUBSTITUTE(A2, "-", "") remove hífens.

• SUBSTITUTE(..., "(", "") remove parênteses de abertura.

• SUBSTITUTE(..., ")", "") remove parênteses de fechamento.

• SUBSTITUTE(..., ".", "") remove pontos.

Isso transforma os vários formatos em uma sequência contínua de dígitos, como abaixo. Agora os dados estão limpos, bem formatados e prontos para uso. Você pode deixar assim, se quiser.

Limpe os dados usando a função SUBSTITUTE(). Imagem do autor.

Formatando texto para relatórios

Se quiser ir além da limpeza e já formatar os dados, você também pode usar diferentes funções de substring.

Considere o exemplo anterior, em que limpei os números de telefone. Agora quero mudar a formatação dos números para que pareçam telefones e não apenas uma sequência de dígitos. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Formato padronizado usando múltiplas substrings. Imagem do autor.

Pronto. Agora tenho todos os números no formato desejado. Veja como a fórmula funciona:

• LEFT(B2, 3) extrai os três primeiros dígitos.

• MID(B2, 4, 3) extrai os três dígitos seguintes a partir da quarta posição.

• RIGHT(B2, 4) extrai os quatro últimos dígitos.

• TEXT(..., "000") e TEXT(..., "0000") formatam cada parte para garantir a quantidade correta de dígitos, com zeros à esquerda se necessário.

• & "-" & une as partes formatadas com hífens.

Considerações finais

Você aprendeu a usar funções de substring do Excel como LEFT(), RIGHT(), MID(), TEXTBEFORE() e TEXTAFTER() para manipular texto. Seja para extrair partes específicas de uma string ou para limpar dados bagunçados, essas funções tornam seu trabalho muito mais simples e eficiente.

Mas sempre dá para ir além no Excel. O curso Introduction to Excel é o próximo passo perfeito para quem está começando. Se você quer aprender mais sobre análise de dados, experimente o curso Data Analysis in Excel, que combina muito bem com nossa Data Manipulation in Excel Cheat Sheet. Eles vão ajudar você a construir uma base sólida e levar suas habilidades para o próximo nível.