Tom arbeitet mit vielen Adresslisten. Eine Sache, die ihn verrückt macht, ist, wenn sich die Adresse in einer einzigen Zelle befindet. Tom braucht die Hausnummer in einer Zelle und die Straße in einer anderen Zelle. Wenn die Adresse also „1234 Maple Glen Ave.“ lautet, benötigt er „1234“ in einer Zelle und „Maple Glen Ave.“ in einem anderen. Er kann die Hausnummer in eine Spalte vor dem Straßennamen kopieren, aber es ist ein langer Prozess, die Nummern erneut einzugeben und sie dann aus dem Straßennamen zu entfernen. Tom fragt sich, ob es einen einfacheren Weg gibt, die Hausnummer vom Straßennamen zu „trennen“.

Es gibt verschiedene Möglichkeiten, wie Sie diese Aufgabe ausführen können. Einige denken möglicherweise, dass die Verwendung des Werkzeugs Text in Spalten (auf der Registerkarte Daten des Menübands)

würde die gewünschten Ergebnisse geben. Leider ist es nicht der beste Weg, die Hausnummer zu trennen. Der Grund ist einfach: Wenn Sie das Tool verwenden, indem Sie ein Trennzeichen angeben, ist das Leerzeichen das einzige, das Sinn macht. Aber wenn Sie das tun, erhalten Sie „1234 Maple Glen Ave.“ verteilt auf vier Spalten anstelle der gewünschten zwei. Sie müssten dann einen Weg finden, um den Straßennamen wieder zusammenzufügen.

Die andere Möglichkeit, das Werkzeug „Text in Spalten“ möglicherweise zu verwenden, besteht in einer festen Spaltenbreite (anstelle eines Trennzeichens). Dieser Ansatz funktioniert nur, wenn alle Ihre Adressen die gleiche Anzahl von Ziffern in der Hausnummer haben. In den meisten Datensätzen ist dies natürlich nicht der Fall – Hausnummern können aus einer beliebigen Anzahl von Ziffern bestehen und sogar nicht numerische Zeichen enthalten (z. B. „1234A“, „1234-B“ oder „1234A“) 1234-36).

Ein Ansatz, der funktioniert, besteht darin, Formeln zu verwenden, um die ursprünglichen Adressen auseinander zu ziehen. Ihre Formeln können das erste Leerzeichen in der Adresse eingeben und entweder den Teil links vom Leerzeichen oder den Teil rechts zurückgeben. Hier ist der Weg, um alles vor dem Leerzeichen herauszuholen, wenn eine Adresse in Zelle A1 angegeben wird:

=LEFT(A1,FIND(" ",A1)-1)

Wenn Sie absolut sicher sind, dass die Adressen keine nicht numerischen Zeichen enthalten, können Sie die Formel in die VALUE-Funktion einschließen, sodass Sie die Hausnummer als numerischen Wert erhalten:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

Um den Teil der Adresse nach dem ersten Leerzeichen abzurufen, können Sie die folgende Formel verwenden:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

Wenn Sie möchten, können Sie eine Formel erstellen, die auf die Hausnummer verweist, die Sie mit der ersten Formel herausgezogen haben. Angenommen, Ihre Hausnummernformel befindet sich in Spalte B; Sie können Folgendes in Spalte C einfügen:

=TRIM(SUBSTITUTE(A1,B1,))

Mit Ihren Formeln in den Spalten B und C (und den Adressen in Spalte A) können Sie die Formeln für beliebig viele Zeilen nach unten kopieren. Dann können Sie diesen B: C-Bereich auswählen und mit Paste Special die Werte wieder in diese Zellen einfügen. Danach können Sie die ursprünglichen Adressen in Spalte A sicher löschen.

Wenn Sie häufig viele Adressen eingeben müssen, ist es möglicherweise besser, ein Makro zum Auseinanderziehen zu verwenden. Das folgende Beispielmakro funktioniert für jeden Bereich, den Sie beim Ausführen ausgewählt haben. Es fügt links von der Auswahl eine Spalte mit leeren Zellen ein, füllt diese Zellen mit der Hausnummer und passt die Adressen so an, dass die Hausnummer nicht mehr enthalten ist.

Sub SplitAddress()

Dim c As Range     Dim j As Integer     Dim n As String     Dim addr As String

Selection.Insert Shift:=xlToRight     Selection.Offset(0, 1).Select

For Each c In Selection         j = InStr(1, c, " ")

n = Left(c, j)

c.Offset(0, -1) = n         addr = Trim(Right(c, Len(c) - j))

c = addr     Next End Sub

Wenn Sie möchten, können Sie eine benutzerdefinierte Funktion erstellen, die nur die Hausnummer zurückgibt:

Function GrabHouseNumber(Raw As String) As Text     Dim x As Variant     Dim House As Variant

x = Split(Raw, " ")     'use space char to split elements into array     House = x(0)   'first element of array     If Left(House, 1) Like "#" Then         'First char is numeric digit         GrabHouseNumber = House  'set return value as house number     Else         GrabHouseNumber = ""  'First char is text, so not a house number     End If End Function

Ein Vorteil der Verwendung der benutzerdefinierten Funktion besteht darin, dass überprüft wird, ob der erste Teil der ursprünglichen Adresse tatsächlich mit einer Nummer beginnt oder nicht. Wenn dies nicht der Fall ist, wird davon ausgegangen, dass die Adresse nicht mit einer Hausnummer beginnt. (Die gesamte Hausnummer muss nicht numerisch sein; sie muss nur mit einer Ziffer beginnen.)

_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 (13350) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.