| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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)&" #"®EX.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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i split a cell? | Excel Discussion (Misc queries) | |||
| split a cell diagonally in excell - a calendar -2 dates in 1 cell | Excel Discussion (Misc queries) | |||
| Split cell help! | Excel Discussion (Misc queries) | |||
| How can I split one cell into two cell parts (upper & lower)? | Excel Discussion (Misc queries) | |||
| how do you split a cell? | Excel Discussion (Misc queries) | |||
Linear Mode
