A little bit shorter formula (which assumes the street number will never be
one million or more)...

=SUBSTITUTE(A1,LOOKUP(999999,--LEFT(A1,ROW($1:$99))),
LOOKUP(999999,--LEFT(A1,ROW($1:$99)))&" ")

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Rob,

You don't need a macro you can use this formula to create what you want in
a
new column

=LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))&"
"&RIGHT(A1,LEN(A1)-LEN(LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))))

You then have choices. You can use paste special to paste values of this
formula back over the original data or simply hide the original column.

Mike

"rob c" wrote:

I need a program that puts a space between the street number and the
street
name of an address. They are in the same field and there are no
delimiters,
Example:
Currently should be
123First Street 123 First Street
2NSecond Street 2 NSecond Street

Any suggestions?