software

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(quot; quot;,A2)-1)

=RIGHT(A2,LEN(A2)-FIND(quot; quot;,A2))

And drag down ...

Regards

Trevorquot;Wowbaggerquot; lt;nonegt; wrote in message
...
gt;I have a 2500 line column of street addresses along the lines of (123 E
gt;Grand Maple). How can I split this column into two columns, first column
gt;only the numeric portion, second column only the street portion even though
gt;the street portion may contain spaces?
gt;
gt; For simplicity's sake I can assume that the first space in the address
gt; represents where the split should occur and that any subsequent spaces
gt; should be ignored.
gt;

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(quot; quot;,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: www.excelforum.com/member.php...oamp;userid=33710
View this thread: www.excelforum.com/showthread...hreadid=523252Try:

=VALUE(RIGHT(B143,LEN(B143)-1-FIND(quot; quot;,B143)))

Regards

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