Hi,

You can't use a formula because a formula can only directly change the value
of the cell it is in. You could use a helper column and sort and then
manually delete or you could try this macro which works on column A. Right
click the sheet tab, view code and paste this in.

Sub stantial()
Dim CopyRange As Range
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each C In MyRange
   If InStr(1, UCase(C.Value), "@XYZ.COM") > 0 Then
     If CopyRange Is Nothing Then
               Set CopyRange = C
       Else
               Set CopyRange = Union(CopyRange, C)
       End If
   End If
Next
If Not CopyRange Is Nothing Then
CopyRange.ClearContents
End If
End Sub

Mike

You cannot delete data with a formula. However, you could use a
formula in a helper column to flag that a cell needs deleting, and
then apply autofilter on that column to list all those marked for
deletion and then delete the appropriate cells in one (manual)
operation. For example, assume your email addresses are in column A,
then put this formula in your helper column:

=IF(LEFT(A1,7)="xyz.com","Delete","")

and copy this down. Then apply autofilter to the helper column,
selecting Delete from the filter drop-down. Then highlight all the
visible cells in column A and press the <delete> key. Then select All
from the filter drop-down.

Hope this helps.

Pete

On May 16, 12:46 pm, Sasikiran <Sasiki...@discussions.microsoft.com>
wrote:

Use find and replace
Find
*xyz.com
replace

(leave blank)
This will replace all selected cell with blanks