Excel forumula to peel back Urls

Posted By: siteadmin on 03/06/2013 11:51:00

We were having difficulty remembering a simple formula to clean up a list of domain names for SEO.

We had a table that looked like this

http://www.example.com

http://www.example.com/

http://www.example.com/somedir

http://www.example/somedir/someotherdir/

and we just wanted the domain names with the www. parts (if applicable)

this is the formula for an item in cell A1: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,A1&"/",IF(RIGHT(A1,1)<>"/",LEFT(A1,FIND("/",A1,FIND("/",A1)+2)),A1))

The psedocode for this is:

if 2 "/" then append "/"

'http://www.example.com

if 3 "/" then

if(RIGHT(A1,1)<>"/",left(a1, FIND("/",A1,FIND("/",A1)+1)), a1)

if rightmost char <> "/" then

'http://www.example/somedir/someotherdir/

delete all to right of 3rd "/"

'=FIND("/",A1,FIND("/",A1)+2) returns the ordinal position of the 3rd "/"

or if rightmost char = "/" then

'http://www.example.com/

'is fine just return

if(RIGHT(A1,1)<>"/",

left(a1, FIND("/",A1,FIND("/",A1)+2)),

a1)

You need to determine number of occurence of a string, that is Count how often a value occurs in a cell by doing this sub test: =FIND("/",A1,FIND("/",A1)+2)

blog comments powered by Disqus

Kbytes Home | Privacy Policy | Contact us | Testing Area