Extract a Portion of a URL Using a Formula

[langhamc]

I have a URL in column A and would like a fromula that extracts a portion of the URL in Column A and puts the results in Column B. In essance I want to extract everthing up to the forth "/" but not including the forth slash. Below is a representation of what I am looking for. Column A has the URL I want to manupulated, Column B has the new URL manipulated by a formula.

Column A
http://abc/sales/pages/home.aspx
http://abc/apps/lists/classified/default view.aspx
http://abc/accounting/pages/home.aspx
http://abc/markenting/pages/home.aspx
http://abc/hr/pages/hr.aspx
http://abc/directory/pages/person.aspx
Column B (results using a formula)
http://abc/sales
http://abc/apps
http://abc/accounting
http://abc/marketing
http://abc/hr
http://abc/directory
Thanks for your help!

 

[Rick Rothstein]

Give this formula a try...

=LEFT(A1,FIND("*",SUBSTITUTE(A1,"/","*",4))-1)

 

[langhamc]

That worked. Thanks for the prompt response!

 

[Scott Huish]

I would use a space because * can be found in URLs. http://www.ietf.org/rfc/rfc1738.txt

=LEFT(A1,FIND(" ",SUBSTITUTE(A1,"/"," ",4))-1)

 

[Rick Rothstein]

I would use a space because * can be found in URLs. http://www.ietf.org/rfc/rfc1738.txt

Good point! I was rushing to go out and rushed that answer too much without giving it enough thought. Thanks for catching that.

 

[langhamc]

Thanks all! You have been helpful and very responsive.