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ódigo	Descrição	Exemplo	Resultado
()	Define um grupo de captura	(abc) em “abcabc”	Corresponde a abc
.	Qualquer caractere, exceto nova linha	a.b	Combina “a” seguido de qualquer caractere seguido de “b”
^	Início da linha	^abc	Combina “abc” no início da linha
$	Fim da linha	abc$	Combina “abc” no fim da linha
\d	Dígito (0-9)	\d	Combina qualquer dígito
\D	Não é um dígito	\D	Combina qualquer caractere que não seja dígito
\w	Palavra (a-z, A-Z, 0-9, _)	\w	Combina qualquer caractere de palavra
\W	Não é uma palavra	\W	Combina qualquer caractere que não seja de palavra
\s	Espaço em branco	\s	Combina qualquer espaço em branco (espaço, tab, nova linha)
\S	Não é espaço em branco	\S	Combina qualquer caractere que não seja espaço em branco
*	Zero ou mais ocorrências	a*	Combina zero ou mais “a”
+	Uma ou mais ocorrências	a+	Combina uma ou mais “a”
?	Zero ou uma ocorrência	a?	Combina zero ou uma “a”
{n}	Exatamente n ocorrências	a{3}	Combina exatamente três “a”
{n,}	Pelo menos n ocorrências	a{3,}	Combina pelo menos três “a”
{n,m}	Entre n e m ocorrências	a{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)abc	Combina “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âmetros	Descrição
text	O texto ou a referência a uma célula que contém o texto com o qual você deseja fazer a correspondência.
pattern	A expressão regular (“regex”) que descreve o padrão de texto que você deseja corresponder.
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

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.

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âmetros	Descrição
text	O texto ou a referência a uma célula que contém o texto do qual você deseja extrair strings.
pattern	A 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.

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.

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âmetros	Descrição
text	O texto ou a referência a uma célula que contém o texto no qual você deseja substituir as strings.
pattern	A expressão regular (“regex”) que descreve o padrão de texto que você deseja substituir.
replacement	O 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