Forum Discussion
Column division, filter Formula
You can use the following formula to separate the domains and subdomains into two different columns in Excel:
1. In the first cell of the “Domain” column (let’s say it’s B2), enter the following formula: =IF(ISERROR(FIND(".",A2,FIND(".",A2)+1)),A2,"").
This formula checks if there is a second period in the text string.
If there is no second period, it returns the value from column A.
If there is a second period, it returns an empty string.
2. In the first cell of the “Subdomain” column (let’s say it’s C2), enter the following formula: =IF(ISERROR(FIND(".",A2,FIND(".",A2)+1)),"",A2).
This formula checks if there is a second period in the text string.
If there is no second period, it returns an empty string.
If there is a second period, it returns the value from column A.
3. Drag these formulas down to fill the rest of the cells in columns B and C.
This should separate your list of domains and subdomains into two different columns.
See Example file.
Hope it has helped you!
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.