Buscar y reemplazar cadenas de texto con VBA

Para reemplazar puntualmente texto y números en una hoja de Excel, en la pestaña Inicio, en el grupo Edición, hacemos clic en Buscar y seleccionar. O bien CTRL+L. Si tenemos que reemplazar así varias cadenas de texto el procedimiento es tedioso y lento.

Una manera de acelerar y automatizar el mismo es mediante VBA, creando una función definida por el usuario. Veamos un ejemplo:

En la tabla anterior, columna A, tenemos un campo fecha descargado de un sistema que las procesa en inglés. Cuando tratamos de operar con las fechas nos devuelve un error porque Excel, con el idioma local en español, no reconoce las iniciales de los meses de enero (JAN), abril (APR), agosto(AUG), y diciembre (DEC). Necesitamos sustituir esas cuatro cadenas de texto. Para reemplazarlas creamos en un módulo de VBA, la siguiente función de usuario.

Public Function BuscarCadena(Reemplazarcadena As String) As String

Dim Originales As Variant, Sustituciones As Variant
Dim i As Long
Originales = Array("JAN", "APR", "AUG", "DEC")
Sustituciones = Array("ENE", "ABR", "AGO", "DIC")
BuscarCadena = Reemplazarcadena
    For i = 0 To 3 'Los subíndices de matriz empiezan en 0,
                   'por tanto Originales(0) = "JAN"[...] Originales(3)="DEC"
    BuscarCadena = Replace(BuscarCadena, Originales(i), Sustituciones(i), _
    compare:=vbTextCompare)
    Next

End Function

El resultado final, con los cambios resaltados en naranja, es:

En la celda C2 introducimos la función definida en VBA =BuscarCadena(A2) y arrastramos la fórmula hasta C13. Si queremos en lugar de 01-ENE-2013 de la columna C el formato de fecha corta (dd/mm/aaaa) de la columna D hay tres opciones:

1. Aplicar el formato en VBA

BuscarCadena = Format(Replace(BuscarCadena, Originales(i), Sustituciones(i), _
compare:=vbTextCompare), "dd/mm/yyyy")

2.Multiplicar por 1 la columna C. Al operar le cambia el formato a fecha corta.

3. Usar la función en D2 =FECHANUMERO(C1) y arrastrar hacia abajo el controlador de relleno. Nos devuelve el número de serie secuencial que representa una fecha determinada, 41275 para C1. A continuación, para darles el formato deseado, seleccionamos las celdas D2:D13. Y en la ficha Inicio, en el grupo Número, clic en la flecha situada en la esquina inferior derecha. En el cuadro de diálogo Formato de celdas, seleccionamos en la categoría Fecha el primer tipo.

También podemos acceder al cuadro de diálogo Formato de celdas presionando CTRL+1.