Return part of cell URL string based on number of '/' values

[chive90]

In Column C I have various URL's, however I only wish to display part of the URL string in Column B.

The URL string in Column C has various '/', but I would only like the URL string up to 5 '/' displayed in Column B.

Example Column C : http://toplevel/childlevel/name/123/456/abc/def/
Desired entry in Column B: http://toplevel/childlevel/name/

Is there a formula I can use in Column B to display what I am looking for?

Thanks

 

[Fluff]

How about

=LEFT(C2,FIND("^",SUBSTITUTE(C2,"/","^",5)))

 

[chive90]

How about

=LEFT(C2,FIND("^",SUBSTITUTE(C2,"/","^",5)))

Hey thanks that works great!

Related to this, is there a formula I can use to find how many entries contain the outputted URL?
e.g. now in B2 thanks to your formula I have http://toplevel/childlevel/name/ - I want to use this as a find/countif to return a count of how many records contain this URL - caveat though is that I don't want to match the exact URL, it also needs to count any that are longer, e.g. http://toplevel/childlevel/name/123/456/abc/def/

I tried using a simple countif and using the string in B2, but it only returns a count of those that exactly match B2, and not any that contain the first part of B2 and the longer string e.g. http://toplevel/childlevel/name/123/456/abc/def/

Is there a way to modify a countif to return any that match the first part of the string?

Thanks

 

[Fluff]

You can use

=countifs(a:a,b2&"*")

 

[chive90]

You can use

=countifs(a:a,b2&"*")

Thanks so much! Care to explain to an amateur like me how this works? What is the &"*" saying, is it that it must match B2 and any additional string after?

Thank you

 

[Fluff]

is it that it must match B2 and any additional string after?

That's right.