Space in address

  • Thread starter Thread starter rob c
  • Start date Start date
R

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?
 
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
 
Thanks it works exactly the way I need it to.

Mike H said:
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
Click to expand...
 
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)))&" ")
 
Back
Top