Excel help please

_Andy_

Hi guys

Hoping you can help - I know this is probably quite simple, maybe using LEFT and FIND?

Anyway, I have a huge list of websites. Mixture of domains so some are .com, some .de, etc etc.
All end with a trailing slash /

so eg

http://www.bbc.co.uk/
http://www.moneysavingexpert.com/

etc

What I'd like to do is have another column which extracts the type of domain (eg com, co.uk, nl, de) etc. So I can then filter the list by domain type.

But can't work out how to do it, especially as it's complicated by stuff like .co.uk.

I guess it needs to be something like it looks for text after the second '.' then loses the final / ?

Thanks in advance

phona

Been playing with that problem for a while and found this works:

=IF(ISNUMBER(FIND("co.uk",A1)),"UK","Non-UK")

to determine if it's .co.uk or something else.
To find other extensions you could use

=IF(ISNUMBER(FIND("co.uk",A1)),"UK",IF(ISNUMBER(FIND(".com",A1)),"USA","Other"))

So you can nest the statement inside itself by replacing "Other" with another copy of IF(ISNUMBER... etc
This will get messy quickly if you have lots of different extensions but should work (by my tests anyway!) There may be a better way...

Edit: by the way this can pick up wrong results if the URL happens to contain the extensions after the www. I tried
https://www.comparemyinsurance.co.uk - correctly detected as UK
https://www.completenonsense.eu - incorrectly detected as US
Maybe if .eu was in my IF-function it would have worked though?

Edit2: Please don't click those links... I have no idea if they go anywhere...

rmg1

Use this formula, it assumes rthe webpage address is in column A:-
=MID(A1,FIND(".",A1,FIND(".",A1,1)+1),LEN(A1)-B1)

I've tested it on .com and .co.uk addresses but that's about it.

_Andy_

thanks RMG - I get 'value' error tho (changed to A2 in my sheet) - also wasn't sure why the B1 ref?

thanks

rmg1

Ooops! I was testing my formula in bits and forgot to change everything to make it one formula. Try this:-
=MID(A1,FIND(".",A1,FIND(".",A1,1)+1),LEN(A1)-FIND(".",A1,FIND(".",A1,1)+1))

bod1467

www.bbc.co.uk/	.co.uk
www.bbc.com/	.com
www.bbc.de/	.de
www.bbc.fr/	.fr
www.bbc.eu/	.eu

Tests and results using RMG's formula.

_Andy_

awesome but it's chopping last digit off end for me for some reason, e.g. I get .co rather than .com

thanks again )

JimmyTheWig

_Andy_ wrote: »

awesome but it's chopping last digit off end for me for some reason, e.g. I get .co rather than .com

I would guess that you don't have a trailing slash on all of them?

Try this:
=SUBSTITUTE(MID(A1,FIND(".",A1,FIND(".",A1,1)+1),LEN(A1)-FIND(".",A1,FIND(".",A1,1))),"/","")

[Formula could be tidied up, but should work as it is.]

_Andy_

Ah dammit, forgot to say - have removed all trailing slashes

_Andy_

Sorted, thank you so much guys, really appreciate it