Code:

Question

Ich habe eine Adressspalte in diesem Format:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

Und ich muss es aufteilen in: Straße (120 Lemon Street), Stadt (Columbus), Staat (OH), Postleitzahl (92738)und Beschreibung((Basketball Courts))

Gibt es eine Möglichkeit, das zu tun? Sie befinden sich alle im selben Bundesstaat, das ist also kein Problem. Sie liegen in unterschiedlichen Städten/Orten und haben unterschiedliche Postleitzahlen.

Wenn Sie nur einen Teil davon kennen, lassen Sie es mich trotzdem wissen. Ich bin für jede Hilfe dankbar, danke!

Answer 1

User1282637, ich habe unten ein Beispiel erstellt, das ich Ihnen zeigen möchte. Ich habe es nur als Ausgangspunkt erstellt, damit Sie mit Ihrer eigenen vollständigen Antwort abschließen können.

Zuerst habe ich zwei Listen erstellt. Eine enthielt alle Abkürzungen der Bundesstaaten und die andere alle akzeptierten Straßennamensuffixe, die ich hier gefunden habe:

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

Ich habe die von mir aufgeführte Formel einfach verwendet, um die Liste so umzuwandeln, dass sie mit einem Großbuchstaben beginnt und der Rest klein geschrieben wird, so wie Sie es bei Ihnen geschrieben haben.

Anschließend einfach nachschauen was zutrifft.

Dieser Teil könnte auf viele Arten erledigt werden, aber ich habe mich als Beispiel für diese entschieden. Es wird einfach eine Nummer in der entsprechenden Zeile aufgelistet, die eine Übereinstimmung für das verwendete Suffix findet.

Ich trenne den Teil (Basketballplätze) durch die Verwendung der Klammer:

Ich zeige an, was übrig bleibt, da in Ihrem Fall „Courts“ von „Basketball Courts“ auch ein Straßensuffix ist:

Als Nächstes muss ich herausfinden, wie lang die Zeichenfolge ist, die das Straßensuffix darstellt. Daher verwende ich Folgendes:

und das...

und schließlich kann ich dadurch nur die Straße anzeigen:

Ich habe die Stadt und den Staat noch nicht gemacht, aber wenn Sie dieser Idee folgen, können Sie das erreichen. Wenn Sie eine sauberere Option wünschen, sollten Sie sich auf jeden Fall mit VBA befassen. Ich hoffe, dies vermittelt Ihnen zumindest einige Ideen, wie Sie dies angehen können.

Answer 2

User1282637 fragt, ob es eine Möglichkeit gibt, diese Aufgabe zu erledigen, und bittet um Hilfe dabei. Das Problem liegt nicht so sehr in der Excel-Mechanik, sondern vielmehr darin, wie man mit der Mehrdeutigkeit der Daten umgeht. Das Parsen der Postleitzahl und der Beschreibung ist unkompliziert (es sei denn, Sie haben eine Mischung aus 5-stelligen und 9-stelligen Postleitzahlen). Das schwierige Problem besteht darin, die Straße von der Stadt zu trennen, daher werde ich mich darauf konzentrieren. Dies ist keine Schritt-für-Schritt-Anleitung mit Excel-Formeln. Es soll nur einige Einblicke in das Problem geben und einen Ansatz beschreiben, um für den schwierigen Teil zu einem Ergebnis zu gelangen.

Das Problem ist, dass es keine Trennzeichen zwischen den verschiedenen Feldern gibt. Das ist kein Problem beim Herauslösen der Beschreibung oder der Postleitzahl, da diese leicht zu erkennen sind. Das Problem besteht darin, zu bestimmen, wo die Straße endet und die Stadt beginnt. Betrachten Sie diese Variationen im Straßenabschnitt (bei weitem keine vollständige Liste):

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

Die Anzahl der „Wörter“ in der Straße kann von 1 oder 2 bis zu 7 oder 8 variieren, was für die Analyse nicht hilfreich ist. Der Straßentyp ist auch nicht besonders hilfreich. Es gibt etwa 50-100 Wörter, die nur für den Straßentyp verwendet werden (Straße, Allee, Boulevard, Weg, Mautstraße, Gasse, Hof, Kreisverkehr, Terrasse usw.). Kombiniert man das mit der Verwendung von Abkürzungen für den Straßentyp, sowohl korrekte als auch falsche, kommt man auf Hunderte. Außerdem ist diese Bezeichnung nicht immer das letzte Wort im Straßenfeld. Die Straße ist der am schwierigsten zu identifizierende Teil, daher besteht der logische Ansatz darin, den Rest zu identifizieren und dann ist der Rest die Straße.

Die Stadt kann aus mehreren Wörtern bestehen. Washington Court House, OH besteht aus drei Wörtern. Dann betrachten Sie Situationen wie St. Marys, OH. Ist das „St“ ein Teil des Städtenamens oder eine Straßentypbezeichnung? In welches Feld kommt es? Oder South Euclid, OH – ist „South“ ein Teil des Städtenamens oder eine Richtungsangabe, die Teil der Straßenadresse ist? Die Stadt hat ihre Probleme, aber es gibt eine Möglichkeit, mit ihnen umzugehen.

