Allan hat eine Liste mit mehreren hundert Namen und Adressen. Die Straßenadressen reichen von der Main Street, der 123 Main Street, der US RT 2 oder der 187 South Elm St. Er möchte die Straßennummer aus den Adressen herausbrechen.

Die Adresse 123 Main Street würde also in einer Zelle mit „123“ und in einer anderen mit „Main Street“ enden. Wenn es keine Hausnummer gibt, landet nichts in der Spalte mit der Hausnummer. Das Werkzeug „Text in Spalten“ funktioniert nicht und er fragt sich, wie er das tun kann.

In einer perfekten Welt können Sie mit Excel die Zahlen leicht von den Straßennamen trennen. Da diese Option nicht vorhanden ist, haben Sie mehrere Möglichkeiten. Die zeitaufwändigste Option besteht darin, eine zusätzliche Spalte hinzuzufügen und die Daten erneut einzugeben. Wenn Sie jedoch etwas Zeit sparen möchten, können Sie verschiedene Formeln verwenden, um die Aufgabe auszuführen.

Angenommen, die Liste der Adressen befindet sich in Spalte A (beginnend in Zelle A1), könnten Sie eine Formel ähnlich der folgenden verwenden, um den numerischen Teil der Adresse herauszuziehen:

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

Angenommen, Sie geben die Formel in Zelle B1 ein, können Sie eine andere Formel verwenden, um den nicht numerischen Teil der Adresse abzuleiten:

=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

Beachten Sie, dass dieser Ansatz eine Einschränkung aufweist. Einige Adressen, insbesondere in großen Ballungsräumen, verwenden ein Format wie 152-33 Bell Blvd. Die obigen Formeln funktionieren für diese Adressen, aber wenn die Alternative 152 33 Bell Blvd. verwendet wird, wird die Formel falsch analysiert. Sofern Sie kein professionell entwickeltes Adressanalyseprogramm kaufen möchten, sollten die obigen Formeln und ein schneller Augapfel-Scan der Ergebnisse ausreichend sein.

In diesem Fall funktioniert eine andere Formel. Angenommen, Ihre Adresse befindet sich in Zelle A2, geben Sie die folgende Formel in Zelle B2 ein:

=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")

Diese Formel lautet: „Wenn das erste Zeichen keine Zahl ist, lassen Sie die Zelle leer. Andernfalls geben Sie mir alle Zeichen auf der linken Seite, ohne das erste Leerzeichen einzuschließen.“ Sie können dann das Ergebnis dieser Formel verwenden, um den nicht numerischen Teil der Adresse herauszuziehen:

=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))

Ein anderer Ansatz ist die Verwendung einer Array-Formel. Auch hier können Sie unter der Annahme, dass sich Ihre Adresse in Zelle A2 befindet, Folgendes verwenden:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE,LEFT(A2,FIND(" ",A2,1)),"")

Da dies eine Array-Formel ist, müssen Sie sie mit Strg + Umschalt + Eingabetaste eingeben. Das Ergebnis ist, dass die Formel den führenden numerischen Teil der Adresse zurückgibt. Sie können dann den nicht numerischen Teil mithilfe der folgenden Array-Formel bestimmen:

=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)

Schließlich kann das folgende Makro verwendet werden, um die Straßenadresse aus dem Straßennamen zu trennen.

Sub GetStreetNum()

Dim sStreet As String     Dim J As Integer     Dim iNum As Integer

For Each cell In Selection         sStreet = cell.Value         J = InStr(sStreet, " ")

If J > 0 Then             iNum = Val(Left(sStreet, J))

If iNum > 0 Then                 cell.Offset(0, 1).Value = iNum                 sStreet = Trim(Mid(sStreet, J, Len(sStreet)))

End If         End If         cell.Offset(0, 2).Value = sStreet     Next End Sub

Um dieses Makro zu verwenden, wählen Sie einfach den Zellenbereich aus, der Ihre Adressen enthält, und führen Sie ihn dann aus. Der führende numerische Teil der Adresse wird in der Zelle rechts von jeder Adresse angezeigt, und der Rest der Adresse wird in der Zelle rechts davon platziert. (Stellen Sie daher sicher, dass sich rechts von den ausgewählten Adressen zwei leere Spalten befinden.)

_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 (8031) 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:

Straßennummern aus einer Adresse extrahieren.