Forum Discussion
Column division, filter Formula
Hi all, I have a list of almost 900 domain and subdomain in the same column. and I needa formula to separate those into 2 different columns. For example List: http://www.domain.com mail.do...
Hi!!! This is good.
But what can i do if not every domain is .com for example .co.uk, the formula thinks it's a subdomain
But what can i do if not every domain is .com for example .co.uk, the formula thinks it's a subdomain
If your list includes domains with multiple extensions such as .co.uk, you can modify the formula to account for these cases. Here’s an example of how you can do this:
- In the first cell of the “Domain” column, enter the formula =IF(OR(ISERROR(FIND(".",A2,FIND(".",A2)+1)),RIGHT(A2,6)=".co.uk"),A2,""). This formula checks if there is a second period in the text and if not, or if the text ends with .co.uk, it returns the text in the “List” column.
- In the first cell of the “Subdomain” column, enter the formula =IF(AND(NOT(ISERROR(FIND(".",A2,FIND(".",A2)+1))),NOT(RIGHT(A2,6)=".co.uk")),A2,""). This formula checks if there is a second period in the text and if so, and if the text does not end with .co.uk, it returns the text in the “List” column.
- Drag these formulas down to fill the rest of the cells in their respective columns.
This should separate your list of domains and subdomains into two different columns while accounting for domains with multiple extensions such as .co.uk.
You can extend the formula as you like, depending on the exceptions you want.