Tutorial de regex en Excel: domina la búsqueda de patrones con expresiones regulares

Cómo usar regex en Excel

Aunque Excel no es compatible con regex de forma nativa, puedes habilitarlas creando tu propia definición personalizada con un script VBA (en Windows) o activando complementos (en Mac). Veamos paso a paso cómo activar una función de regex para extraer texto de una columna en Excel.

Para Windows

Para empezar, pulsa Alt+F11 para abrir el Editor de Visual Basic. También puedes ir a Herramientas > Macro > Editor de Visual Basic. Luego, haz clic en Insertar > Módulo.

Pega la siguiente función en el 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

Guarda la función y vuelve a la hoja de cálculo. Ahora, guarda el libro como habilitado para macros, con la extensión .xlsm.

Usar la función en una hoja de Excel

Al habilitar macros en tu libro de Excel, ya puedes usar la función CustomRegExpExtract como una función normal en Excel para analizar patrones de cadena complejos, donde:

• inputText es la cadena original que se va a analizar;

• regexPattern es el patrón de expresión regular que se usa para analizar inputText;

• instanceNumber devuelve, por defecto, todas las apariciones del patrón, pero puedes introducir un entero para especificar cuántas instancias devolver; y

• matchCase indica si se debe tener en cuenta la distinción entre mayúsculas y minúsculas durante la coincidencia (TRUE o en blanco) o ignorarla (FALSE).

Imagina que queremos extraer números de teléfono de un campo. Podemos escribir el patrón regex en cualquier celda (en el ejemplo, aparece en la celda A2). En la celda A6 está el texto libre de ejemplo, "Sophia, 1111111111". En la celda B6, escribimos =CustomRegExpExtract(A6,$A$2). La función extrae el número de teléfono al hacer coincidir la cadena con el patrón de la celda A2 y devuelve "1111111111", como se esperaba.

Para Mac OS

Por desgracia, Mac OS no reconoce la biblioteca MS VBScript Regular Expressions 5.5. Para habilitar coincidencia de patrones complejos en Excel para Mac, tienes dos opciones: usar filtros avanzados o instalar complementos.

Uso de filtros avanzados

Los filtros avanzados de Excel ayudan a realizar operaciones de filtrado complejas basadas en múltiples criterios. Esta función es especialmente útil con conjuntos de datos grandes. Así es como se usan los filtros avanzados en Excel:

• Prepara tus datos: asegúrate de que tu información está en formato de tabla con encabezados claros.

• Crea un rango de criterios: define en una zona aparte de la hoja los criterios de filtrado. Esta zona debe tener los mismos encabezados que las columnas a filtrar. Debajo de esos encabezados, especifica los criterios. Puedes introducir varios criterios bajo el mismo encabezado para lógica "OR" o en filas separadas para lógica "AND".

• Selecciona los datos: haz clic en una celda del conjunto de datos o selecciona todo el rango que quieras filtrar.

• Configura el filtro: haz clic en la pestaña Datos de la cinta de Excel y ve a la opción Filtro avanzado. Pulsa Avanzadas en el grupo Ordenar y filtrar. En el cuadro de diálogo de Filtro avanzado, elige si quieres filtrar en el lugar o copiar los resultados a otra ubicación. Especifica el rango de lista (tus datos). Especifica el rango de criterios (donde definiste las condiciones). Haz clic en Aceptar para aplicar el filtro.

Supón que tienes un conjunto de datos con las columnas Name, Age, City. Quieres filtrar para mostrar personas mayores de 30 en New York o a cualquiera en Los Angeles. Tu rango de criterios podría tener este aspecto:

Instalar complementos

• Kutools for Excel: Kutools es un complemento muy completo que incluye muchas funciones, entre ellas buscar y reemplazar datos usando regex. Añade una nueva pestaña a la cinta de Excel con una amplia gama de herramientas, incluidas funciones de regex.

• RegEx Find/Replace: es un complemento para Excel que permite buscar y reemplazar texto usando expresiones regulares. Se integra en Excel y ofrece una funcionalidad de búsqueda/reemplazo más avanzada que la opción nativa.

• PowerGREP: aunque no es un complemento directo de Excel, PowerGREP puede trabajar con archivos de Excel. Es una potente herramienta grep para Windows que permite operaciones regex complejas sobre archivos de texto y binarios. Es más una aplicación independiente, pero puede usarse junto con Excel para procesamiento avanzado con regex. Eso sí, no es barato: una licencia de un solo usuario cuesta 159 $ y el precio sube hasta 5.300 $ para 100 usuarios.

Algunos complementos pueden encontrarse directamente en Excel desde la pestaña Insertar > Obtener complementos.

Otros hay que descargarlos de una fuente externa y seguir sus instrucciones de instalación.

Algunas consideraciones:

• Compatibilidad: asegúrate de que el complemento es compatible con tu versión de Excel y tu sistema operativo.
• Seguridad: descarga e instala complementos solo de fuentes fiables.
• Necesidades funcionales: piensa en las tareas de regex concretas que necesitas realizar y elige el complemento que mejor se adapte.

Conclusión

En la mayoría de los casos, funciones nativas como FIND, SEARCH y REPLACE cubrirán el 90% de tus necesidades de manipulación de texto en Excel. Sin embargo, para el 10% restante, cuando intervienen patrones de cadenas más complejos, regex puede ser de enorme ayuda. Para aprender más sobre cómo aplicar expresiones regulares con lenguajes de programación populares, echa un vistazo a nuestros cursos de Regular Expressions in Python e Intermediate Regular Expressions in R.

Para ponerte al día con todo lo relacionado con Excel, explora nuestro itinerario de aprendizaje Excel Fundamentals, que te guía por las habilidades esenciales para dominar la herramienta.