Código:

Question

Tengo una columna de direcciones en este formato:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

Y necesito dividirlo en: dirección (120 Lemon Street), ciudad (Columbus), estado (OH), código postal (92738)y descripción.((Basketball Courts))

¿Hay alguna manera de hacer esto? Todos estos están en el mismo estado, por lo que eso no es un problema. Están en diferentes ciudades/pueblos y tienen diferentes códigos postales.

Si solo sabes cómo hacer una parte, házmelo saber. Se agradece cualquier ayuda, ¡gracias!

Answer 1

Usuario1282637, he configurado un ejemplo para mostrarle a continuación. Solo lo he hecho como un punto de partida para que puedas terminar con tu propia respuesta completa.

Primero, configuré dos listas. Una eran todas las abreviaturas estatales y la otra eran todos los sufijos callejeros aceptados que encontré aquí:

http://pe.usps.com/text/pub28/28apc_002.htm

Utilicé la fórmula que enumeré simplemente para convertir la lista para que comience con una letra mayúscula y luego el resto en minúsculas, como tú escribiste la tuya.

Lo siguiente es simplemente buscar lo que aplica.

Esta parte se puede hacer de muchas maneras, pero me decidí por esta como ejemplo. Simplemente enumerará un número en la fila correspondiente que encuentre una coincidencia para el sufijo utilizado.

Separo la parte (Canchas de baloncesto) usando el paréntesis:

Muestro lo que queda porque en su caso "Canchas" de "Canchas de Baloncesto" también es un sufijo de calle:

A continuación necesito encontrar qué tan larga es la cadena que es el sufijo de la calle, así que uso lo siguiente:

y esto...

y por último esto me permite mostrar solo la calle:

Ahora no he hecho la ciudad y el estado, pero siguiendo esta idea, puedes lograrlo. Además, si desea una opción más limpia, definitivamente busque aprender más sobre VBA. Espero que esto al menos te enseñe algunas ideas sobre cómo hacerlo.

Answer 2

El usuario1282637 pregunta si hay alguna manera de realizar esta tarea y solicita ayuda para hacerlo. El problema no es tanto la mecánica de Excel sino cómo lidiar con la ambigüedad de los datos. Analizar el código postal y la descripción es sencillo (a menos que tenga una combinación de códigos postales de 5 y 9 dígitos). El problema difícil está en separar la calle de la ciudad, así que me centraré en eso. Esto no pretende ser una guía paso a paso sobre cómo hacerlo con fórmulas de Excel. Se trata simplemente de compartir una idea del problema y describir un enfoque para llegar a un resultado en la parte difícil.

El problema es que no existen delimitadores entre los diferentes campos. Eso no es un problema para quitar la descripción o el ZIP porque son fácilmente identificables. El problema es determinar dónde termina la Calle y comienza la Ciudad. Considere estas variaciones en la parte de la calle (lejos de ser una lista exhaustiva):

120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3

El número de "palabras" en la calle puede variar desde 1 o 2 hasta 7 u 8, por lo que no es útil para el análisis. El "tipo" de calle tampoco es especialmente útil. Hay del orden de 50 a 100 palabras que se usan solo para el "tipo" de calle (calle, avenida, bulevar, camino, autopista de peaje, carril, patio, círculo, terraza, etc.). Combine eso con el uso de abreviaturas para el tipo de calle, tanto correctas como incorrectas, y la lista es de cientos. Además, esta designación no siempre es la última palabra en el campo callejero. La calle es la parte más difícil de identificar, por lo que el enfoque lógico es identificar el resto y luego el resto es la calle.

La ciudad puede ser varias palabras. Washington Court House, OH son tres palabras. Luego considere situaciones como St Marys, OH. ¿Es "St" parte del nombre de la ciudad o una designación de tipo de calle? ¿En qué campo va? O South Euclid, OH: ¿"Sur" es parte del nombre de la ciudad o una dirección que forma parte de la dirección de la calle? La ciudad tiene sus problemas pero hay una manera de solucionarlos.

Incluso utilizar el código postal para identificar la ciudad tiene problemas. No siempre hay una coincidencia 1:1 entre el nombre de la ciudad y el código postal.

