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.

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.

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

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