Is this Macro possible?

[rodriar]

Hi,

I would like to create a macro or excel formula that can verify email addresses in a column.

For example, I need to verify a list with a lot of emails that cannot have any special characters or spaces, just one below each other like these:

john.doe@extension.com
john.doe@extension.com
john.doe@extension.com
john.doe@extension.com
john.doe@extension.com
john.doe@extension.com

And if someone comes up with a list that contains special characters or spaces, the formula/macro could verify and highlight where is the error. I just want to paste the whole list in a column to get it validated.

john.doe@extension.com
john.doe@extension.com
john./doe@extension.com
john.doe@extension.com
john.doe@extension.com
john.doe@extension.com

Let me know if i was clear

Thanks,

 

[hiker95]

rodriar,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[rodriar]

Hi Rick!

Hope you're well. You have helped me few weeks ago with this macro:


Sub VerifyEmailAddresses()
Dim R As Long, X As Long, CellVal As String
Application.ScreenUpdating = False
Columns("C").Replace " ", "", xlPart
For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
CellVal = Cells(R, "C").Value
For X = 1 To Len(CellVal)
If Mid(CellVal, X, 1) Like "[!A-Za-z.@]" Or _
(Mid(CellVal, X, 1) = "@" And Len(CellVal) - _
Len(Replace(CellVal, "@", "")) > 1) Then
With Cells(R, "C").Characters(X, 1)
.Font.ColorIndex = 3
.Font.Bold = True
End With
ElseIf Mid(CellVal, X, 2) = ".." Then
With Cells(R, "C").Characters(X, 2)
.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
Next
Next
Application.ScreenUpdating = True
End Sub


How can i add the functionality to verify if every item in the column contains the extension @appe.com and if they don't, to add them automatically?