Move Split Cell
 

We Have addresses that have Apt. After the numeric digits without spaces we
want to move it to end how can we?
Ex; 123a Main St NY NY 10988 shall change to 123 Main St NY NY 10988 #a

Move Split Cell
 

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)&MID(A1,FIND("
",A1),99)&" #"&SUBSTITUTE(LEFT(A1,FIND("
",A1)-1),LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1) )))+0),"")


"dk" wrote:

We Have addresses that have Apt. After the numeric digits without spaces we
want to move it to end how can we?
Ex; 123a Main St NY NY 10988 shall change to 123 Main St NY NY 10988 #a

Move Split Cell
 

will this work no difference what the numbers are & tha alpha from a-z 1-0

"Teethless mama" wrote:

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)&MID(A1,FIND("
",A1),99)&" #"&SUBSTITUTE(LEFT(A1,FIND("
",A1)-1),LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1) )))+0),"")


"dk" wrote:

We Have addresses that have Apt. After the numeric digits without spaces we
want to move it to end how can we?
Ex; 123a Main St NY NY 10988 shall change to 123 Main St NY NY 10988 #a

Move Split Cell
 

It will work as long as number(s) in front


"dk" wrote:

will this work no difference what the numbers are & tha alpha from a-z 1-0

"Teethless mama" wrote:

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)&MID(A1,FIND("
",A1),99)&" #"&SUBSTITUTE(LEFT(A1,FIND("
",A1)-1),LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1) )))+0),"")


"dk" wrote:

We Have addresses that have Apt. After the numeric digits without spaces we
want to move it to end how can we?
Ex; 123a Main St NY NY 10988 shall change to 123 Main St NY NY 10988 #a

Move Split Cell
 

This one also work, but you have to Download and install the free add-in
Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html

then use this formula

=REGEX.MID(A1,"\d+")&MID(A1,FIND(" ",A1),99)&"
#"&REGEX.SUBSTITUTE(REGEX.MID(A1,"[^[:punct:]\s]+",1),"[0-9]","")



"dk" wrote:

will this work no difference what the numbers are & tha alpha from a-z 1-0

"Teethless mama" wrote:

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))) )+0)&MID(A1,FIND("
",A1),99)&" #"&SUBSTITUTE(LEFT(A1,FIND("
",A1)-1),LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1) )))+0),"")


"dk" wrote:

We Have addresses that have Apt. After the numeric digits without spaces we
want to move it to end how can we?
Ex; 123a Main St NY NY 10988 shall change to 123 Main St NY NY 10988 #a