Separate text and number from address with no space

[cktrini]

How do I separate addresses in column that have no spaces. Ideally I want to format the address with spaces, but if I have to separate text and numbers into columns then concatenate, which I am not sure of. Here's an example:

121SW23rdSt
11964sw75 st
8199sw107
1243sw76 ave

 

[Tom Urtis]

One way, assuming your posted example is in A1:A4, place this formula into B1 and copy down to B4:

=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)&" "&MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)

Important, you must commit the formula to cell B1 by pressing Ctrl+Shift+Enter, not just with Enter.

 

[cktrini]

Thamk you so much. Now it looks great, but I am still seeing this

3050 SW110th St

How can I completely separate to:

3050 SW 110th St