Get all characters on far right sight up to a space

[jebenexcel]

Hi,

How would one go about getting the last set/word of characters on the right side of a string, delimited by the first space from the right without inserting any new cells?
I need to separate some addresses into street names and house numbers; the numbers are 1-999 but sometimes they are letters as well, so I can't just remove all non-number characters to do that.

 

[Special-K99]

There may be a shorter way

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))+1)*(LEN(A1))-LEN(A1))+1,LEN(A1)))

 

[jebenexcel]

I used this, found it somewhere with google. Inserts 100 spaces between words, takes the last 100 hundred characters, changes spaces to "".

=IF(A2="";"";TRIM(RIGHT(SUBSTITUTE(K2;" ";REPT(" ";100));100)))

Thanks for the help anyway!!