How to filter out all rows which do not contain external email addresses in a particular column

Would a custom function that returns TRUE or FALSE be an acceptable solution for you?

For example, this defines a custom function FindExternal and takes the cell with emails as the first argument, and company's email as the second argument:
=FindExternal(C2,"mycompany.com")

Option Explicit

Function FindExternal(rng As Range, domain As String) As Boolean
    Dim text As String
    Dim emails() As String
    Dim email As Variant
    Dim pos As Integer
    Dim result As Boolean
    
    result = False
    text = rng.Cells(1, 1)
    emails = Split(text, ",")
    For Each email In emails
        pos = InStr(email, "@")
        If pos > 0 Then
            email = Trim(Mid(email, pos + 1, 100))
            If email <> domain Then
                result = True
                Exit For
            End If
        End If
    Next
    FindExternal = result
End Function

Select allOpen in new window

That's great thank you, as well as 'mycompany.com' can we also filter out 'onmicrosoft.com'?

Sure!

I replaced the second parameter with a range, so you can select the cells containing multiple company's domains, something like:

=FindExternal(C2, $E$1:$E$3)

where you will enter your domains in E1:E3: E1 = mycompany.com, E2 = mytenant.onmicrosoft.com, E3 = mytenant.mail.onmicrosoft.com

Option Explicit

Function FindExternal(rng As Range, domainRng As Range) As Boolean
    Dim text As String
    Dim emails() As String
    Dim email As Variant
    Dim pos As Integer
    Dim domains() As String
    Dim domain As Variant
    Dim result As Boolean
    
    result = False
    ReDim domains(domainRng.Cells.Count - 1)
    pos = 0
    For Each domain In domainRng
        domains(pos) = domain
        pos = pos + 1
    Next
    text = rng.Cells(1, 1)
    emails = Split(text, ",")
    For Each email In emails
        pos = InStr(email, "@")
        If pos > 0 Then
            email = Trim(Mid(email, pos + 1, 100))
            If (UBound(Filter(domains, email)) < 0) Then
                result = True
                Exit For
            End If
        End If
    Next
    FindExternal = result
End Function

Select allOpen in new window

Thanks Boris, the values would be in the same cell  (e.g. If Cell C2 contains 'mycompany.com' or 'onmicrosoft.com').

Effectively i'm trying to do this:  =FindExternal(C2,"mycompany.com" OR "onmicrosoft.com") 

The function I wrote expects the range where your company's emails are written, one in each cell.

Sorry, I don't understand the last comment.  Are you saying I need to add additional columns and place each domain in those columns?

How about using a FIND function:

=IF(AND(ISERROR(FIND("mycompany.com",C2,1)),ISERROR(FIND("onmicrosoft.com",C2,1))),"No Company email","Includes Company email")

This looks for "mycompany.com" and "onmicrosoft.com" and if it finds neither it returns "No Company email", otherwise it just returns "Includes Company email"

You can then filter on the column where you put that formula to show only "No Company email"

Thanks Rob, to explain a bit more what i'm trying to do.  I have exported a list of email distribution groups, each row contains information about the distribution group.  One cell in each row contains the email address of each of the distribution group members.  Most if not all of the distribution groups will contain internal addresses (either mycompany.com, mytenant.mail.onmicrosoft.com or mytenant.onmicrosoft.com), I'm trying to identify the distribution groups which also contain external email addresses.

Sorry, don't think my suggestion will work after all.

It will only flag those where there is no company email, ie only external, not those where there is a combination of both.

Working on an alternative.

See attached with your samples.

Column E: uses the SUBSTITUTE function to determine how many emails in total in cell, replace @ with blank and compare lengths will show number of emails

Column F & G: uses the SUBSTITUTE function to determine how many emails with domain in row 1, again replacing "@"&"domain" with "|"&"domain" and then replacing "|" with blank and comparing length

Column J: column E - sum of F & G gives total number of external addresses.

Email extraction.xlsx

If you don't have too many domains to search, you could just remove the internal domains using nested SUBSTITUTE, then look for @  in the remaining string. Note that SUBSTITUTE is case sensitive, so I needed to make sure the string being searched is lower case.

=IF(ISNUMBER(FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(C2),"@mycompany.com",""),"@mytenant.mail.onmicrosoft.com",""),"@mytenant.onmicrosoft.com",""))),"External","")

Select allOpen in new window

Filter on the results of this formula in an auxiliary column.

Thank you Boris that is exactly what I needed!

And thank you everyone who contributed, have a great day!