Ron,

Thanks ... in B2 put:

=LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A3& "1,2,3,4,5,6,7,8,9,0")-1))

and enter as array formula.

re "123 First Marine Avenue 18 1303 11 1"

OP said street names ["The true text ( a series of Road names are
potentially all different, having a sequence of words that may be up to 5
words long before the numbers start)"] with no numbers ... or that was my
interpretation!

If "123 First " etc is valid, then my solution won't work.

Appreciate the feedback.


"Ron Rosenfeld" wrote:

On Sun, 20 Aug 2006 04:23:01 -0700, Toppers
wrote:

Try this ( works on my testing assuming single blanks as delimiters)

Assuming data in A2

in B1:
=LEFT(A2,SEARCH({1,2,3,4,5,6,7,8,9,0},A2& "1,2,3,4,5,6,7,8,9,0")-1)

Enter the following with Ctrl+Shift+Enter (array formulae)

in C1:

=VALUE(MID($A2,SUM(LEN($B2:$B2))+1,FIND("
",$A2,SUM(LEN($B2:$B2))+1)-SUM(LEN($B2:$B2))-1))

in D1:
=VALUE(MID($A2,SUM(LEN($B2:$C2))+2,FIND("
",$A2,SUM(LEN($B2:$C2))+2)-SUM(LEN($B2:$C2))-2))

in E1:
=VALUE(MID($A2,SUM(LEN($B2:$D2))+3,FIND("
",$A2,SUM(LEN($B2:$D2))+3)-SUM(LEN($B2:$D2))-3))

in F1:
=VALUE(RIGHT($A2,LEN($A2)-(SUM(LEN($B2:$E2))+3)))

HTH



Try these addresses:

123 First Marine Avenue 18 1303 11 1
First Marine Avenue 28 1303 11 1

Neither one seems to give the expected results using your formulas.


--ron