Auch die Verwendung der Postleitzahl zur Identifizierung der Stadt ist problematisch. Es besteht nicht immer eine 1:1-Übereinstimmung zwischen Städtename und Postleitzahl.

Die praktischste Lösung für dieses Problem ist die Verwendung von „Wörterbüchern“: einer Liste von Städten und einem Postleitzahlenverzeichnis. Diese stellen den eindeutigsten Teil der Adresse dar. Diese sind online oder bei der Post erhältlich. Um Vergleiche anstellen zu können, müssen Sie möglicherweise entweder Ihre Daten oder die Einträge bereinigen. Sie benötigen die gleiche Groß- und Kleinschreibung und zusätzliche Leerzeichen in Ihren Daten verhindern eine genaue Übereinstimmung.

Wenn Ihre Daten oder die Auflistung Abkürzungen enthalten, müssen Sie sich damit befassen. Übersetzen Sie die ungekürzte Abkürzung entweder in die Standardabkürzungen oder führen Sie einen zweiten Abgleich mit einem Abkürzungswörterbuch durch (auch online oder bei der Post erhältlich), wenn diese Unterschiede gefunden werden.

Die Postleitzahl kann problemlos analysiert werden, und das ist ein guter Ausgangspunkt. Führen Sie eine Postleitzahlensuche im Postleitzahlenverzeichnis durch. Wenn das Ergebnis genau mit einer Zeichenfolge übereinstimmt, die unmittelbar vor der Postleitzahl steht, ist klar, welcher Teil des Datensatzes das Stadtfeld ist.

Wenn es keine exakte oder eindeutige Übereinstimmung gibt, vergleichen Sie die Städtenamen. Gehen Sie die Liste der Städtenamen durch. Bestimmen Sie für jeden Namen die Anzahl der Wörter, die er enthält, und vergleichen Sie sie mit der Anzahl der Wörter, die der Postleitzahl unmittelbar vorangehen.

Wenn Sie mit einem der beiden Verfahren eine Übereinstimmung erzielen, ist alles, was links von der Stadt übrig bleibt, die Straßenadresse.

Diese Art der Anwendung lässt sich mit einer Datenbankanwendung viel einfacher durchführen als mit einer Tabellenkalkulation. Wie dem auch sei, Sie werden sehen, dass der Versuch, dies automatisiert durchzuführen, keine einfache Aufgabe ist. Mit ein paar Tabellenkalkulationsformeln ist dies nicht möglich.

Egal, wie anspruchsvoll Ihre Programmierung ist, Sie werden wahrscheinlich immer noch Datensätze haben, die Sie manuell analysieren müssen, und Analysefehler, die Sie manuell korrigieren müssen. Sie geben nicht an, wie viele Datensätze Sie haben. Es ist möglicherweise weniger Arbeit, es einfach manuell zu tun.

Wenn die Zahl groß ist und ich es tun müsste, würde ich die Liste aussortieren. Ordnen Sie die Datensätze zu, die einfach sind, wie eindeutige Postleitzahlenübereinstimmungen. Dann lassen Sie die Datenmenge bestimmen, wie weit Sie bei der Programmierung automatischer Übereinstimmungen gehen.

Für die verbleibenden Datensätze gibt es eine Möglichkeit, den manuellen Vorgang zu beschleunigen, vorausgesetzt, Sie haben bereits die Postleitzahl und die Beschreibung abgetrennt. Sehen Sie sich den Datensatz an und ermitteln Sie visuell die Anzahl der „Wörter“ in der Stadt, was eine schnelle mentale Aufgabe ist. Geben Sie diese in eine vorgegebene Zelle ein und verwenden Sie eine Formel, um die Straße von der Stadt basierend auf der Anzahl der Leerzeichen zu trennen (trennen Sie am N-ten Leerzeichen, wobei N = Gesamtzahl der Leerzeichen + 1 – Anzahl der Wörter im Städtenamen).

Answer 3

Probieren Sie das aus. Dieser einfache Ansatz sollte wirklich gut funktionieren, wenn Sie damit leben können, dass Adresse und Stadt in derselben Zelle stehen. Ich habe eine Formel, um die Stadt zu extrahieren, wenn es nur ein Wort ist, aber es wird viel komplexer, wenn Städte aus mehreren Wörtern bestehen (z. B. New York).

Formeln... Adresse & Stadt: =LEFT(A2,FIND("OH",A2)-1) Staat: =MID(A2,FIND("OH",A2),2) – Sie haben erwähnt, dass alles OH ist, also habe ich es einfach gehalten PLZ: =MID(A2,FIND("OH",A2)+3,5) Beschreibung: =TRIM(MID(A2,FIND("OH",A2)+8,30))

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

Answer 4

Code:

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

Erklärung: Drücken Sie Alt+F11und fügen Sie den obigen Code in das angezeigte Fenster ein. Wählen Sie dann eine Zelle aus, die die Adresse enthält, kehren Sie zum Fenster zurück, in das Sie den Code eingefügt haben, und drücken Sie F5.

Wenn das funktioniert, können wir daran arbeiten, es spezifischer auf Ihre spezielle Situation abzustimmen.