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


"Wowbagger" <none> wrote in message
news:%[email protected]...
>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.
>

[Aussie_Striker]

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.

[Trevor Shuttleworth]

Try:

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

Regards

Trevor


"Aussie_Striker"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[darcyclay]

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

[arlu1201]

Darcyclay,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Russell Dawson]

Definitely worth starting a new thread. This is six years old!