Können Sie eine Adresse in eine Tabelle in einer Tabelle konvertieren?

37969
Isaac Moses

In Anbetracht einer Spalte mit Straßenadressen mit Stadt und Bundesstaat, jedoch ohne ZIP in einer Tabelle, möchte ich eine Formel in eine zweite Spalte einfügen, die die Postleitzahl enthält. Kennen Sie einen Weg, dies zu tun?

Ich habe es mit US-Adressen zu tun, aber auch Antworten auf andere Länder sind interessant.

UPDATE: Ich denke, ich hoffe meistens, dass dies in Google Spreadsheets möglich ist. Mir ist klar, dass Sie dazu auf eine umfangreiche Postleitzahlendatenbank zugreifen müssen, aber es scheint mir, dass sich eine solche Datenbank bereits in Google Maps befindet. Wenn ich dort eine Adresse ohne Postleitzahl eingebe, bekomme ich eine Adresse mit Postleitzahl zurück. Wenn Maps diese Suche durchführen kann, gibt es möglicherweise eine Möglichkeit, dies auch in Tabellenkalkulationen zu ermöglichen.

11
In Verbindung mit der Antwort von @ ChrisF, in welcher Region befinden Sie sich? JMD vor 14 Jahren 0
Ich wusste nicht, dass der Begriff "ZIP" außerhalb der USA galt. Jedenfalls habe ich jetzt angegeben. Isaac Moses vor 14 Jahren 0
Es ist wenig bekannt, dass Postleitzahlen nur benötigt werden, um die Postzustellung zu beschleunigen. Diese Verwendung ist heute weniger anwendbar als zu ihrer ersten Einführung (1968), da der USPS nun getippte Adressen durchsucht und die ZIP-Datei für Sie nachschlägt, wenn sie fehlt. Wenn Sie keinen bereitstellen, verwendet die USPS ihr System, um es bereitzustellen (und das Beste von allem ist, dass dies keine zusätzlichen Kosten verursacht). Auch sind Postleitzahlen US-spezifisch. Die meisten Orte haben sie, aber überall werden sie als Postleitzahlen bezeichnet. krowe vor 9 Jahren 0

9 Antworten auf die Frage

8
Doug Harris

Google-Tabellen haben eine Reihe von Funktionen für externe Daten . Wenn Sie eine Site finden (oder erstellen) können, die die Suche durch Übergeben von Parametern durchführt, könnten Sie eine der folgenden Formel ähnliche Formel eingeben :

Cell A1 (Address): 123 Main St Cell B1 (City): Springfield Cell C1 (State): MO  Cell D1 (combined address): =concatenate(A1,B1,C1)  Cell E1 (imported zip code):  =importData(concatenate("http://zipfinder.com/search?addr=",D1))

Dies ist eine hypothetische Idee. Die verketteten, importData- und anderen Funktionen für externe Daten sind in Google Spreadsheets vorhanden. "zipfinder.com" existiert nicht. Es gibt viele Websites, die Ihnen helfen, eine Postleitzahl von einer Adresse aus zu finden. Der schwierige Teil ist das Finden einer Adresse, die Adressdaten in der URL akzeptiert und etwas liefert, das für Google Spreadsheets einfach genug ist.

4
ChrisF

In eine Kalkulationstabelle ist nichts eingebaut, da verifizierte Adressendatenbanken sehr teuer sind.

Möglicherweise finden Sie einen Onlinedienst, der dies für Sie erledigt. In Großbritannien können Sie beispielsweise auf der Royal Mail- Website nach Postleitzahlen suchen (das entspricht den Postleitzahlen in Großbritannien), aber Sie können maximal 15 Suchvorgänge pro Tag durchführen. Dies ist für den persönlichen Gebrauch. Unternehmen werden mehr bekommen - aber Sie müssen sich dafür anmelden und dafür bezahlen.

Ähnliche Dienste gibt es in anderen Ländern.

3
BBlake

