Удалите определенный текст между запятыми в Excel

Мне нужно удалить все электронные письма, которые заканчиваются на @live.com, и оставить остальные электронные письма в ячейке. Большинство ячеек содержит несколько электронных писем, разделенных запятыми. Смотрите ниже:

Пример 1

Электронные письма:

[email protected], [email protected], [email protected], [email protected]

Ожидаемый результат:

[email protected]

Пример 2

Электронные письма:

[email protected], [email protected],[email protected], [email protected], [email protected]

Ожидаемый результат:

[email protected], [email protected]

Я начинающий пользователь Excel и не уверен, как использовать функции здесь.

Спасибо.

Следующая формула работает с версией MS365 Excel, так как, согласно помеченному посту, формула не будет применима для вашей версии Excel, поэтому предлагается использовать Power Query, который может элегантно разделить и затем объединить строку, используя разделитель.

• Формула, использованная в ячейке B1

=LET(
     a, TEXTSPLIT(A1,", "),
     TEXTJOIN(", ",1,FILTER(a, ISERR(FIND("@live.com",a)))))

Или, можно использовать следующим образом:

=MAP(A1:A3, LAMBDA(x,
 LET(b, TEXTSPLIT(x,", "),
 TEXTJOIN(", ",1,FILTER(b, ISERR(FIND("@live.com",b)))))))

Это также можно сделать с помощью Power Query, доступного в Windows Excel 2010+ и Excel 365 (Windows или Mac)

Чтобы использовать Power Query, выполните следующие действия:

• Сначала преобразуйте исходные диапазоны в таблицу и назовите её соответствующим образом, для этого примера я назвал её Table1

• Затем откройте пустой запрос из вкладки Данные –> Получить и преобразовать данные –> Получить данные –> Из других источников –> Пустой запрос

• Это откроет окно Power Query, теперь из вкладки Главная –> Расширенный редактор –> Вставьте следующий M-Code, удалив всё, что вы видите, и нажмите Готово

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Result", each Text.Combine(List.Select(Text.Split([Emails], ","), each not Text.EndsWith(Text.Trim(_), "@live.com")), ", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Emails"})
in
    #"Removed Columns"

• Наконец, чтобы импортировать обратно в Excel –> Нажмите на Закрыть и загрузить или Закрыть и загрузить в –> При нажатии на первый вариант будет создан Новый лист с требуемым результатом, в то время как второй вариант вызовет окно, в котором вам нужно будет указать место для размещения результата.

Вы также можете легко достичь своей цели с помощью функции UDF, написанной на VBA.
Содержимое функции должно быть помещено в стандартный модуль, вставленный в редактор VBA. Если вы еще не использовали VBA, в интернете много информации об этом.
Затем вы вызываете свою функцию на листе, как обычную функцию. Первый аргумент – это текст, который нужно обработать, второй – это фрагмент, который следует исключить.
Используйте разделители так, как вы обычно делаете в Excel.

Function FRemove(text As String, remv As String) As String
    Dim a As Variant
    a = Split(text, ",")
    a = Filter(a, remv, False)
    a = Join(a, ", ")
    FRemove = Application.Trim(a)
End Function