Funções RegexExtract, RegexTest e RegexReplace Excel

Como utilizar funções REGEX RegexExtract, RegexTest e RegexReplace no Excel, com exemplos e download gratuito.

O que é REGEX?

Regex ou expressões regulares são ferramentas para manipulação de texto, para identificar e substituir dados em textos.

Foi criada em 1950 por Stephen Kleene e é utilizado como parte essencial de muitas ferramentas de programação.

Elas fazem uso de uma cadeia de caracteres e coringas para definir quais informações devem ser localizadas em uma cadeia de caracteres.

Por exemplo:

CódigoDescriçãoExemploResultado
()Define um grupo de captura(abc) em “abcabc”Corresponde a abc
.Qualquer caractere, exceto nova linhaa.bCombina “a” seguido de qualquer caractere seguido de “b”
^Início da linha^abcCombina “abc” no início da linha
$Fim da linhaabc$Combina “abc” no fim da linha
\dDígito (0-9)\dCombina qualquer dígito
\DNão é um dígito\DCombina qualquer caractere que não seja dígito
\wPalavra (a-z, A-Z, 0-9, _)\wCombina qualquer caractere de palavra
\WNão é uma palavra\WCombina qualquer caractere que não seja de palavra
\sEspaço em branco\sCombina qualquer espaço em branco (espaço, tab, nova linha)
\SNão é espaço em branco\SCombina qualquer caractere que não seja espaço em branco
*Zero ou mais ocorrênciasa*Combina zero ou mais “a”
+Uma ou mais ocorrênciasa+Combina uma ou mais “a”
?Zero ou uma ocorrênciaa?Combina zero ou uma “a”
{n}Exatamente n ocorrênciasa{3}Combina exatamente três “a”
{n,}Pelo menos n ocorrênciasa{3,}Combina pelo menos três “a”
{n,m}Entre n e m ocorrênciasa{3,5}Combina entre três e cinco “a”
``Ou`a
(…)Agrupamento(abc)Combina “abc” e trata como um grupo
[…]Conjunto de caracteres[a-z]Combina qualquer caractere de “a” a “z”
[^…]Conjunto negado[^a-z]Combina qualquer caractere que não seja de “a” a “z”
(?i)Casamento sem distinção entre maiúsculas e minúsculas(?i)abcCombina “abc”, “ABC”, “Abc”, etc.
(?m)Modo multiline(?m)^abc“^” e “$” combinam início e fim de linha em vez de início e fim de string

No Excel antes fazíamos uso do VBA para suprir esta necessidade, dado que o REGEX não estava presente como funções do Excel.

Mas com esta nova atualização isso mudou e agora temos as expressões regulares em três novas funções no Excel, RegexTest, RegexExtract e RegexReplace que veremos a seguir.

RegexTest Excel

A função RegexTest permite verificar se alguma parte de um texto fornecido corresponde a uma expressão regular, REGEX. A função retorna VERDADEIRO se houver correspondência e FALSO se não houver.

A sintaxe é =REGEXTEST (text; pattern; [case_sensitivity])

Parâmetros:

ParâmetrosDescrição
textO texto ou a referência a uma célula que contém o texto com o qual você deseja fazer a correspondência.
patternA expressão regular (“regex”) que descreve o padrão de texto que você deseja corresponder.
case_sensitivityDetermina se a correspondência diferencia maiúsculas de minúsculas. Por padrão, a correspondência diferencia maiúsculas de minúsculas. Insira um dos seguintes:

0: Diferencia maiúsculas de minúsculas

1: Não diferencia maiúsculas de minúsculas
Podemos por exemplo usar para identificar se há datas em uma célula que contém texto:

Veja que usamos a seguinte função =REGEXTEST(B6;”[0-9]+/[0-9]+/[0-9]+”;1)

Nela usamos a expressão REGEX: [0-9]+/[0-9]+/[0-9]+ aonde temos um padrão de data composto por um ou mais números e barra para cada um destes

A aplicação da função abaixo:

Regexextract RegexTest RegexReplace Excel 1

Como resultado, em cada linha nós temos se existem datas nos textos, retornando VERDADEIRO para quando existir.

Regexextract RegexTest RegexReplace Excel 2

Função RegexExtract Excel

A função RegexExtract, presente no Excel 365 permite extrair textos de uma célula ou conjunto de células utilizando uma expressão regular REGEX.

Você poderia extrair a primeira correspondência, todas as correspondência ou grupos da primeira correspondência.

A sintaxe da função é: =REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Veja os parâmetros abaixo:

ParâmetrosDescrição
textO texto ou a referência a uma célula que contém o texto do qual você deseja extrair strings.
patternA expressão regular (“regex”) que descreve o padrão de texto que você deseja extrair.
[return_mode]Um número que especifica quais strings você deseja extrair. Por padrão, o modo de retorno é 0. Os valores possíveis são:

0:  Retorna a primeira string que corresponde ao padrão
1:  Retorna todas as strings que correspondem ao padrão como um array
2:  Retorna grupos de captura da primeira partida como um array

Nota: Os grupos de captura são partes de um padrão regex entre parênteses “(…)”. Eles permitem que você retorne partes separadas de uma única partida individualmente.
[case_sensitivity]Determina se a correspondência diferencia maiúsculas de minúsculas. Por padrão, a correspondência diferencia maiúsculas de minúsculas. Insira um dos seguintes:

0: Diferencia maiúsculas de minúsculas

1: Não diferencia maiúsculas de minúsculas

Esta é uma das funções mais utilizadas, aonde podemos extrair padrões como veremos a seguir em exemplos:

Extrair Somente Datas Excel

Para extração de somente datas, como vemos na tabela abaixo, usaremos também a função RegexExtract, lembrando que as funções RegexReplace e RegexTest também trabalham com os mesmos padrões.

Regexextract RegexTest RegexReplace Excel 3

Para isso usamos a função =TRANSPOR(REGEXEXTRACT(B6;”[0-9]+/[0-9]+/[0-9]+”;1))*1

Como detalhe nesta função veja que usamos o padrão [0-9]+/[0-9]+/[0-9]+ e também a função TRANSPOR, além disso, veja que o resultado é multiplicado por 1.

O motivo desta multiplicação é porque quando a data é extraída do texto, ela vem como texto, e a multiplicação faz uma conversão implícita do texto para data.

A função traspor extrai os dados da data para a direita.

Regexextract RegexTest RegexReplace Excel 4

Extrair Somente Horários no Excel com RegexExtract

No exemplo abaixo, aplicamos a extração dos dados com o seguinte padrão: [0-9]+:[0-9]+

A função utilizada foi: =TRANSPOR(REGEXEXTRACT(B6;”[0-9]+:[0-9]+”;1))*1

Com isso temos também o uso da função TRANSPOR para que os dados fossem transpostos como temos a extração das datas e também temos a multiplicação por 1 para a conversão.

Extrair Email com Regex RegextExtract Excel

Com a função REGEX no Excel você pode extrair qualquer padrão, inclusive email.

O padrão utilizado para extrair é: \w+([+.]\w+)@\w+([.]\w+).\w+([.]\w+)*

E a função utilizada é: =REGEXEXTRACT(B6:B10;”\w+([+.]\w+)@\w+([.]\w+).\w+([.]\w+)*”)

Com isso temos o email extraído do texto, veja que o padrão é aplicado identificando a questão de ponto, de @, etc.

Extrair Dados Compostos com Regex e RegextExtract

A função REGEX é muito poderosa com ela podemos também extrair dados compostos, como um nome de usuário de um texto por exemplo

Na imagem abaixo temos no campo texto o nome de usuário e a sua matrícula, o objetivo é extrair estes dados.

A função utilizada foi: =TRANSPOR(REGEXEXTRACT(B6;”[\w]*[0-9]+”;1))

No REGEX acima ele busca um padrão com qualquer quantidade de letras e mais qualquer quantidade de números.

Extrair REGEX Complexo com RegexExtract

No exemplo abaixo temos a extração de números de processos à partir de textos digitados.

É bastante complexo a extração destes dados de outra forma, mas com REGEX você consegue.

O padrão utilizado foi: [0-9]{7}-[0-9]{2}.[0-9]{4}.[0-9]{1}.[0-9]{2}.[0-9]{4}, nele temos os números e a quantidade de números e em seguida as pontuações.

A função utilizada para a extração dos dados foi: =REGEXTEST(B8;”[0-9]{7}-[0-9]{2}.[0-9]{4}.[0-9]{1}.[0-9]{2}.[0-9]{4}”)

Com isso temos os dados extraídos conforme abaixo, no primeiro usamos o RegexTest para mostrar se existe o padrão e no segundo usamos a função:

=TRANSPOR(REGEXEXTRACT(B8;”[0-9]{7}-[0-9]{2}.[0-9]{4}.[0-9]{1}.[0-9]{2}.[0-9]{4}”;1))

RegexReplace Excel

A função RegexReplace tem por finalidade substituir uma determinada cadeia de caracteres em uma string identificada por uma expressão regular.

A sintaxe utilizada para esta finalidade é: =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])

Os parâmetros utilizados da função são:

ParâmetrosDescrição
textO texto ou a referência a uma célula que contém o texto no qual você deseja substituir as strings.
patternA expressão regular (“regex”) que descreve o padrão de texto que você deseja substituir.
replacementO texto que você deseja substituir instâncias de pattern.
[occurrence]Especifica qual instância do padrão você deseja substituir. Por padrão, a ocorrência é 0, que substitui todas as instâncias. Um número negativo substitui essa instância, pesquisando a partir do final.
[case_sensitivity]Determina se a correspondência diferencia maiúsculas de minúsculas. Por padrão, a correspondência diferencia maiúsculas de minúsculas. Insira um dos seguintes:

0: Diferencia maiúsculas de minúsculas
1: Não diferencia maiúsculas de minúsculas

No exemplo abaixo temos a extração dos dados conforme o uso da função REGEXREPLACE, nela substituímos os números por um texto vazio.

Extrair Somente Texto Excel

No exemplo abaixo usaremos a função para extrair somente texto de uma célula.

Para isso usamos o padrão [\d], ele extrai então o texto de cada uma das células, pois o padrão [\d] retorna somente textos.

A função usada será: =REGEXREPLACE(B6:B15;”[\d]”;””)

Como resultado temos então o texto extraído de cada uma das células, não retornando então os números.

Download Planilha RegexExtract Excel

Clique no botão abaixo para realizar o  download do arquivo de exemplo:

Baixe a planilha


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel