Hallo Ray,
es ist nicht das letzte Leerzeichen, nach dem beim Zerlegen von Straßennamen gesucht wird, mitunter fehlt das, bei "42 b" ist das auch wenig hilfreich. Die erste Zahl zu finden, ist viel praktischer:
=WENNFEHLER(RECHTS($A2;LÄNGE($A2)-(AGGREGAT(15;6;FINDEN(ZEILE($1:$9);$A2);1)-1));"")
Dabei ist (von innen nach außen) ZEILE($1:$9) nichts weiter als eine Liste von Zahlen: {1;2;3;4;5;6;7;8;9}
Werden FINDEN neun Suchwerte übergeben, die in A2 gesucht werden sollen, liefert die Funktion neun Ergebnisse: {13;#WERT!;#WERT!;#WERT!;15;#WERT!;#WERT!;#WERT!;#WERT!}
An Position 13 im durchsuchten Text wurde also die 1 gefunden, an Position 15 die 5, in A2 steht "Hauptstraße 105a".
AGGREGAT(15;...;...;1) ist KKLeinste, die 6 entfernt die Fehlerwerte (#WERT!), übrig bleibt also die 11. Davon wird eine 1 abgezogen, die Position ist dann das Zeichen vor der Zahl. Die resultierende 10 wird anschließend von der Länge des durchsuchten Textes subtrahiert. Das ist die Anzahl der Zeichen von RECHTS, die vom durchsuchten String zugeliefert wird.
Drumrum noch ein WENNFEHLER, denn ohne jede Nummer im Straßennamen wirft die Formel einen #ZAHL!-Fehler.
Offensichtlich funktioniert das nicht mit Straßennamen, in denen auch ohne Hausnummer Zahlen vorkommen. Zum Glück sind die selten und mit Berlin oder Mannheim hatte ich noch wenig zu tun.
Doch wenn es darauf ankommen sollte, findest Du die dafür passende Formel hier, ganz unten:
https://www.herber.de/excelformeln/pages/Straenname_und_HausNr_trennen.html
Erklären muss Dir die jemand anders.
Komplizierte Formeln wie von Yal beschrieben in diversen Hilfsspalten oder -zeilen in ihre Einzelteile zu zerlegen ist sehr, sehr hilfreich! Nur mit der eben empfohlenen half das leider auch nicht :·(
Viele Grüße
Michael