Text to columns, split at first space only

Assuming the addresses start in cell A2

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

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

And drag down ...

Regards

Trevor


"Wowbagger" <none> wrote in message
news:%23uG1tZTSGHA.1728@TK2MSFTNGP11.phx.gbl...
>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.
>

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

ie
cell = word word: number

need result
number

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

Try:

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

Regards

Trevor


"Aussie_Striker"
<Aussie_Striker.26m8iy_1145625304.4886@excelforum-nospam.com> wrote in
message news:Aussie_Striker.26m8iy_1145625304.4886@excelforum-nospam.com...
>
> I have a similar problem however I have data that has 2 spaces and I
> need the numeral at the end. With the formula above I get the word and
> numeral.
>
> ie
> cell = word word: number
>
> need result
> number
>
> I had tried RIGHT(B143,FIND(" ",B143)-9) however the number is not
> always the same number of digits and it sometimes gives a result
> including part of the text before it, or not all of the number.
>
>
> --
> Aussie_Striker
> ------------------------------------------------------------------------
> Aussie_Striker's Profile:
> http://www.excelforum.com/member.php...o&userid=33710
> View this thread: http://www.excelforum.com/showthread...hreadid=523252
>

I have a similar problem. I have a column of information where some cells start with a space and others do not. I want to split the cells with a space at the start in to the adjacent cell and leave the cells that do not start with a space where they are.

E.g.
The spreadsheet documents search queries in an image database. I have indicated in red the cells i wish to move to the adjacent right cell.

Capture.PNG