Analiza texto con expresións regulares (RegExp) en Excel

Unha das tarefas máis lentas e frustrantes cando se traballa con texto en Excel é análise – analizar a “gacha” alfanumérica en compoñentes e extraer dela os fragmentos que precisamos. Por exemplo:

• extraer o código postal do enderezo (é bo se o código postal está sempre ao principio, pero e se non o está?)
• atopando o número e a data da factura a partir da descrición do pagamento no extracto bancario
• extracción de TIN de descricións variadas de empresas na lista de contrapartes
• buscar un número de coche ou número de artigo na descrición, etc.
  

Normalmente, en tales casos, despois de media hora de triste selección manual do texto, comezan a ocorrer pensamentos para automatizar este proceso (especialmente se hai moitos datos). Existen varias solucións e con distintos graos de complexidade e eficiencia:

• Usar funcións de texto de Excel integradas para buscar texto-cortar-pegar: LEVSIMV (ESQUERDA), DEREITO (DEREITO), PSTR (medio), STsEPIT (CONCATENAR) e os seus análogos, COMBINAR (TEXTO CONXUNTO), EXACT (EXACTO) etc. Este método é bo se hai unha lóxica clara no texto (por exemplo, o índice está sempre ao comezo do enderezo). En caso contrario, as fórmulas complícanse moito e, ás veces, ata chega a fórmulas matriciales, o que se ralentiza moito nas táboas grandes.
• Uso como operador de semellanza de texto de Visual Basic envolto nunha función de macro personalizada. Isto permítelle implementar unha busca máis flexible usando caracteres comodín (*, #,?, etc.) Desafortunadamente, esta ferramenta non pode extraer a subcadea desexada do texto; só comprobe se está contida nel.

Ademais do anterior, hai outro enfoque que é moi coñecido en círculos estreitos de programadores profesionais, desenvolvedores web e outros técnicos. expresións regulares (Expresións regulares = RegExp = “regexps” = “regulares”). Simplemente, RegExp é unha linguaxe onde se usan caracteres e regras especiais para buscar as subcadeas necesarias no texto, extraelas ou substituílas por outro texto.. As expresións regulares son unha ferramenta moi poderosa e fermosa que supera todas as outras formas de traballar co texto nunha orde de magnitude. Moitos linguaxes de programación (C#, PHP, Perl, JavaScript...) e editores de texto (Word, Notepad++...) admiten expresións regulares.

Desafortunadamente, Microsoft Excel non ten soporte para RegExp fóra da caixa, pero isto pódese solucionar facilmente con VBA. Abre o Editor de Visual Basic desde a pestana revelador (Desenvolvedor) ou atallo de teclado alt+F11. A continuación, insira o novo módulo a través do menú Inserir – Módulo e copie alí o texto da seguinte función de macro:

Función pública RegExpExtract(Texto como cadea, patrón como cadea, elemento opcional como número enteiro = 1) como cadea por erro Ir a ErrHandl Establecer regex = CreateObject("VBScript.RegExp") regex.Pattern = Patrón regex.Global = True If regex.Test (Texto) A continuación, Establecer coincidencias = regex.Execute (Texto) RegExpExtract = coincidencias.Item (Item - 1) Función de saída End If ErrHandl: RegExpExtract = CVErr (xlErrValue) End Function  

Agora podemos pechar o Editor de Visual Basic e volver a Excel para probar a nosa nova función. A súa sintaxe é a seguinte:

=RegExpExtract(Txt; Patrón; Elemento)

onde

• txt – unha cela co texto que estamos a comprobar e da que queremos extraer a subcadea que necesitamos
• estándar – máscara (patrón) para a busca de subcadeas
• Elemento – o número de secuencia da subcadea que se vai extraer, se hai varias (se non se especifica, móstrase a primeira aparición)

O máis interesante aquí, por suposto, é Pattern: unha cadea de modelos de caracteres especiais "no idioma" de RegExp, que especifica o que exactamente e onde queremos atopar. Aquí tes os máis básicos para comezar:

Se buscamos un certo número de caracteres, por exemplo, un código postal de seis díxitos ou todos os códigos de produtos de tres letras, entón imos ao rescate cuantificadores or cuantificadores son expresións especiais que especifican o número de caracteres que se van buscar. Os cuantificadores aplícanse ao carácter que lle precede:

Agora imos pasar á parte máis interesante: unha análise da aplicación da función creada e o que aprendemos sobre patróns sobre exemplos prácticos da vida.

Extraer números do texto

Para comezar, imos analizar un caso sinxelo: cómpre extraer o primeiro número de mingau alfanumérico, por exemplo, a potencia das fontes de alimentación ininterrompidas da lista de prezos:

A lóxica detrás da expresión regular é sinxela: d significa calquera díxito e o cuantificador + di que o seu número debería ser un ou máis. O dobre menos diante da función é necesario para converter "sobre a marcha" os caracteres extraídos nun número completo do número como texto.

CEP

A primeira vista, aquí todo é sinxelo: buscamos exactamente seis díxitos seguidos. Usamos un carácter especial d para díxito e cuantificador 6 {} para o número de caracteres:

Non obstante, é posible unha situación na que, á esquerda do índice da liña, hai outro gran conxunto de números seguidos (número de teléfono, TIN, conta bancaria, etc.) Entón a nosa tempada regular sacará os primeiros 6. díxitos del, é dicir, non funcionará correctamente:

Para evitar que isto ocorra, necesitamos engadir un modificador ao redor dos bordos da nosa expresión regular b que indica o final dunha palabra. Isto deixará claro a Excel que o fragmento (índice) que necesitamos debe ser unha palabra separada e non parte doutro fragmento (número de teléfono):

teléfono

O problema de atopar un número de teléfono no texto é que hai moitas opcións para escribir números: con e sen guións, a través de espazos, con ou sen un código de rexión entre corchetes, etc. Polo tanto, na miña opinión, é máis doado primeiro limpa todos estes caracteres do texto fonte usando varias funcións aniñadas SUBSTITUTO (SUBSTITUTO)de xeito que se pegue nun único todo, e despois cun regular primitivo d{11} saca 11 díxitos seguidos:

ITN

Aquí é un pouco máis complicado, porque o TIN (no noso país) pode ser de 10 díxitos (para persoas xurídicas) ou de 12 díxitos (para persoas físicas). Se non atopas ningún fallo especialmente, entón é moi posible estar satisfeito co habitual d{10,12}, pero, en rigor, sacará todos os números de 10 a 12 caracteres, é dicir, e introduciu erroneamente 11 díxitos. Sería máis correcto usar dous patróns conectados por un operador OU lóxico | (barra vertical):

Teña en conta que na consulta buscamos primeiro números de 12 bits e só despois números de 10 bits. Se escribimos a nosa expresión regular ao revés, eliminarase para todos, incluso para os TIN longos de 12 bits, só para os primeiros 10 caracteres. É dicir, despois de que se activa a primeira condición, xa non se realiza unha verificación adicional:

Esta é a diferenza fundamental entre o operador | dunha función lóxica estándar de Excel OR (OU), onde reorganizar os argumentos non cambia o resultado.

SKU do produto

En moitas empresas, os identificadores únicos son asignados aos bens e servizos: artigos, códigos SAP, SKU, etc. Se hai lóxica na súa notación, pódense extraer facilmente de calquera texto mediante expresións regulares. Por exemplo, se sabemos que os nosos artigos sempre constan de tres letras maiúsculas inglesas, un guión e un número de tres díxitos posterior, entón:

A lóxica detrás do modelo é sinxela. [AZ] – significa calquera letra maiúscula do alfabeto latino. O seguinte cuantificador 3 {} di que é importante para nós que haxa exactamente tres letras deste tipo. Despois do guión, estamos esperando tres díxitos, polo que sumamos ao final d{3}

Cantidades en efectivo

Do mesmo xeito que no parágrafo anterior, tamén pode extraer prezos (gastos, IVE...) da descrición dos bens. Se as cantidades monetarias, por exemplo, se indican cun guión, entón:

patrón d con cuantificador + busca calquera número ata un guión e d{2} buscará centavos (dous díxitos) despois.

Se non precisa extraer prezos, senón IVE, pode utilizar o terceiro argumento opcional da nosa función RegExpExtract, que especifica o número ordinal do elemento que se vai extraer. E, por suposto, pode substituír a función SUBSTITUTO (SUBSTITUTO) nos resultados, pon un guión ao separador decimal estándar e engade un dobre menos ao principio para que Excel interprete o IVE atopado como un número normal:

Números de placa de coche

If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:

Tempo

Para extraer a hora no formato HH:MM, é adecuada a seguinte expresión regular:

Despois do fragmento de colon [0-5] d, como é fácil de descubrir, establece calquera número no rango 00-59. Antes dos dous puntos entre parénteses, funcionan dous patróns, separados por un OU lóxico (tubo):

• [0-1] d - calquera número no rango 00-19
• 2[0-3] - calquera número no rango 20-23

Ao resultado obtido, tamén pode aplicar a función estándar de Excel TEMPO (EQUIPO)para convertelo nun formato de tempo que sexa comprensible para o programa e axeitado para cálculos posteriores.

Comprobación de contrasinal

Supoñamos que necesitamos comprobar a corrección da lista de contrasinais inventados polos usuarios. Segundo as nosas regras, os contrasinais só poden conter letras inglesas (minúsculas ou maiúsculas) e números. Non se permiten espazos, guións baixos e outros signos de puntuación.

A comprobación pódese organizar mediante a seguinte expresión regular sinxela:

De feito, con tal patrón esiximos que entre o principio (^) e final ($) no noso texto só había caracteres do conxunto indicado entre corchetes. Se tamén precisa comprobar a lonxitude do contrasinal (por exemplo, polo menos 6 caracteres), entón o cuantificador + pódese substituír polo intervalo "seis ou máis" no formulario {6,}:

Cidade do enderezo

Digamos que necesitamos sacar a cidade da barra de enderezos. O programa regular axudará, extraendo o texto de "g". á seguinte coma:

Vexamos máis de cerca este patrón.

Se leu o texto anterior, xa entendeu que algúns caracteres das expresións regulares (puntos, asteriscos, signos de dólar, etc.) teñen un significado especial. Se precisa buscar estes personaxes eles mesmos, van precedidos dunha barra invertida (ás veces chamada Blindaxe). Polo tanto, ao buscar o fragmento "g". temos que escribir con expresión regular Señor se buscamos un plus, entón + etc.

Os dous seguintes caracteres do noso modelo, o punto e o asterisco cuantificador, representan calquera número de caracteres, é dicir, calquera nome de cidade.

Hai unha coma ao final do modelo, porque estamos a buscar texto de "g". a unha coma. Pero pode haber varias comas no texto, non? Non só despois da cidade, senón tamén despois da rúa, das casas, etc. ¿En cal delas parará a nosa petición? Para iso serve o signo de interrogación. Sen ela, a nosa expresión regular sacaría a cadea máis longa posible:

En termos de expresións regulares, tal patrón é "cobizoso". Para corrixir a situación, é necesario un signo de interrogación (fai o cuantificador despois do cal queda "tacaño") e a nosa consulta leva o texto só ata a primeira coma do contador despois de "g".

Nome do ficheiro da ruta completa

Outra situación moi común é extraer o nome do ficheiro da ruta completa. Unha simple expresión regular da forma axudará aquí:

O truco aquí é que a busca, de feito, ocorre na dirección oposta: desde o final ata o principio, porque ao final do noso modelo está $, e estamos a buscar todo antes ata a primeira barra invertida desde a dereita. Escápase a barra invertida, como o punto no exemplo anterior.

PS

"Ao final" quero aclarar que todo o anterior é unha pequena parte de todas as posibilidades que ofrecen as expresións regulares. Hai moitos personaxes especiais e regras para o seu uso, e escribiuse libros enteiros sobre este tema (recomendo polo menos este para comezar). En certo modo, escribir expresións regulares é case unha arte. Case sempre, unha expresión regular inventada pódese mellorar ou complementar, facéndoa máis elegante ou capaz de traballar cunha gama máis ampla de datos de entrada.

Para analizar e analizar expresións regulares doutras persoas ou depurar as súas propias, hai varios servizos en liña convenientes: RegEx 101, RegExr e máis

Desafortunadamente, non todas as funcións das expresións regulares clásicas están soportadas en VBA (por exemplo, a busca inversa ou as clases POSIX) e poden funcionar con Cyrillic, pero creo que o que hai é suficiente por primeira vez para agradarche.

Se non es novo no tema e tes algo que compartir, deixa expresións regulares útiles cando traballes en Excel nos comentarios a continuación. Unha mente é boa, pero dúas botas son un par!

• Substitución e limpeza de texto coa función SUBSTITUTE
• Search and highlighting of Latin characters in text
• Busca o texto semellante máis próximo (Ivanov = Ivonov = Ivanof, etc.)