Dan hat eine Spalte mit Zellen und jede Zelle enthält drei Elemente: Stadt, Bundesland und Postleitzahl. (Alle drei befinden sich in einer einzelnen Zelle, ähnlich wie in einer Adresszeile.) Einige der Postleitzahlen sind fünfstellig und andere neunstellig. Dan muss sowohl den zweistelligen Status als auch die fünfstellige Postleitzahl in ihre eigenen Zellen rechts neben den aktuellen Daten ziehen. Dan weiß, dass er das Werkzeug „Text in Spalten“ verwenden kann, ist jedoch der Ansicht, dass dies viel Arbeit erfordert, da er sich mit Städtenamen und Kommas mit mehreren Wörtern befassen müsste. Dan kann nicht anders, als zu glauben, dass es einen formelhaften Ansatz geben könnte, der einfacher wäre.

Es müssen einige Annahmen zu den Daten getroffen werden, um Empfehlungen aussprechen zu können. Nehmen wir zum Beispiel an, dass alle Daten in diesem Format vorliegen:

My Town, CA 98765-4321

Der Teil ab dem Bindestrich (der nachfolgende Teil der Postleitzahl) ist optional, aber die Position des Kommas ist statisch – es folgt immer dem Namen der Stadt – und der Staat besteht immer aus zwei Zeichen. In diesem Fall ist es einfach, zwei Formeln zu erstellen, die die staatliche Abkürzung und die ersten fünf Ziffern der Postleitzahl extrahieren:

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

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

Beide Formeln geben das Komma ein; Es dient als Begrenzer zwischen der Stadt und den beiden wirklich gewünschten Gegenständen. Wenn die Daten kein Komma enthalten oder wenn mehrere Kommas vorhanden sind, geben die Formeln nicht die gewünschten Informationen zurück.

In den Formeln wird außerdem davon ausgegangen, dass Ihre Daten keine zusätzlichen Leerzeichen enthalten. Nach dem Komma und zwischen Bundesland und Postleitzahl steht höchstens ein Leerzeichen. Dies ist natürlich einfach zu erzwingen. Verwenden Sie einfach Suchen und Ersetzen, um zwei Leerzeichen an einer beliebigen Stelle in Ihrem Arbeitsblatt durch ein einzelnes Leerzeichen zu ersetzen.

Wenn Ihre Daten so strukturiert sind, können Sie sich bei Ihrer Arbeit natürlich immer noch auf das Tool Text to Columns verlassen. Sie müssen lediglich das Tool ausführen und Ihre Daten anhand des Kommas aufteilen. Dadurch wird die Stadt in einer Zelle verlassen und der Staat und die Postleitzahl in der nächsten Zelle zusammengefasst. Anschließend können Sie Text in Spalten erneut verwenden, diesmal in der zweiten Zelle (nicht im Namen der Stadt) und den Inhalt anhand des Leerzeichens aufteilen.

Wenn Ihre Daten nicht so strukturiert sind – möglicherweise haben sie mehrere Kommas in der Adresse oder zusätzliche Leerzeichen -, ist ein völlig anderer Ansatz erforderlich. Um dies zu bewältigen, besteht die grundlegende Technik darin, die Daten zu kürzen, um überflüssige Leerzeichen (führende, nachfolgende und interne) zu entfernen, und dann die Position des letzten Leerzeichens und des vorletzten Leerzeichens zu bestimmen.

Mit dieser Formel können Sie die fünf Ziffern in der Postleitzahl herausziehen, die als unmittelbar nach dem letzten Leerzeichen in den Daten definiert ist:

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

Die zweistellige Statusabkürzung kann zurückgegeben werden, indem die beiden Zeichen unmittelbar nach dem vorletzten Leerzeichen herausgezogen werden:

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

Wenn Ihre Daten noch weniger strukturiert sind – möglicherweise enthält sie Adressen, die nicht alle zweistellige Statusabkürzungen haben (N.J. anstelle von NJ) -, sollten Sie am besten ein Makro verwenden, um die Daten aufzuteilen.

Der Grund dafür ist, dass VBA über eine viel umfangreichere Auswahl an Textverarbeitungsfunktionen verfügt als mit Excel-Formeln. Das folgende Makro erstellt eine benutzerdefinierte Funktion, die entweder den Status oder die Postleitzahl zurückgeben kann:

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

Um diese Funktion zu verwenden, geben Sie einfach eine Zellreferenz und entweder 1 (wenn Sie den Status möchten) oder 2 (wenn Sie die Postleitzahl möchten) an. Hier ist ein Beispiel für die Anforderung der Postleitzahl für eine beliebige Adresse in Zelle A1:

=GetStateZIP(A1,2)

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9599) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Extrahieren eines Staates und einer Postleitzahl.