Parsing URLs

[1953CAG]

Hi
I have a long list of URLs that I need to split. I know that I can do this and separate the domain, but what I actually want to do is to split the first two levels of the URL from the remainder, apols if I am not using the right terminology. So for example:-

full URL http://share.mycompany.com/path-name.../date/document name.ext

split into:-

First two levels http://share.mycompany.com/path-name
and
remainder /workspace/date/document name.ext or workspace/date/document name.ext

Is that possible?

Any help would be gratefully received, thanks in advance.

[1953CAG]

Also should have said, the URLs may not be the same length, some may just be the two levels that I need, but some may have several more levels. Some may have a document name, but others may not, etc, etc

[davsth]

If you post a few examples of where the split wants to take place, preferably in a spreadsheet, it will be easier to provide an answer

[kasan]

As an option you could split each url by "/" and then concatenate new url with one level.

[Aardigspook]

For the first two levels, use this:

Please Login or Register  to view this content.

If you don't want the final '/' included then add '-1' at the end, like this:

Please Login or Register  to view this content.

For everything after that, use this:

Please Login or Register  to view this content.

That won't include the '/' before - if you want the '/' included, then add '+1' at the end:

Please Login or Register  to view this content.

Hope that does what you want.

Edit: the above work by finding the fourth instance of the '/' character, so if you have a share address which doesn't start with 'http://share.' but directly with 'share.' then change the '4' in the above formulae to '2' instead.

[davsth]

Possibly
=LEFT(A1,FIND("@",SUBSTITUTE(A1,"/","@",4)))
==MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",4)),99)

but more detail would be better

[1953CAG]

Thanks Aardigspook, that worked an absolute treat! Made my day! Really grateful and appreciative of such swift responses from everyone too.

[Aardigspook]

You're welcome. Thanks for the feedback and the rep.

One point: if your url contains the character '#' already, then the formulae above might fail (depending on exactly where the '#' is). To get round this, you can replace the '#' with 'CHAR(160)' which is a blank space and therefore won't be in any url. Just select the formulae and do a find-and-replace of '#' with 'CHAR(160)' (without the inverted commas). I meant to change the formulae before I posted them earlier, but didn't - sorry!

[1953CAG]

Thanks, so far there are no # characters in the file, but I will amend that to the CHAR(160) as you suggest just in case there should be in the future!

Thanks again, really grateful :-)