Split text using multiple spaces as identifier

[pinco]

I'm trying to split a list of addresses where the only unique identifer is the six spaces between the street address and city which is listed in one cell.
For example, the following text is in cell A3:

1234 Main St Chicago, IL 12345

=MID(A3,1,FIND(" ",A3,1)-1)

When I use the above formula it returns the #VALUE!

If I retype the address and use this formula it will return 1234 Main St which is what I'm looking for. I've used the format painter to reformat the text which was copied but I'm still receiving the #VALUE! problem - Please Help!

 

[Jonmo1]

Welcome to the board.

sounds like your spaces are not really spaces, some other character like a hard return.

What does this return

=CODE(MID(A3,13,1))

Where 13 is the position number of the first one of the 6 "spaces"

 

[pinco]

Thank you

The =CODE formula returns the number 32

 

[pinco]

After checking all six positions this is what I'm finding

32,160,32,160,160,160

 

[Jonmo1]

Try

=TRIM(LEFT(A3,FIND(CHAR(CODE(160)),A3)-1))

 

[pinco]

The =TRIM formula returned a blank cell

 

[Jonmo1]

my bad..

Try
=TRIM(LEFT(A3,FIND(CHAR(160),A3)-1))

 

[pinco]

Worked perfectly - thank you!!!

What would be the formula to split the city,state & zip into a cell?