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)