La forma más práctica de atacar el problema es utilizar "diccionarios": una lista de ciudades y un directorio de códigos postales. Ésta es la parte más inequívoca del discurso. Estos se pueden encontrar en línea o en el Servicio Postal. Para hacer comparaciones, es posible que necesites limpiar tus datos o los listados. Necesitarán el mismo estilo de capitalización y cualquier espacio adicional en sus datos impedirá una coincidencia exacta.

Si sus datos o el listado utilizan abreviaturas, deberá ocuparse de ello. Traduzca la no abreviada a las abreviaturas estándar o haga una comparación secundaria con un diccionario de abreviaturas (también disponible en línea o en el Servicio Postal), cuando se encuentren esas diferencias.

El ZIP se puede analizar fácilmente y ese es un buen punto de partida. Realice una búsqueda de código postal en el directorio de códigos postales. Si el resultado coincide exactamente con una cadena de palabras que precede inmediatamente al código postal, eso identifica qué parte del registro es el campo de ciudad.

Si no hay una coincidencia exacta o inequívoca, pase a una comparación de nombres de ciudades. Repita la lista de nombres de ciudades. Para cada nombre, determine la cantidad de palabras que contiene y compárelo con la cantidad de palabras que preceden inmediatamente al código postal.

Si obtiene una coincidencia a través de cualquiera de los procesos, todo lo que queda a la izquierda de la ciudad es la dirección de la calle.

Este tipo de aplicación es mucho más fácil de realizar con una aplicación de base de datos que con una hoja de cálculo. De cualquier manera, puedes ver que intentar hacer esto de forma automatizada no es una tarea sencilla. No puedes hacerlo con algunas fórmulas de hoja de cálculo.

No importa cuán rigurosa sea su programación, es probable que aún tenga registros que deba analizar a mano y errores de análisis que deba corregir a mano. No indicas cuantos registros tienes. Puede que sea menos trabajo hacerlo simplemente manualmente.

Si el número es grande y tuviera que hacerlo, seleccionaría la lista. Haga coincidir los registros que son fáciles, como coincidencias ZIP inequívocas. Luego, deja que la cantidad de datos determine hasta dónde llegas programando coincidencias automáticas.

Para los registros que quedan, suponiendo que ya hayas quitado el ZIP y la descripción, aquí tienes una manera de acelerar el proceso manual. Mire el registro e identifique visualmente el número de "palabras" de la ciudad, lo cual es una tarea mental rápida. Ingrese eso en una celda prescrita y use una fórmula para dividir la calle de la ciudad según la cantidad de espacios (separe en el enésimo espacio donde N = espacios totales + 1 - número de palabras en el nombre de la ciudad).

Answer 3

Probar esto. Este sencillo enfoque debería funcionar muy bien si puedes vivir teniendo la dirección y la ciudad en la misma celda. Tengo una fórmula para extraer la ciudad si es solo una palabra, pero se vuelve mucho más compleja si hay ciudades con varias palabras (por ejemplo, Nueva York).

Fórmulas... Dirección y ciudad: =LEFT(A2,FIND("OH",A2)-1) Estado: =MID(A2,FIND("OH",A2),2) - mencionaste que todo es OH, así que lo mantuve simple Código postal: =MEDIO(A2,BUSCAR("OH",A2)+3,5) Descripción: =RECORTAR(MEDIO(A2,BUSCAR("OH",A2)+8,30))

https://onedrive.live.com/redir?page=view&resid=D91C36B074F4D0F6!3224&authkey=!AO_MbW7Qxv4yWDo

Answer 4

Código:

Sub SplitAddress()
    Dim Addr As String
    Dim l As Integer
    Dim Desc As String
    Dim Zip As String
    Dim State As String
    Dim City As String
    
    Addr = Selection
    
    l = InStrRev(Addr, "(")
    Desc = Right(Addr, Len(Addr) - l + 1)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    Zip = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    State = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    City = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    Selection.Offset(0, 1) = Addr
    Selection.Offset(0, 2) = City
    Selection.Offset(0, 3) = State
    Selection.Range("B11").Offset(0, 4) = Zip
    Selection.Range("B11").Offset(0, 5) = Desc
End Sub

Explicación: Presione Alt+F11y pegue el código anterior en la ventana que aparece. Luego selecciona una celda que contenga la dirección y regresa a la ventana donde pegaste el código y presiona F5.

Si eso funciona, podemos trabajar para hacerlo más específico para su situación específica.