Excel forumula to peel back Urls
Account Home | Help | Blog | Contact us | Log Out


Welcome to Kbytes > Articles

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

© 2004 - 2018 1 Oak Hill Grove Surbiton Surrey KT6 6DS Phone: +44(020)82617371