Forum Discussion

JohnGreenwood's avatar
JohnGreenwood
Copper Contributor
Apr 03, 2023

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:

www.domain.com

mail.domain.com.nl

ftp.domain.com

example.com

domain.com

 

And i need all the subdomains in one column and all domains in another one. (Don't neet to extract the domain from the subdomain.)

As seen below:

 

ListDomainSubdomain
www.domain.comexample.comwww.domain.com
mail.domain.com.nldomain.commail.domain.com.nl
ftp.domain.com ftp.domain.com
example.com  

domain.com

  
   
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 03, 2023

    JohnGreenwood 

    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!

    • JohnGreenwood's avatar
      JohnGreenwood
      Copper Contributor
      Apr 03, 2023
      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
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Apr 03, 2023

        JohnGreenwood 

        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:

        1. 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.
        2. 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.
        3. 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.

Resources