Dan tiene una columna de celdas y cada celda contiene tres elementos: ciudad, estado y código postal. (Los tres están en una sola celda, como se ve en una línea de dirección). Algunos de los códigos postales tienen cinco dígitos y otros nueve. Dan necesita introducir tanto el estado de dos caracteres como el código postal de cinco dígitos en sus propias celdas, a la derecha de los datos actuales. Dan sabe que puede usar la herramienta Texto a columnas, pero siente que implica mucho trabajo, ya que tendría que lidiar con nombres de ciudades y comas de varias palabras. Dan no puede evitar pensar que puede haber un enfoque basado en fórmulas que sería más fácil.

Es necesario que se hagan algunas suposiciones sobre los datos para poder hacer recomendaciones. Supongamos, por ejemplo, que todos los datos están en este formato:

My Town, CA 98765-4321

La parte desde el guión en adelante (la parte final del código postal) es opcional, pero la posición de la coma es estática (siempre sigue al nombre de la ciudad) y el estado siempre consta de dos caracteres. En este caso, es fácil idear dos fórmulas que extraigan la abreviatura del estado y los primeros cinco dígitos del código postal:

=MID(A1,FIND(",",A1)+2,2)

=MID(A1,FIND(",",A1)+5,5)

Ambas fórmulas se introducen en la coma; sirve como delimitador entre la ciudad y los dos elementos que realmente se buscan. Si no hay una coma en los datos o si hay varias comas, las fórmulas no devolverán la información deseada.

Las fórmulas también asumen que no hay espacios adicionales en sus datos; como máximo, hay un solo espacio después de la coma y entre el estado y el código postal. Esto, por supuesto, es bastante fácil de aplicar: solo use Buscar y reemplazar para reemplazar dos espacios con un solo espacio en cualquier lugar de su hoja de trabajo.

Por supuesto, si sus datos están así de estructurados, aún puede confiar en la herramienta Texto a columnas para hacer su trabajo. Todo lo que necesita hacer es ejecutar la herramienta y dividir sus datos en función de la coma. Esto dejará la ciudad en una celda y pondrá el estado y el código postal juntos en la siguiente celda. Luego, puede usar Text to Columns nuevamente, esta vez en la segunda celda (no en el nombre de la ciudad) y dividir el contenido según el espacio.

Si sus datos no están tan estructurados, tal vez tengan varias comas en la dirección o espacios adicionales, entonces se requiere un enfoque completamente diferente. Para lidiar con esto, la técnica básica implica recortar los datos para eliminar los espacios extraños (iniciales, finales e internos) y luego determinar la ubicación del último espacio y el penúltimo espacio.

Puede extraer los cinco dígitos en el código postal, que se define inmediatamente después del último espacio en los datos, utilizando esta fórmula:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

La abreviatura del estado de dos caracteres se puede devolver sacando los dos caracteres que siguen inmediatamente al penúltimo espacio:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

Si sus datos están aún menos estructurados, tal vez incluyen direcciones que no todas tienen abreviaturas de estado de dos caracteres (N.J. en lugar de NJ), entonces sería mejor que utilizara una macro para dividir los datos.

La razón de esto es que VBA tiene un conjunto mucho más rico de funciones de manejo de texto que lo que puede hacer usando fórmulas de Excel. La siguiente macro crea una función definida por el usuario que puede devolver el estado o el código postal:

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function     arr = Split(rstrAddress, " ")

With arr         If UBound(arr) < 2 Then             sState = "?"

sZIP = "?"

Else             sState = arr(UBound(arr) - 1)

sZIP = arr(UBound(arr))

End If     End With     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

Para usar esta función, simplemente proporcione una referencia de celda y 1 (si desea el estado) o 2 (si desea el código postal). Aquí hay un ejemplo de cómo solicitar el código postal para cualquier dirección que esté en la celda A1:

=GetStateZIP(A1,2)

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (9599) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Extracting_a_State_and_a_ZIP_Code [Extrayendo un estado y un código postal].