Removing specific emails from a list of emails

[Jambo_]

Hi,

I recently had to export a list of emails to excel and want to refine the list but am not sure on how to.

The exported list shows who the email is from and the address it was sent to. In this example I will be hotmail.

Hotmail have been contacting apple over x number of years but hotmail have been cc'd into each email. This fills the To column with hotmail address that I don't want there. How do I delete the hotmail address and leave the apple and gmail account there?

The cell is split by ( ; space ). All the emails are within column B and multiple emails are in each cell.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(a,TEXTSPLIT(B2,"; "),TEXTJOIN("; ",,FILTER(a,ISERROR(SEARCH("hotmail",a)))))

 

[Automatrix]

Power Query solution: Split column by delimiter (Custom: "; ") into rows, then filter by Does Not Contain "@Hotmail".

Book1
	A	B
1	From	To
2	5@hotmail.com	1@hotmail.com; 2@hotmail.com; 3@gmail.com; 4@apple.com
3
4	From	To
5	5@hotmail.com	3@gmail.com
6	5@hotmail.com	4@apple.com
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"To", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "To"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"To", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each not Text.Contains([To], "@hotmail"))
in
    #"Filtered Rows"

 

[Jambo_]

Power Query solution: Split column by delimiter (Custom: "; ") into rows, then filter by Does Not Contain "@Hotmail".

Book1
	A	B
1	From	To
2	5@hotmail.com	1@hotmail.com; 2@hotmail.com; 3@gmail.com; 4@apple.com
3
4	From	To
5	5@hotmail.com	3@gmail.com
6	5@hotmail.com	4@apple.com
Sheet1

View attachment 80279

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"To", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "To"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"To", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each not Text.Contains([To], "@hotmail"))
in
    #"Filtered Rows"

Thank you! That's it working now

 

[Fluff]

Did you try the formula I suggested?