Sie benötigen eine Art externes Adressensuchwerkzeug oder -dienst. Bei nur Stadt / Bundesstaat gibt es oft keine Möglichkeit, die korrekte Postleitzahl zu bestimmen, da viele Städte über mehrere Postleitzahlen verfügen und viele Städte über Straßen mit mehreren Postleitzahlen verfügen, sodass Sie nicht nur die Straße hinzufügen und eine Stadt / Bundesstaat verwenden können / zip-Formel oder Nachschlagen. Wenn Sie nach Postleitzahlen für Adressen suchen müssen, müssen Sie einen Dienst oder ein Werkzeug aufgeben und bezahlen. Da gibt es keinen guten Weg.

Ja, ich mache das zum Leben - das Konvertieren von Daten von einem Format in ein anderes. Wie bereits erwähnt, können einige Städte mehrere Postleitzahlen haben. In den Gebäuden des World Trade Centers gab es eigene Postleitzahlen, sodass Stadt / Bundesland nicht ausreichen. Das USPS verfügt über eine riesige Datenbank, die dies ermöglicht, aber es erfordert auch die Straße und ist relativ teuer. Blackbeagle vor 14 Jahren 0
Könnten Sie die US-Volkszählungsdaten aus dem Jahr 2000 nicht verwenden? Ich glaube, dass es kostenlos ist. Chris vor 14 Jahren 0
Ich kenne das zwar nicht, aber Sie sprechen auch von einer riesigen Datenmenge, und dies ist nicht etwas, das Sie in einer Excel-Tabelle ohne weiteres tun können. BBlake vor 14 Jahren 1
3
Neil McGuigan

Es ist schwer in Excel zu machen. Sie müssen einen Webdienst mithilfe des SOAP-Toolkits aufrufen oder Visual Studio verwenden. Man muss sich fragen, was Microsoft denkt.

In einer Google Text & Tabellen-Kalkulationstabelle ist es viel einfacher, dann nach Excel zu exportieren:

Sie können den XML-Webdienst von GeoCoder.ca verwenden, um die Postleitzahl nach Adresse aufzurufen. In Google Docs würden Sie diese Funktion verwenden:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//postal")

(wobei A2 die Straße ist.)

Sie können den Breiten- und Längengrad auch auf diese Weise erhalten:

=importXML("http://geocoder.ca/?geoit=xml&showpostal=1&locate=" & A2,"//geodata")

Beachten Sie, dass GeoCoder die Anforderungen pro Tag für den kostenlosen Service einschränkt.

3
Sushil Jhamnani

Ja - Sie können die Postleitzahlen in Ihrem Google Docs-Datenblatt jetzt unter der folgenden URL finden:

Schritt 1: Geben Sie auf dieser Zielseite die Adresse ein, nach der Sie suchen möchten.

Schritt 2: Ich habe nach der Adresse "1245 5TH ST" in Washington, DC gesucht und die URL sah folgendermaßen aus:

https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=1245+5TH+ST&address2=&city=Washington&state=DC&urbanCode=&postalCode=&zip=

Schritt 3: Kopieren Sie die folgende Formel und fügen Sie sie ein, und ersetzen Sie CELL, CITY, STATE:

=Mid(importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15),Find("DC",importHTML(CONCATENATE("https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=",CELL,"&address2=&city=CITY&state=STATE&urbanCode=&postalCode=&zip="),"list",15))+2,11)

Denken Sie daran, dass die URL in Schritt 3 mit der URL aus Schritt 2 übereinstimmen sollte. Die Zelle wird basierend auf Ihrem Zielzellenwert dynamisch sein.

Die Funktion in Schritt 3 extrahiert 11 Zeichen nach dem STATE-Wert. Daher müssen Sie die MID-Funktion anpassen, um die korrekten Werte zu erhalten. Das hat perfekt für mich funktioniert.

Eine letzte Sache: Google erlaubt nur 50 Importfunktionen pro Blatt. Mit der obigen Methode können Sie also nur 25 Postleitzahlen in einem Durchgang finden.

Das hat heute morgen für mich nicht funktioniert. mooreds vor 8 Jahren 0
3
Max Ghenis

Sie können mit dem Google Maps-Dienst eine benutzerdefinierte Apps-Skript-Funktion schreiben, indem Sie einen ähnlichen Ansatz für diese Antwort verwenden . Öffnen Sie dazu "Tools ... Skripteditor" und fügen Sie diese Funktion ein:

