Tutorial do Excel Regex: Dominando a correspondência de padrões com expressões regulares

Você já precisou padronizar uma lista de números de telefone em diferentes formatos? E quanto à extração de palavras-chave de campos de texto de formato livre? Apresentando: RegEx! RegEx, abreviação de Regular Expressions (expressões regulares), são padrões poderosos e flexíveis usados para fazer a correspondência de strings em blocos de texto. Neste artigo, falaremos sobre a sintaxe de regex comum, como ativar a regex no Excel e, por fim, como usá-la de forma eficaz.

O que são expressões regulares?

As expressões regulares, geralmente abreviadas como "regex" ou "regexp", são uma forma de definir um padrão de pesquisa, que pode ser usado para várias tarefas de manipulação de texto, como pesquisa, análise e/ou substituição de texto. As expressões regulares são amplamente usadas em programação, editores de texto e outros softwares para tarefas que envolvem correspondência de padrões. O tipo de regex, ou sintaxe, no Excel é baseado no mecanismo de expressão regular do .NET.

Expressões regulares comuns

As expressões regulares podem ser agrupadas com base nas características que as definem. Abaixo estão as expressões regulares comuns que você encontrará.

Caracteres literais

As expressões regulares podem conter caracteres literais que correspondem a si mesmos. Por exemplo, a regex "hello" corresponderia exatamente à string "hello".

Metacaracteres

As expressões regulares também incluem metacaracteres, que têm significados especiais. Alguns metacaracteres comuns incluem:

• . (ponto): Corresponde a qualquer caractere único, exceto uma quebra de linha.
• *: Corresponde a zero ou mais ocorrências do caractere ou grupo anterior.
• +: Corresponde a uma ou mais ocorrências do caractere ou grupo anterior.
• ?: Corresponde a zero ou uma ocorrência do caractere ou grupo anterior.
• | (tubo): Atua como um OU lógico e permite que você especifique alternativas.
• () (parênteses): Agrupa caracteres ou subpadrões.
• [] (colchetes): Define uma classe de caracteres, permitindo que você faça a correspondência de qualquer caractere de um conjunto de caracteres.
• ^ (caret): Corresponde ao início de uma linha ou cadeia de caracteres.
• $: Corresponde ao final de uma linha ou cadeia de caracteres.
• \ (barra invertida): Escapa de um metacaractere para combiná-lo literalmente.

Classes de caracteres

As classes de caracteres em expressões regulares (regex) são notações especiais que permitem a correspondência de qualquer um de um conjunto de caracteres. Eles são usados para simplificar os padrões regex, fornecendo uma maneira concisa de especificar um grupo de caracteres que deve ser correspondido. Você pode usar colchetes [...] para definir classes de caracteres. Por exemplo, [aeiou] corresponde a qualquer vogal. [A-Z] corresponde a qualquer letra maiúscula.

Quantificadores

Quantificadores são construções que especificam quantas vezes um determinado padrão, caractere ou classe de caracteres deve ocorrer na cadeia de caracteres de destino para obter uma correspondência. Em outras palavras, eles especificam quantas vezes um caractere ou grupo deve ser repetido. Por exemplo, a{3} corresponde exatamente a três caracteres "a" consecutivos. ab? corresponde a "a" ou "ab".

Âncoras

As âncoras são caracteres especiais que não correspondem a nenhum caractere da cadeia de caracteres. Em vez disso, eles correspondem a uma posição antes, depois ou entre caracteres. As âncoras são usadas para garantir que o padrão regex ocorra em um local específico do texto. ^ ancora o padrão no início da cadeia de caracteres e $ ancora-o no final, por exemplo.

Modificador

Modificadores são caracteres que alteram a forma como o mecanismo de regex interpreta o padrão. Eles são usados para ajustar o comportamento da correspondência de regex, permitindo mais flexibilidade e controle. Os modificadores podem afetar vários aspectos do processo de correspondência, como sensibilidade a maiúsculas e minúsculas, correspondência de várias linhas e como os caracteres especiais são interpretados. Por exemplo, i torna o padrão insensível a maiúsculas e minúsculas e g o torna global, de modo que ele corresponde a todas as ocorrências na entrada.

