Cleaning URLs

[rajtak]

Hi,

I'd would like to clean URLs, In other words, if you have a url like this "http://www.abc.org/dada/aboutus.htm", then in the "Clean URL" column, this should be displayed as "http://www.abc.org".

I just need Homepage URL i.e. http://www.abc.org.

Please suggest any function.

 

[Makrini]

=left(a1,find("/",a1,8)-1)

 

[EducatedFool]

[FONT="Courier New"][SIZE="2"][COLOR="Navy"]    Cell: [/COLOR][B]B1      [/B][COLOR="Navy"]Formula  (style A1):   [/COLOR][B]=LEFT(A1,FIND("/",A1,10)-1)[/B]
[COLOR="Navy"]   [/COLOR][B][/B][COLOR="Navy"]                  Formula  (style R1C1): [/COLOR][B]=LEFT(RC[-1],FIND("/",RC[-1],10)-1)[/B]

[COLOR="Navy"]    Cell: [/COLOR][B]B2      [/B][COLOR="Navy"]Formula  (style A1):   [/COLOR][B]=LEFT(A2,FIND("/",A2,10))[/B]
[COLOR="Navy"]   [/COLOR][B][/B][COLOR="Navy"]                  Formula  (style R1C1): [/COLOR][B]=LEFT(RC[-1],FIND("/",RC[-1],10))[/B][/SIZE][/FONT]

 

[rajtak]

Thanks a lot for your suggestion. It works perfectly!!!

 

[GTO]

Certainly no better; just in case you want to clean them in place...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ex()<br><SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Selection<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> InStr(8, rCell.Value, "/", vbTextCompare) = 0 <SPAN style="color:#00007F">Then</SPAN><br>            rCell.Value = Left(rCell.Value, (InStr(8, rCell.Value, "/", vbTextCompare) - 1))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

...changing Selection to whatever range.

Mark