Analisar texto com expressões regulares (RegExp) no Excel

Uma das tarefas mais demoradas e frustrantes ao trabalhar com texto no Excel é análise – analisando o “mingau” alfanumérico em componentes e extraindo dele os fragmentos que precisamos. Por exemplo:

• extraindo o CEP do endereço (é bom que o CEP esteja sempre no início, mas e se não estiver?)
• encontrar o número e a data da fatura na descrição do pagamento no extrato bancário
• extração de TIN de descrições heterogêneas de empresas na lista de contrapartes
• procure um número de carro ou número de artigo na descrição, etc.
  

Normalmente, nesses casos, após meia hora de leitura manual do texto, pensamentos começam a vir à mente de alguma forma para automatizar esse processo (especialmente se houver muitos dados). Existem várias soluções e com vários graus de complexidade-eficiência:

• Use funções de texto embutidas do Excel para pesquisar texto com cola e recorte: LEVSIMV (DEIXOU), DIREITO (DIREITO), PSTR (meio), STsEPIT (CONCATENAR) e seus análogos, COMBINAR (TEXTO JUNTO), EXATO (EXATO) etc. Este método é bom se houver uma lógica clara no texto (por exemplo, o índice está sempre no início do endereço). Caso contrário, as fórmulas ficam muito mais complicadas e, às vezes, chega-se até a fórmulas de matriz, o que diminui bastante em tabelas grandes.
• utilização como operador de similaridade de texto do Visual Basic encapsulado em uma função de macro personalizada. Isso permite que você implemente uma pesquisa mais flexível usando caracteres curinga (*, #,?, etc.) Infelizmente, esta ferramenta não pode extrair a substring desejada do texto – apenas verifique se ela está contida nela.

Além do acima, há outra abordagem que é muito conhecida em círculos estreitos de programadores profissionais, desenvolvedores da web e outros técnicos – isso é expressões regulares (Expressões Regulares = RegExp = “regexps” = “regulares”). Simplificando, RegExp é uma linguagem onde caracteres e regras especiais são usados ​​para procurar as substrings necessárias no texto, extraí-las ou substituí-las por outro texto. As expressões regulares são uma ferramenta muito poderosa e bonita que supera todas as outras formas de trabalhar com texto em uma ordem de magnitude. Muitas linguagens de programação (C#, PHP, Perl, JavaScript…) e editores de texto (Word, Notepad++…) suportam expressões regulares.

Infelizmente, o Microsoft Excel não tem suporte RegExp pronto para uso, mas isso pode ser facilmente corrigido com o VBA. Abra o Editor do Visual Basic na guia revelador (Desenvolvedor) ou atalho de teclado outro+F11. Em seguida, insira o novo módulo através do menu Inserir - Módulo e copie o texto da seguinte função de macro lá:

Função pública RegExpExtract(Text As String, Pattern As String, Item Opcional As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Texto) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Agora podemos fechar o Editor do Visual Basic e retornar ao Excel para experimentar nosso novo recurso. Sua sintaxe é a seguinte:

=RegExpExtract( Txt ; Padrão ; Item )

onde

• txt – uma célula com o texto que estamos verificando e da qual queremos extrair a substring que precisamos
• de cinto de segurança – máscara (padrão) para pesquisa de substring
• item – o número de sequência da substring a ser extraída, se houver várias delas (se não for especificada, a primeira ocorrência será exibida)

A coisa mais interessante aqui, é claro, é Pattern – uma string de modelo de caracteres especiais “na linguagem” do RegExp, que especifica exatamente o que e onde queremos encontrar. Aqui estão os mais básicos para você começar:

Se estivermos procurando por um certo número de caracteres, por exemplo, um código postal de seis dígitos ou todos os códigos de produto de três letras, vamos em socorro quantificadores or quantificadores são expressões especiais que especificam o número de caracteres a serem pesquisados. Quantificadores são aplicados ao caractere que vem antes dele:

Agora vamos para a parte mais interessante – uma análise da aplicação da função criada e o que aprendemos sobre padrões em exemplos práticos da vida.

Extraindo números do texto

Para começar, vamos analisar um caso simples – você precisa extrair o primeiro número do mingau alfanumérico, por exemplo, o poder das fontes de alimentação ininterruptas da lista de preços:

A lógica por trás da expressão regular é simples: d significa qualquer dígito, e o quantificador + diz que seu número deve ser um ou mais. O sinal de menos duplo na frente da função é necessário para converter “on the fly” os caracteres extraídos em um número completo do número como texto.

CEP

À primeira vista, tudo é simples aqui – estamos procurando exatamente seis dígitos seguidos. Usamos um caractere especial d para dígito e quantificador 6{} para o número de caracteres:

No entanto, uma situação é possível quando, à esquerda do índice na linha, há outro grande conjunto de números em uma linha (número de telefone, TIN, conta bancária etc.) Então nossa temporada regular retirará os primeiros 6 dígitos dele, ou seja, não funcionará corretamente:

Para evitar que isso aconteça, precisamos adicionar um modificador nas bordas da nossa expressão regular b significando o fim de uma palavra. Isso deixará claro para o Excel que o fragmento (índice) que precisamos deve ser uma palavra separada e não parte de outro fragmento (número de telefone):

Telefone

O problema de encontrar um número de telefone no texto é que há tantas opções para escrever números – com e sem hífens, por meio de espaços, com ou sem código de região entre colchetes etc. Portanto, na minha opinião, é mais fácil primeiro limpe todos esses caracteres do texto de origem usando várias funções aninhadas SUBSTITUTO (SUBSTITUTO)de modo que ele se une em um único todo e, em seguida, com um regular primitivo e{11} retire 11 dígitos seguidos:

ITN

Aqui é um pouco mais complicado, porque o TIN (em nosso país) pode ter 10 dígitos (para pessoas jurídicas) ou 12 dígitos (para pessoas físicas). Se você não encontrar falhas especialmente, então é bem possível ficar satisfeito com o regular e{10,12}, mas, estritamente falando, ele extrairá todos os números de 10 a 12 caracteres, ou seja, e 11 dígitos digitados erroneamente. Seria mais correto usar dois padrões conectados por um operador lógico OR | (Barra vertical):

Observe que na consulta procuramos primeiro por números de 12 bits e só depois por números de 10 bits. Se escrevermos nossa expressão regular ao contrário, ela será exibida para todos, mesmo TINs longos de 12 bits, apenas os primeiros 10 caracteres. Ou seja, após a primeira condição ser acionada, a verificação adicional não é mais realizada:

Esta é a diferença fundamental entre o operador | de uma função lógica padrão do Excel OR (OR), onde reorganizar os argumentos não altera o resultado.

SKUs do produto

Em muitas empresas, identificadores exclusivos são atribuídos a bens e serviços – artigos, códigos SAP, SKUs, etc. Se houver lógica em sua notação, eles podem ser facilmente extraídos de qualquer texto usando expressões regulares. Por exemplo, se sabemos que nossos artigos sempre consistem em três letras maiúsculas em inglês, um hífen e um número subsequente de três dígitos, então:

A lógica por trás do modelo é simples. [AZ] – significa quaisquer letras maiúsculas do alfabeto latino. O próximo quantificador 3{} diz que é importante para nós que existam exatamente três dessas cartas. Após o hífen, estamos aguardando três dígitos, então adicionamos no final e{3}

Valores em dinheiro

De forma semelhante ao parágrafo anterior, você também pode retirar preços (custos, IVA …) da descrição das mercadorias. Se os valores monetários, por exemplo, forem indicados com um hífen, então:

padrão d com quantificador + pesquisa qualquer número até um hífen e e{2} procurará centavos (dois dígitos) depois.

Se você precisar extrair não preços, mas IVA, poderá usar o terceiro argumento opcional de nossa função RegExpExtract, que especifica o número ordinal do elemento a ser extraído. E, claro, você pode substituir a função SUBSTITUTO (SUBSTITUTO) nos resultados, hifen no separador decimal padrão e adicione um sinal de menos duplo no início para que o Excel interprete o IVA encontrado como um número normal:

Números da placa do carro

Se você não leva veículos especiais, reboques e outras motocicletas, o número padrão do carro é analisado de acordo com o princípio “letra – três números – duas letras – código da região”. Além disso, o código da região pode ter 2 ou 3 dígitos, e apenas aqueles que são semelhantes em aparência ao alfabeto latino são usados ​​como letras. Assim, a seguinte expressão regular nos ajudará a extrair números do texto:

Horário

Para extrair a hora no formato HH:MM, a seguinte expressão regular é adequada:

Após o fragmento do cólon [0-5] dias, como é fácil de descobrir, define qualquer número no intervalo de 00 a 59. Antes dos dois pontos entre parênteses, dois padrões funcionam, separados por um OR lógico (pipe):

• [0-1] dias – qualquer número no intervalo 00-19
• 2 [0-3] – qualquer número no intervalo 20-23

Ao resultado obtido, você pode aplicar adicionalmente a função padrão do Excel TIME (EQUIPE)para convertê-lo em um formato de hora que seja compreensível para o programa e adequado para cálculos posteriores.

Verificação de senha

Suponha que precisamos verificar a lista de senhas inventadas pelos usuários para ver se estão corretas. De acordo com nossas regras, as senhas podem conter apenas letras em inglês (minúsculas ou maiúsculas) e números. Espaços, sublinhados e outros sinais de pontuação não são permitidos.

A verificação pode ser organizada usando a seguinte expressão regular simples:

De fato, com esse padrão, exigimos que entre o início (^) e fim ($) em nosso texto havia apenas caracteres do conjunto dado entre colchetes. Se você também precisar verificar o comprimento da senha (por exemplo, pelo menos 6 caracteres), o quantificador + pode ser substituído pelo intervalo “seis ou mais” na forma {6,}:

Cidade do endereço

Digamos que precisamos puxar a cidade da barra de endereços. O programa regular ajudará, extraindo o texto de “g”. para a próxima vírgula:

Vamos dar uma olhada neste padrão.

Se você leu o texto acima, já entendeu que alguns caracteres em expressões regulares (pontos, asteriscos, cifrões etc.) têm um significado especial. Se você precisar procurar por esses caracteres, eles serão precedidos por uma barra invertida (às vezes chamada blindagem). Portanto, ao buscar o fragmento “g.” temos que escrever em expressão regular Sr.. se estamos procurando por um plus, então + etc.

Os próximos dois caracteres em nosso modelo, o ponto e o asterisco quantificador, representam qualquer número de qualquer caractere, ou seja, qualquer nome de cidade.

Há uma vírgula no final do modelo, porque estamos procurando o texto de “g”. a uma vírgula. Mas pode haver várias vírgulas no texto, certo? Não só depois da cidade, mas também depois da rua, das casas, etc. Em qual deles vai parar o nosso pedido? É para isso que serve o ponto de interrogação. Sem ele, nossa expressão regular extrairia a string mais longa possível:

Em termos de expressões regulares, esse padrão é “ganancioso”. Para corrigir a situação, é necessário um ponto de interrogação – ele torna o quantificador após o qual fica “mesquinho” – e nossa consulta leva o texto apenas até a primeira vírgula do contador após “g.”:

Nome do arquivo do caminho completo

Outra situação muito comum é extrair o nome do arquivo do caminho completo. Uma simples expressão regular do formulário ajudará aqui:

O truque aqui é que a busca, de fato, ocorre na direção oposta – do fim para o início, pois no final do nosso template está $, e estamos procurando tudo antes dele até a primeira barra invertida da direita. A barra invertida é escapada, como o ponto no exemplo anterior.

PS

“Para o fim” Quero esclarecer que tudo o que foi dito acima é uma pequena parte de todas as possibilidades que as expressões regulares oferecem. Existem muitos caracteres especiais e regras para seu uso, e livros inteiros foram escritos sobre esse tópico (eu recomendo pelo menos este para começar). De certa forma, escrever expressões regulares é quase uma arte. Quase sempre, uma expressão regular inventada pode ser melhorada ou complementada, tornando-a mais elegante ou capaz de trabalhar com uma gama mais ampla de dados de entrada.

Para analisar e analisar as expressões regulares de outras pessoas ou depurar as suas próprias, existem vários serviços online convenientes: RegEx101, REGEXR e mais

Infelizmente, nem todos os recursos das expressões regulares clássicas são suportados no VBA (por exemplo, pesquisa reversa ou classes POSIX) e podem funcionar com cirílico, mas acho que o que existe é suficiente para a primeira vez.

Se você não é novo no tópico e tem algo para compartilhar, deixe as expressões regulares úteis ao trabalhar no Excel nos comentários abaixo. Uma mente é boa, mas duas botas são um par!

• Substituir e limpar texto com a função SUBSTITUTE
• Pesquisa e destaque de caracteres latinos no texto
• Procure o texto semelhante mais próximo (Ivanov = Ivonov = Ivanof, etc.)