¿Cómo extraer el código postal de una lista de direcciones en Excel?

Al gestionar información de clientes en Excel, es común tener una lista de direcciones completas donde cada entrada contiene tanto detalles de la calle como un código postal. Si necesitas aislar y extraer solo los códigos postales de estas direcciones, tal vez para listas de correo, análisis regionales o formato de datos, separarlos manualmente uno por uno puede ser tedioso y propenso a errores, especialmente con grandes volúmenes de datos. Afortunadamente, Excel ofrece formas prácticas de extraer rápidamente múltiples códigos postales a la vez sin esfuerzo manual. Este artículo proporciona instrucciones paso a paso para extraer códigos postales de direcciones usando una fórmula de Excel y una solución mediante macro VBA.

Extraer código postal con fórmula en Excel

Extraer código postal con Función Definida por el Usuario en Excel

Extraer código postal con fórmula en Excel

En muchas listas de direcciones comunes, el código postal se encuentra al final de la cadena de dirección y no tiene más de 8 caracteres. Este método es más adecuado cuando todas las direcciones en tu lista siguen un formato consistente y el código postal aparece como el último elemento de cada dirección, separado por espacios.

Para extraer rápidamente el código postal utilizando una fórmula, sigue estos pasos:

1. Selecciona una celda en blanco donde deseas que aparezca el código postal (por ejemplo, B1 si tu dirección está en A1). Introduce la siguiente fórmula:

=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))

2. Presiona la tecla Enter. El código postal de la dirección en A1 se mostrará en la celda seleccionada.

3. Para aplicar esta fórmula a otras direcciones, selecciona la celda con la fórmula, arrastra el controlador de relleno hacia abajo a lo largo de la columna para cubrir todas las filas de direcciones, y Excel extraerá automáticamente el código postal para cada dirección.

Consejo: Esta fórmula asume que el código postal es el último elemento después del último espacio en la cadena de dirección. Si tus direcciones están estructuradas de manera diferente, como códigos postales que aparecen en medio o separados por comas, esta fórmula puede no devolver el resultado correcto. Para extender este método a códigos postales internacionales u otros formatos personalizados, podrías necesitar un enfoque más avanzado o probar la solución VBA a continuación.

Extraer código postal con Función Definida por el Usuario en Excel

Si tus datos de dirección son más complejos, inconsistentes o contienen códigos postales en varios formatos y posiciones dentro de la cadena, como se muestra en la siguiente captura de pantalla, una macro VBA puede ofrecer opciones de extracción más flexibles. Este método automatizado procesa rápidamente toda una columna de direcciones para aislar y devolver solo los códigos postales, ahorrando tiempo significativo y minimizando el esfuerzo manual.

1. Presiona Alt + F11 para abrir la ventana de Microsoft Visual Basic for Applications.

2. En la ventana de VBA, haz clic en Insertar > Módulo para crear un nuevo módulo. Copia y pega el siguiente código VBA en la ventana del módulo:

Public Function ExtractPostcode(text As String) As String
    Dim reg As New RegExp
    Dim m As MatchCollection
    reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
    reg.IgnoreCase = True
    reg.Global = False
    
    If reg.Test(text) Then
        Set m = reg.Execute(text)
        ExtractPostcode = m(0).Value
    Else
        ExtractPostcode = ""
    End If
End Function

3. Después de pegar el código, en la ventana del editor de VBA, elige Herramientas > Referencias. Ver captura de pantalla:

4. En el cuadro de diálogo Referencias, marca Microsoft VBScript Regular Expressions 5.5 y haz clic en Aceptar.

5. Regresa a la hoja de cálculo e introduce esta fórmula: =ExtractPostcode(A2), arrastra el controlador de relleno hacia abajo a otras celdas. Todos los códigos postales se muestran de inmediato, ver captura de pantalla:

Consejo: Con este código, puedes extraer automáticamente códigos postales de cualquier país o región en Excel en segundos; simplemente ajustando la expresión regular para coincidir con las reglas de código postal de tu área objetivo, puedes adaptarte rápidamente a diferentes formatos, como el “SW1A 1AA” del Reino Unido, el “12345-6789” de EE.UU. o el “100000” de China, mejorando enormemente la eficiencia de limpieza y análisis de datos.

Artículos relacionados:

• Extraer iniciales de nombres en Excel
• Extraer valores decimales de cadenas en Excel