Filtering but ignoring numbers

pls attach sample excel file

Use this formula in B1 and copy down

=REPLACE(A1,1,FIND(" ",A1),"")

Row\Col	A	B
1	1 Letsby ave	Letsby ave
2	2 Letsby ave	Letsby ave
3	3 Letsby ave	Letsby ave
4	4 Letsby ave	Letsby ave
5	5 Round Rd	Round Rd
6	5 Round Rd	Round Rd
7	5 Round Rd	Round Rd
8	1 Red Rd	Red Rd
9	1 Red Rd	Red Rd
10	1 Red Rd	Red Rd
11	1 Red Rd	Red Rd
12	2 Red Rd	Red Rd

Another way to separate the number from the street name:

Enter in B1 and fill down.

Formula:

Please Login or Register  to view this content.

B2=IFERROR(INDEX(MID($A$1:$A$22,FIND(" ",$A$1:$A$22)+1,256),MATCH(0,MMULT((MID($A$1:$A$22,FIND(" ",$A$1:$A$22)+1,256)=TRANSPOSE($C$1:$C1))*1,(ROW($C$1:$C1)>0)*1),0)),"")
TRY THIS AND COPPY TOWARDS DOWN
Note: Don't start this formula in Row 1