Space in address

[rob c]

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?

 

[Mike H]

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]

Thanks it works exactly the way I need it to.

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

 

[Mike H]

Glad I could help

Thanks it works exactly the way I need it to.

 

[Rick Rothstein]

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)))&" ")