Text to column

[lezawang]

250 Main Street

1000 Grand Street

<tbody>
</tbody>

Hello
I am trying to use Text-to-Column command to separate street# from street name. I used Space as delimiter. But excel breaks the text into 3 columns, one for number and second for street name and third for the word "street". How can I use Text-To-Columns to separate the above address into 2 columns only like the following

A	B
250	Main street

<tbody>
</tbody>

Thanks a lot.

 

[Gary's Student]

Use formulas rather than text to columns. With raw data in A1, in B1 enter:

=LEFT(A1,FIND(" ",A1,1)-1)

and in C1 enter:

=MID(A1,FIND(" ",A1,1)+1,9999)

 

[AlKey]

Enter formula in B1, copy across and down

=TRIM(MID(SUBSTITUTE(SUBSTITUTE("|"&$A1," ","|",1),"|",REPT(" ",99)),99*COLUMNS($A:A),99))

Book1
	A	B	C
1	250 Main Street	250	Main Street
2	1000 Grand Street	1000	Grand Street
Sheet1