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

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  "/"
if 3 "/"  then
if(RIGHT(A1,1)<>"/",left(a1,     FIND("/",A1,FIND("/",A1)+1)), a1)
if rightmost char <> "/" then 
delete all to right of 3rd "/"
'=FIND("/",A1,FIND("/",A1)+2)  returns the ordinal position of the 3rd "/"
or if rightmost char = "/" then
 'is fine just return
left(a1,     FIND("/",A1,FIND("/",A1)+2)),
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