excel formula

E

eric

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?
 
E

eric

Gary''s Student said:
=MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1))
Click to expand...


almost. this resulted to

0R8qzgntg3Rsy4KM1286H. spaces.msn.com/0R8qz
 
T

T. Valko

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),"")
 
T

T. Valko

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),"")
 
E

eric

it worked! thanks!

T. Valko said:
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),"")
Click to expand...
 
Top