| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel formula
I have several data with this format:
1 test.msn.com/dfsmnfkmnfdf 1 test.msn.com/defgfdhrh How can I rearrange them to look like this format: dfsmnfkmnfdf.test.msn.com defgfdhrh.test.msn.com Can someone help me out with the formula? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
=MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1))
-- Gary''s Student - gsnu200854 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
"Gary''s Student" wrote: =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 almost. this resulted to 0R8qzgntg3Rsy4KM1286H. spaces.msn.com/0R8qz |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
That formula fails on the 2nd example.
It might be better to find the 1st space rather than assume the start of the string is always 1<space. =MID(A1,FIND("/",A1)+1,50)&"."&SUBSTITUTE(MID(A1,FIND(" ",A1)+1,100),MID(A1,FIND("/",A1),50),"") -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
Post the full input cell that caused the output to fail.
-- Gary''s Student - gsnu200854 "eric" wrote: "Gary''s Student" wrote: =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 almost. this resulted to 0R8qzgntg3Rsy4KM1286H. spaces.msn.com/0R8qz |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
Argh!
I should've known line wrap would fubar the formula! Here it is in chunks. Entered all on one line: =MID(A1,FIND("/",A1)+1,50)&"."&SUBSTITUTE (MID(A1,FIND(" ",A1)+1,100),MID(A1,FIND ("/",A1),50),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... That formula fails on the 2nd example. It might be better to find the 1st space rather than assume the start of the string is always 1<space. =MID(A1,FIND("/",A1)+1,50)&"."&SUBSTITUTE(MID(A1,FIND(" ",A1)+1,100),MID(A1,FIND("/",A1),50),"") -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
it worked! thanks!
"T. Valko" wrote: That formula fails on the 2nd example. It might be better to find the 1st space rather than assume the start of the string is always 1<space. =MID(A1,FIND("/",A1)+1,50)&"."&SUBSTITUTE(MID(A1,FIND(" ",A1)+1,100),MID(A1,FIND("/",A1),50),"") -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "eric" wrote in message ... it worked! thanks! "T. Valko" wrote: That formula fails on the 2nd example. It might be better to find the 1st space rather than assume the start of the string is always 1<space. =MID(A1,FIND("/",A1)+1,50)&"."&SUBSTITUTE(MID(A1,FIND(" ",A1)+1,100),MID(A1,FIND("/",A1),50),"") -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... =MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1)) -- Gary''s Student - gsnu200854 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
| Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
| match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
| Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
| i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) | |||
Linear Mode
