Contenido
Una de las tareas más frustrantes y que lleva más tiempo cuando se trabaja con texto en Excel es Analizando – analizando la “papilla” alfanumérica en componentes y extrayendo los fragmentos que necesitamos de ella. Por ejemplo:
- extrayendo el código postal de la dirección (es bueno si el código postal siempre está al principio, pero ¿y si no es así?)
- encontrar el número y la fecha de la factura a partir de la descripción del pago en el extracto bancario
- extracción de TIN de descripciones variopintas de empresas en la lista de contrapartes
- buscar un número de coche o número de artículo en la descripción, etc.
Por lo general, en tales casos, después de media hora de aburrida selección manual del texto, comienzan a surgir ideas para automatizar este proceso de alguna manera (especialmente si hay una gran cantidad de datos). Existen varias soluciones y con distintos grados de complejidad-eficiencia:
- Uso funciones de texto de Excel integradas para buscar-cortar-pegar texto: LEVSIMV (IZQUIERDA), DERECHO (DERECHO), PSTR (medio), ESTÉPICO (CONCATENAR) y sus análogos, COMBINAR (TEXTO CONJUNTO), EXACT (EXACTO) etc. Este método es bueno si hay una lógica clara en el texto (por ejemplo, el índice siempre está al principio de la dirección). De lo contrario, las fórmulas se vuelven mucho más complicadas y, a veces, incluso se trata de fórmulas de matriz, lo que ralentiza mucho en tablas grandes.
- Usar como operador de similitud de texto de Visual Basic envuelto en una función de macro personalizada. Esto le permite implementar una búsqueda más flexible utilizando caracteres comodín (*, #,?, etc.). Desafortunadamente, esta herramienta no puede extraer la subcadena deseada del texto; solo verifique si está contenida en él.
Además de lo anterior, existe otro enfoque que es muy conocido en círculos reducidos de programadores profesionales, desarrolladores web y otros expertos en tecnología: este es expresiones regulares (Expresiones regulares = RegExp = "regexps" = "regulares"). Simplemente pon, RegExp es un lenguaje en el que se utilizan caracteres especiales y reglas para buscar las subcadenas necesarias en el texto, extraerlas o reemplazarlas con otro texto.. Las expresiones regulares son una herramienta muy poderosa y hermosa que supera a todas las demás formas de trabajar con texto en un orden de magnitud. Muchos lenguajes de programación (C#, PHP, Perl, JavaScript…) y editores de texto (Word, Notepad++…) admiten expresiones regulares.
Desafortunadamente, Microsoft Excel no tiene soporte RegExp listo para usar, pero esto se puede solucionar fácilmente con VBA. Abra el Editor de Visual Basic desde la pestaña revelador (Desarrollador) o atajo de teclado otro+F11. Luego inserte el nuevo módulo a través del menú. Insertar – Módulo y copie el texto de la siguiente macro función allí:
Función pública RegExpExtract(Texto como cadena, patrón como cadena, elemento opcional como entero = 1) Como cadena en caso de error GoTo ErrHandl Establecer regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Texto) Luego Establecer coincidencias = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Salir de la función Finalizar si ErrHandl: RegExpExtract = CVErr(xlErrValue) Finalizar la función Ahora podemos cerrar el Editor de Visual Basic y volver a Excel para probar nuestra nueva característica. Su sintaxis es la siguiente:
=RegExpExtract( Txt ; Patrón ; Elemento )
donde
- txt – una celda con el texto que estamos revisando y de donde queremos extraer la subcadena que necesitamos
- patrón – máscara (patrón) para la búsqueda de subcadenas
- Asunto – el número de secuencia de la subcadena que se va a extraer, si hay varias (si no se especifica, se muestra la primera aparición)
Lo más interesante aquí, por supuesto, es Pattern: una plantilla de cadena de caracteres especiales "en el idioma" de RegExp, que especifica exactamente qué y dónde queremos encontrar. Aquí están los más básicos para empezar:
| Patrón de Costura | Descripción |
| . | El más simple es un punto. Coincide con cualquier carácter del patrón en la posición especificada. |
| s | Cualquier carácter que parezca un espacio (espacio, tabulador o salto de línea). |
| S | Una antivariante del patrón anterior, es decir, cualquier carácter que no sea un espacio en blanco. |
| d | Cualquier número |
| D | Una antivariante de la anterior, es decir, cualquier dígito NOT |
| w | Cualquier carácter latino (AZ), dígito o guión bajo |
| W | Una antivariante de la anterior, es decir, no latín, ni número, ni guión bajo. |
| [personajes] | Entre corchetes, puede especificar uno o más caracteres permitidos en la posición especificada en el texto. Por ejemplo Arte coincidirá con cualquiera de las palabras: mesa or Presidente. Tampoco puede enumerar caracteres, sino configurarlos como un rango separado por un guión, es decir, en lugar de [ABDCDEF] escribir [AF]. o en su lugar [ 4567 ] introducir [-4 7]. Por ejemplo, para designar todos los caracteres cirílicos, puede utilizar la plantilla [a-yaA-YayoYo]. |
| [^personajes] | Si después del corchete de apertura se añade el símbolo “tapa” ^, entonces el conjunto adquirirá el significado opuesto: en la posición especificada en el texto, se permitirán todos los caracteres, excepto los enumerados. si, plantilla [^ЖМ]ut encontrará Path or Sustancia or olvidar, Pero no De miedo or Valor, p.ej. |
| | | operador booleano OR (OR) para verificar cualquiera de los criterios especificados. Por ejemplo (сjue|sincluso|factura) buscará en el texto cualquiera de las palabras especificadas. Por lo general, un conjunto de opciones se incluye entre paréntesis. |
| ^ | Comienzo de linea |
| $ | End of line |
| b | Fin de la palabra |
Si estamos buscando una cierta cantidad de caracteres, por ejemplo, un código postal de seis dígitos o todos los códigos de productos de tres letras, entonces venimos al rescate. cuantificadores or cuantificadores son expresiones especiales que especifican el número de caracteres a buscar. Los cuantificadores se aplican al carácter que le precede:
| Quantor | Descripción |
| ? | Cero o una ocurrencia. Por ejemplo .? significará cualquier carácter o su ausencia. |
| + | Una o más entradas. Por ejemplo d+ significa cualquier número de dígitos (es decir, cualquier número entre 0 e infinito). |
| * | Cero o más ocurrencias, es decir, cualquier cantidad. Asi que s* significa cualquier número de espacios o ningún espacio. |
| {número} or {Número1,Número2} | Si necesita especificar un número estrictamente definido de ocurrencias, entonces se especifica entre llaves. Por ejemplo d{6} significa estrictamente seis dígitos, y el patrón s{2,5} – de dos a cinco espacios |
Ahora pasemos a la parte más interesante: un análisis de la aplicación de la función creada y lo que aprendimos sobre patrones en ejemplos prácticos de la vida.
Extraer números del texto
Para empezar, analicemos un caso simple: debe extraer el primer número de la papilla alfanumérica, por ejemplo, el poder de los sistemas de alimentación ininterrumpida de la lista de precios:
La lógica detrás de la expresión regular es simple: d significa cualquier dígito, y el cuantificador + dice que su número debe ser uno o más. El doble menos delante de la función es necesario para convertir "sobre la marcha" los caracteres extraídos en un número completo del número como texto.
Código postal
A primera vista, aquí todo es simple: estamos buscando exactamente seis dígitos seguidos. Usamos un caracter especial d para dígito y cuantificador 6 {} por el número de caracteres:
Sin embargo, es posible una situación en la que, a la izquierda del índice en la línea, hay otro gran conjunto de números seguidos (número de teléfono, TIN, cuenta bancaria, etc.). Luego, nuestra temporada regular sacará los primeros 6 dígitos de él, es decir, no funcionará correctamente:
Para evitar que esto suceda, debemos agregar un modificador alrededor de los bordes de nuestra expresión regular b que significa el final de una palabra. Esto dejará en claro a Excel que el fragmento (índice) que necesitamos debe ser una palabra separada y no parte de otro fragmento (número de teléfono):
Teléfono
El problema de encontrar un número de teléfono en el texto es que hay tantas opciones para escribir números, con y sin guiones, entre espacios, con o sin código de región entre paréntesis, etc. Por lo tanto, en mi opinión, es más fácil primero limpie todos estos caracteres del texto fuente usando varias funciones anidadas SUSTITUIR (SUSTITUTO)para que se pegue en un solo todo, y luego con un primitivo regular d{11} sacar 11 dígitos seguidos:
ITN
Aquí es un poco más complicado, porque el TIN (en Nuestro País) puede ser de 10 dígitos (para personas jurídicas) o de 12 dígitos (para personas físicas). Si no encuentra fallas especialmente, entonces es muy posible estar satisfecho con el regular d{10,12}, pero, en sentido estricto, extraerá todos los números de 10 a 12 caracteres, es decir, e ingresará erróneamente 11 dígitos. Sería más correcto usar dos patrones conectados por un operador lógico OR | (barra vertical):
Tenga en cuenta que en la consulta primero buscamos números de 12 bits y solo luego números de 10 bits. Si escribimos nuestra expresión regular al revés, se extraerá para todos, incluso los TIN largos de 12 bits, solo los primeros 10 caracteres. Es decir, después de que se activa la primera condición, ya no se realiza más verificación:
Esta es la diferencia fundamental entre el operador | de una función lógica estándar de Excel OR (OR), donde reorganizar los argumentos no cambia el resultado.
SKU de producto
En muchas empresas, se asignan identificadores únicos a bienes y servicios: artículos, códigos SAP, SKU, etc. Si hay lógica en su notación, se pueden extraer fácilmente de cualquier texto mediante expresiones regulares. Por ejemplo, si sabemos que nuestros artículos siempre constan de tres letras inglesas mayúsculas, un guión y un número posterior de tres dígitos, entonces:
La lógica detrás de la plantilla es simple. [ARIZONA] – significa cualquier letra mayúscula del alfabeto latino. El siguiente cuantificador 3 {} dice que es importante para nosotros que haya exactamente tres de esas letras. Después del guión, estamos esperando tres dígitos, por lo que agregamos al final d{3}
importes en efectivo
De forma similar al párrafo anterior, también puedes sacar precios (gastos, IVA…) de la descripción de las mercancías. Si las cantidades monetarias, por ejemplo, se indican con un guión, entonces:
Patrón de Costura d con cuantificador + busca cualquier número hasta un guión, y d{2} buscará centavos (dos dígitos) después.
Si necesita extraer no los precios, sino el IVA, puede utilizar el tercer argumento opcional de nuestra función RegExpExtract, que especifica el número ordinal del elemento que se va a extraer. Y, por supuesto, puede reemplazar la función SUSTITUIR (SUSTITUTO) en los resultados, agregue un guión al separador decimal estándar y agregue un doble menos al principio para que Excel interprete el IVA encontrado como un número normal:
Números de placa de coche
Si no se transportan vehículos especiales, remolques y otras motocicletas, el número de vehículo estándar se analiza según el principio “letra – tres números – dos letras – código de región”. Además, el código de región puede tener 2 o 3 dígitos, y solo se utilizan como letras aquellas que son similares en apariencia al alfabeto latino. Así, la siguiente expresión regular nos ayudará a extraer números del texto:
Horario
Para extraer la hora en el formato HH:MM, es adecuada la siguiente expresión regular:
Después del fragmento de colon [0-5] día, como es fácil de entender, establece cualquier número en el rango 00-59. Antes de los dos puntos entre paréntesis, funcionan dos patrones, separados por un OR lógico (tubería):
- [0-1] día – cualquier número en el rango 00-19
- 2 [0-3] – cualquier número en el rango 20-23
Al resultado obtenido, puede aplicar adicionalmente la función estándar de Excel HORA (EQUIPO)para convertirlo a un formato de tiempo que sea comprensible para el programa y adecuado para cálculos posteriores.
Confirmacion de contraseña
Supongamos que necesitamos verificar la lista de contraseñas inventadas por los usuarios para verificar que sean correctas. De acuerdo con nuestras reglas, las contraseñas solo pueden contener letras en inglés (minúsculas o mayúsculas) y números. No se permiten espacios, guiones bajos y otros signos de puntuación.
La comprobación se puede organizar utilizando la siguiente expresión regular simple:
De hecho, con tal patrón requerimos que entre el comienzo (^) y punto ($) en nuestro texto solo había caracteres del conjunto entre corchetes. Si también necesita verificar la longitud de la contraseña (por ejemplo, al menos 6 caracteres), entonces el cuantificador + puede ser reemplazado por el intervalo “seis o más” en la forma {6,}:
Ciudad desde la dirección
Digamos que necesitamos sacar la ciudad de la barra de direcciones. El programa regular ayudará, extrayendo el texto de "g". a la siguiente coma:
Echemos un vistazo más de cerca a este patrón.
Si ha leído el texto anterior, entonces ya entendió que algunos caracteres en las expresiones regulares (puntos, asteriscos, signos de dólar, etc.) tienen un significado especial. Si necesita buscar estos caracteres, entonces están precedidos por una barra invertida (a veces llamada blindaje). Por lo tanto, al buscar el fragmento “g.” tenemos que escribir en expresión regular señor. si estamos buscando un plus, entonces + etc.
Los siguientes dos caracteres en nuestra plantilla, el punto y el asterisco cuantificador, representan cualquier número de cualquier carácter, es decir, cualquier nombre de ciudad.
Hay una coma al final de la plantilla, porque estamos buscando texto de "g". a una coma. Pero puede haber varias comas en el texto, ¿verdad? No sólo después de la ciudad, sino también de la calle, las casas, etc. ¿En cuál de ellos se detendrá nuestra petición? Para eso está el signo de interrogación. Sin ella, nuestra expresión regular sacaría la cadena más larga posible:
En términos de expresiones regulares, dicho patrón es "codicioso". Para corregir la situación, se necesita un signo de interrogación (hace que el cuantificador sea "tacaño") y nuestra consulta toma el texto solo hasta la primera contracoma después de "g":
Nombre de archivo de la ruta completa
Otra situación muy común es extraer el nombre del archivo de la ruta completa. Una simple expresión regular de la forma ayudará aquí:
El truco aquí es que la búsqueda, de hecho, ocurre en la dirección opuesta, desde el final hasta el principio, porque al final de nuestra plantilla está $, y buscamos todo lo anterior hasta la primera barra invertida desde la derecha. La barra diagonal inversa se escapa, como el punto en el ejemplo anterior.
PS
“Hacia el final” quiero aclarar que todo lo anterior es una pequeña parte de todas las posibilidades que brindan las expresiones regulares. Hay muchos caracteres especiales y reglas para su uso, y se han escrito libros completos sobre este tema (recomiendo al menos este para empezar). En cierto modo, escribir expresiones regulares es casi un arte. Casi siempre, una expresión regular inventada se puede mejorar o complementar, haciéndola más elegante o capaz de trabajar con una gama más amplia de datos de entrada.
Para analizar y analizar las expresiones regulares de otras personas o depurar las suyas propias, existen varios servicios en línea convenientes: RegEx101, RegExr Y mucho más.
Desafortunadamente, no todas las características de las expresiones regulares clásicas son compatibles con VBA (por ejemplo, búsqueda inversa o clases POSIX) y pueden funcionar con cirílico, pero creo que lo que hay es suficiente para complacerlo por primera vez.
Si no es nuevo en el tema y tiene algo que compartir, deje expresiones regulares útiles cuando trabaje en Excel en los comentarios a continuación. ¡Una mente es buena, pero dos botas son un par!
- Reemplazar y limpiar texto con la función SUSTITUIR
- Búsqueda y resaltado de caracteres latinos en texto.
- Busque el texto similar más cercano (Ivanov = Ivonov = Ivanof, etc.)