Text to columns, removing duplicates)

[acaran02]

Hello all,

Need your help here. This is what I am trying to do. I have posted a sample workbook to hopefully help what i am trying to do get across clearer.


https://spreadsheets.google.com/ccc?...UWlRGVFE&hl=en

Column A contains the raw data I am working with. part of what i am trying to do is similar to my prior post (http://www.excelforum.com/excel-gene...ml#post1760658)

* first thing i need to do is to display all the websites on column B but without the "http://technorati.com/blogs/" prefix.

* on column C I would like to be able to automatically replace all the "%2F" characters with "/".

* On column D I would like to remove everything after the main website (i.e. instead of website1.com/subpage1/subpage2... it will just display website1.com)


so basically i need to have these raw data ..

http://technorati.com/blogs/open2.net%2Fblogs%2Fmoney
http://technorati.com/blogs/blogs.mi...oney-questions
http://technorati.com/blogs/www.pand...ncy-funds.html
http://technorati.com/blogs/www.cramers-mad-money.com
http://technorati.com/blogs/blogs.terrapinn.com%2Fcards
http://technorati.com/blogs/blogs.te...estment%2Ffeed
...

formulated into these results..

open2.net
blogs.mirror.co.uk
www.pandaikaya.com
www.cramers-mad-money.com
blogs.terrapinn.com
blogs.terrapinn.com



The next thing I need to do is to be able to have a formula that will automatically display all the unique web addresses from the results above onto another column (let's say Column E). That way the results will not display duplicate website names.

This forum has been invaluable and i hope you all guys can help me out. Many thanks in advance!

best regards,
Aaron

[martindwilson]

try like this uses helper column

[Marcol]

Try this
In B1

Please Login or Register  to view this content.

In C1

Please Login or Register  to view this content.

Drag/Fill both Down

Or you could combine the two
In B1

Please Login or Register  to view this content.

[EDIT]
Then use Martins' method to remove duplicates

Hope this helps

[acaran02]

MArtin and Marcol you guys are just awesome! these are exactly what I needed! thank you very much!

[Spyros13]

Wow Martin!! How!?! Why!? How!?!/ Wow

Thank you.

Genius. Merry Thank you's! Wow.