Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Space in address
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Space in address
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Space in address
Thanks it works exactly the way I need it to.
"Mike H" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Space in address
Glad I could help
"rob c" wrote: Thanks it works exactly the way I need it to. "Mike H" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Space in address
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard for space text space pattern | Excel Programming | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) | |||
Mail Merge creates a space in the address block | Excel Discussion (Misc queries) | |||
How do I remove a blank space on a set of address labels when u. | Excel Programming | |||
Custom functions address space | Excel Programming |