Aplicativos de expressões regulares no Excel

RegEx são úteis em vários cenários:

Validação de dados

Na maioria dos casos, você precisará validar os dados para garantir que está analisando as informações corretas. Um exemplo clássico são os números de telefone. Nos EUA, o formato padrão é (###) ###-####. No entanto, eles também podem ser formatados como: ###-###-####, (+#) ### ### ####, ##########, etc. O RegEx pode ajudar a identificar que as informações contidas no arquivo são de fato números de telefone. A expressão regular a seguir retornará uma correspondência em números de telefone de 10 dígitos, com um código de país opcional entre 1 e 2 dígitos: (\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$.

Extração de texto

Frases longas geralmente são difíceis de serem analisadas por um software de planilha tradicional. Para extrair palavras-chave específicas, você pode usar o RegEx para fazer exatamente isso. Por exemplo, para extrair títulos de livros, entre aspas simples, de uma transcrição de entrevista, podemos usar a seguinte expressão regular: '(.*?)'.

Manipulação de texto

Além de extrair informações, você também pode transformá-las e padronizá-las para fins de relatório. Digamos que estejamos interessados em extrair os primeiros cinco dígitos dos códigos postais dos EUA. Podemos substituir todas as instâncias de um campo que contém CEPs usando a expressão regular ^\d{5}.

Tokenização

A tokenização é o processo de dividir o texto em tokens ou palavras individuais. Os padrões Regex podem ser usados para definir regras de tokenização de texto. Por exemplo, você pode usar \s+ para dividir o texto em caracteres de espaço em branco ou \W+ para dividir o texto em caracteres que não sejam de palavras, como pontuação.

Para obter informações sobre padrões adicionais, consulte nossa folha de dicas sobre Expressão regular em Python.

Usando RegEx no Excel

Embora o Excel não ofereça suporte nativo ao RegEx, você pode habilitá-lo escrevendo sua própria definição personalizada usando um script VBA (para Windows) ou habilitando suplementos (para Mac). Vamos ver como ativar uma função regex para extrair texto de uma coluna no Excel.

Para Windows

Para começar, pressione Alt+F11 para abrir o Editor do Visual Basic. Você também pode navegar até ele em Tools > Macro > Visual Basic Editor. Em seguida, clique em Inserir > Módulo.

Cole a seguinte função no módulo:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Salve a função e navegue de volta para a planilha. Agora, salve a pasta de trabalho como uma pasta habilitada para macro, com uma extensão de arquivo .xlsm.

Usando a função em uma planilha do Excel

Ao ativar as macros em sua pasta de trabalho do Excel, agora você pode usar a função CustomRegExpExtract como uma função regular no Excel para analisar padrões complexos de strings, onde:

• inputText é a cadeia de caracteres original a ser analisada;

• regexPattern é o padrão de expressão regular usado para analisar o inputText;

• instanceNumber retorna todas as instâncias do padrão, por padrão, mas um número inteiro pode ser inserido para especificar o número de instâncias a serem retornadas; e

• matchCase especifica se devem ser consideradas as maiúsculas e minúsculas do texto durante a correspondência (TRUE ou omitido) ou se devem ser desconsideradas (FALSE).

Digamos que estejamos interessados em extrair números de telefone de um campo. Podemos digitar o padrão regex em qualquer célula (no caso abaixo, ele aparece na célula A2). Na célula A6 está seu exemplo de string de forma livre, "Sophia, 1111111111". Na célula B6, digitamos =CustomRegExpExtract(A6,$A$2). A função extrai o número de telefone fazendo a correspondência da string com o padrão regex na célula A2 e retorna "1111111111", conforme esperado.

Para Mac OS

Infelizmente, o Mac OS não reconhece a biblioteca MS VBScript Regular Expressions 5.5. Para habilitar a correspondência de padrões complexos no Excel em um Mac, há duas opções: usar filtros avançados ou instalar pacotes.

Uso de filtros avançados

Os filtros avançados do Excel ajudam a realizar operações complexas de filtragem de dados com base em um conjunto de vários critérios. Esse recurso pode ser particularmente útil ao lidar com grandes conjuntos de dados. Veja como usar filtros avançados no Excel:

• Configure seus dados: Certifique-se de que seus dados estejam em um formato de tabela com cabeçalhos claros.

• Criar um intervalo de critérios: Configure uma área separada em sua planilha para definir os critérios de filtragem. Essa área deve ter os mesmos cabeçalhos das colunas que você deseja filtrar. Abaixo desses cabeçalhos, especifique os critérios para filtragem. Você pode inserir vários critérios no mesmo cabeçalho para uma lógica "OR" ou em linhas separadas para uma lógica "AND".

• Selecione os Dados: Clique em uma célula do conjunto de dados ou selecione o intervalo inteiro que deseja filtrar.

• Configurar o filtro: Clique na guia Dados na faixa de opções do Excel e navegue até a opção Filtro avançado. Clique em Advanced (Avançado ) no grupo Sort & Filter (Classificar e filtrar ). Na caixa de diálogo Advanced Filter (Filtro avançado), escolha se deseja filtrar a lista no local ou copiar os resultados para outro local. Especifique o intervalo da lista (seus dados). Especifique o intervalo de critérios (onde você define as condições de filtragem). Clique em OK para aplicar o filtro.

Suponha que você tenha um conjunto de dados com as colunas Nome, Idade, Cidade. Você deseja filtrar para mostrar pessoas com mais de 30 anos em Nova York ou qualquer pessoa em Los Angeles. Seu intervalo de critérios pode ser assim:

Instalação de add-ins

• Kutools for Excel: O Kutools é um add-in abrangente que inclui muitos recursos, um dos quais é a capacidade de localizar e substituir dados usando regex. Ele adiciona uma nova guia à faixa de opções do Excel com uma ampla gama de ferramentas, incluindo funções regex.

• RegEx Find/Replace: Esse é um suplemento para o Excel que permite localizar e substituir texto usando expressões regulares. Ele se integra ao Excel e oferece uma funcionalidade de localização/substituição mais avançada do que a opção incorporada.

• PowerGREP: Embora não seja um suplemento direto do Excel, o PowerGREP pode trabalhar com arquivos do Excel. É uma poderosa ferramenta grep para Windows que permite operações complexas de regex em arquivos de texto e binários. É mais um aplicativo autônomo, mas pode ser usado em conjunto com o Excel para processamento avançado de regex. No entanto, não é barato. Uma licença para um único usuário custa US$ 159 e o preço sobe para US$ 5.300 para uma licença para 100 usuários.

Alguns suplementos podem ser encontrados diretamente no Excel por meio da guia Inserir > Obter suplementos.

Outros precisam ser baixados de uma fonte externa e seguir suas instruções de instalação.

Algumas considerações:

• Compatibilidade: Verifique se o suplemento é compatível com a versão do Excel e com o sistema operacional.
• Segurança: Faça download e instale add-ins somente de fontes confiáveis.
• Necessidades de funcionalidade: Considere as tarefas específicas de regex que você precisa realizar e escolha um add-in que melhor atenda a essas necessidades.

Conclusão

Na maioria dos casos, as funções nativas como FIND, SEARCH e REPLACE ajudarão em 90% de suas necessidades de manipulação de texto no Excel. No entanto, para os 10% restantes, em casos que envolvem padrões de cadeia mais complexos, o regex pode ajudar muito. Para saber mais sobre como aplicar expressões regulares usando linguagens de programação populares, confira nossos cursos sobre Expressões regulares em Python e Expressões regulares intermediárias em R.

Para se familiarizar com todos os aspectos do Excel, confira nosso curso de habilidades Fundamentos do Excel, que o conduz pelas habilidades essenciais necessárias para se tornar proficiente com a ferramenta.