# Text to columns, split at first space only

1. ## Text to columns, split at first space only

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.

2. ## Re: 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.
>

3. ## Similar problem

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.

4. ## Re: Text to columns, split at first space only

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
>

5. ## Re: Text to columns, split at first space only

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

6. ## Re: Text to columns, split at first space only

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.

7. ## Re: Text to columns, split at first space only

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1