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.

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
...
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.

Text to columns, split at first space only
 

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

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@excelforu m-nospam.com wrote in
message news:Aussie_Striker.26m8iy_1145625304.4886@excelfo rum-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