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?

excel formula
 

=MID(A1,FIND("/",A1,1)+1,255) & "." & MID(A1,3,LEN(A1)-FIND("/",A1,1))

--
Gary''s Student - gsnu200854

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

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

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

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

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

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