On Mon, 25 Jun 2007 07:50:00 -0700, Larry
wrote:

Does anyone know of a formula (or combination of formulas) that I can use to
split cells containing combined numerical and alpha datainto 2 separate cells.

Sample Data:
123 Anywhere Dr
10 Somewhere Dr
4998 Somewhere Else Ln

I have a listing of addresses and I want to split the number part of the
address and the alpha part of the address into 2 columns for sorting
purposes. I want to sort results by street name then by number. As you can
see above, the length of the numerical part of the address can vary, it is
not fixed in length.

Thanks,
Larry

The length of the nubers can be different as show above.

A2: 123 Anywhere St
B2:
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345678 9"))LEN(A2),
"",LOOKUP(1E+307,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))))

C2: =TRIM(SUBSTITUTE(A2,B2,"",1))


--ron