Remove everything after 4th space including the space

[realestateexcel]

Hi

First time posting. I am a huge fan of Excel I use it everyday at work and it is amazing what it can do. I have a list of addresses and I need to remove the unit numbers at the end from. Please see below. I am thinking is there a way to remove everything after the 4th space. Thank You in advance for the help.

1007 W Armitage Ave 107
1008 W Altgeld St
1018 W Armitage Ave 607

2719 W Belmont St 1 Change to

<colgroup><col></colgroup><tbody>
</tbody>

1007 W Armitage Ave
1008 W Altgeld St
1018 W Armitage Ave

2719 W Belmont St

<colgroup><col></colgroup><tbody>
</tbody>

 

[63falcondude]

What happens if there is not a 4th space? This formula removes everything after (and including( the 4th space and will return the value if there is not a 4th space:

=IFERROR(LEFT(SUBSTITUTE(A1," ","|",4),FIND("|",SUBSTITUTE(A1," ","|",4))-1),A1)

 

[Comfy]

Here you go.

Excel 2010
	A	B
1	1007 W Armitage Ave 107	1007 W Armitage Ave
2	1008 W Altgeld St	1008 W Altgeld St
3	1018 W Armitage Ave 607	1018 W Armitage Ave
4	2719 W Belmont St 1	2719 W Belmont St
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",999), 4),255))

 

[Nishant94]

Unknown
	A	B
1	1007 W Armitage Ave 107	1007 W Armitage Ave
2	1008 W Altgeld St	1008 W Altgeld St
3	1018 W Armitage Ave 607	1018 W Armitage Ave
4	2719 W Belmont St 1	279 W Belmont St
Sheet3

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(RIGHT(A1,1)+0),SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50)),""),A1)

 

[FormR]

And another option

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",4)&"|")-1)

Welcome to the board btw!