function getZIP(address) { var geo = Maps.newGeocoder().geocode(address); var resultComponents = geo.results[0].address_components; for (var i = 0; i < resultComponents.length; i++) { if (resultComponents[i].types.indexOf('postal_code') > -1) { return resultComponents[i].long_name; } } }

Sie können dies dann in einer Tabellenzelle aufrufen, z. B. gibt es 20500 für das Weiße Haus korrekt zurück:

=getZIP("1600 Pennsylvania Ave. NW, Washington, DC")

Im Gegensatz zu anderen Ansätzen funktioniert es auch für Standorte außerhalb der USA, z. B. gibt es 75007 für den Eiffelturm korrekt zurück:

=getZIP("Champ de Mars, 5 Avenue Anatole France, Paris, France")
Beschränkt die Google-API, wie oft Sie den Geocoder verwenden können? Wenn ja, lohnt es sich hier zu erwähnen. Excellll vor 8 Jahren 0
Die Kontingentseite listet Maps nicht speziell auf. Daher würde ich am besten auf 20.000 / Tag (URL-Abruflimit) setzen: https://developers.google.com/apps-script/guides/services/quotas?hl=de#current_quotas Max Ghenis vor 8 Jahren 0
1
JMD

In Kanada ermöglicht die Website von Canada Post die Suche nach Postleitzahlen nach Adresse (und mir ist kein Tageslimit bekannt), was Ihnen jedoch bei der Integration in Ihre Tabelle leider nicht hilft. Ich bin daran interessiert, auch dafür mögliche Lösungen zu sehen. Ich habe immer die manuelle Postleitzahlensuche für die verwendet, die ich nicht kenne.

0
Lunatik

In Großbritannien benötigen Sie Zugriff auf die Postleitzahl-Adressdatei (PAF), in der alle Adressen des Landes aufgeführt sind. Ein direkter Zugang zum PAF kostet Geld und nicht nur einen unbedeutenden Betrag. Wir zahlen ungefähr 200 £ pro Sitzplatz pro Jahr für uneingeschränkten Zugriff auf aktualisierte PAF-Daten, aber dann werfen wir jeden Monat mehr als 10.000 Anfragen auf.

Die meisten anderen Territorien sind in dieser Hinsicht ähnlich, so dass ich mir vorstellen könnte, dass Sie keinen legitimen Weg finden werden, um auf Zip-Daten so zuzugreifen, wie Sie möchten.

Selbst wenn Sie eine Website finden könnten, auf der RESTful das Abfragen ihrer Daten zum direkten Einfügen einer ZIP-Datei in eine Kalkulationstabelle ermöglicht, würden sie zweifellos strikte Grenzen für die Anzahl der Abfragen festlegen, die in einem bestimmten Zeitraum ausgeführt werden könnten.

0
Jonathan Oliver

Es gibt einige Onlinedienste, die eine Excel-Tabelle (oder eine durch CSV / Tabulatorzeichen getrennte Datei) verarbeiten und fehlende ZIP-Codes anfügen. Der Preis kann je nach Diensteanbieter zwischen ein paar Dollar und 100 US-Dollar liegen, von denen einige Einrichtungsgebühren, Bearbeitungsgebühren und Eilauftragsgebühren haben.

Einige Diensteanbieter können die bearbeitete Datei innerhalb weniger Minuten an Sie zurücksenden, während andere, die behaupten, dass "Eilverarbeitung" mehr als drei Werktage in Anspruch nehmen kann. Aufgrund Ihrer Frage möchten Sie nicht in den Kauf von Software investieren und Tausende von Dollar für eine On-Site-Softwarelösung sparen. Wenn dies der Fall ist, ist ein Online-Anbieter die beste Wahl.

Im Interesse der vollständigen Offenlegung bin ich der Gründer von SmartyStreets. Wir bieten eine vollautomatische Online- Adressprüfung . Sie können Ihre Excel-Datei per Drag-and-Drop auf unsere Website ziehen. Wir werden sie innerhalb von ein bis zwei Minuten bearbeiten und zurückgeben, je nach Größe.

Verwandte Probleme