how to make a function to extract from to

[Fableman]

Sesam street 4B
Motherstreet 56
Jungle terror street 5
Monky do funky street 6D

How can I make a function that extract the street number ex. 4B, 56, 5 and
6D ?

and

How to make a funtion that only extract the street named and not the numbers ?

(English not my native language , hope you understand what I try to ask for)


//Thanks..

 

[Jarek Kujawa]

would this:

=MID(A1,FIND("street",A1)+7,LEN(A1)-FIND("street",A1)-7)
(for street number)

=LEFT(A1,FIND("street",A1)+5)
(for street name)

help?