Text to columns, split at first space only

[Wowbagger]

I have a 2500 line column of street addresses along the lines of (123 E
Grand Maple). How can I split this column into two columns, first column
only the numeric portion, second column only the street portion even though
the street portion may contain spaces?

For simplicity's sake I can assume that the first space in the address
represents where the split should occur and that any subsequent spaces
should be ignored.

 

[Trevor Shuttleworth]

Assuming the addresses start in cell A2

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

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

And drag down ...

Regards

Trevor

 

[Aussie_Striker]

I have a similar problem however I have data that has 2 spaces and
need the numeral at the end. With the formula above I get the word an
numeral.

ie
cell = word word: number

need result
number

I had tried RIGHT(B143,FIND(" ",B143)-9) however the number is no
always the same number of digits and it sometimes gives a resul
including part of the text before it, or not all of the number

 

[Trevor Shuttleworth]

Try:

=VALUE(RIGHT(B143,LEN(B143)-1-FIND(" ",B143)))

Regards

Trevor


"Aussie_Striker"