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,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider, if your raw data is in column A, per the following screenshots.

The macro will remove the / character, and, a space character.

Sample raw data:

Excel 2007
	A
1	john.doe@extension.com
2	john.doe@extension.com
3	john./doe@extension.com
4	john.doe@extension.com
5	john . Doe @ extension . com
6	john.doe@extension.com
7
Sheet1

After the macro:

Excel 2007
	A
1	john.doe@extension.com
2	john.doe@extension.com
3	john.doe@extension.com
4	john.doe@extension.com
5	john.Doe@extension.com
6	john.doe@extension.com
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveSpecialCharacters()
' hiker95, 05/04/2015, ME852996
With Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ",""/"",""""),"""")")
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" "",""""),"""")")
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveSpecialCharacters macro.

 

[hiker95]

rodriar,

1. Can we have a list of the special characters that you would want to remove?

2. Can we see some strings/e-mails containing the special characters?

 

[Rick Rothstein]

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,

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

As far as I can tell from an Internet search, the slash character is a valid character for the locale (the part in front of the @ sign), so your wanting to remove it raises the question as to what you consider a "special character". Is your request, perhaps, that you want email addresses made of letters and dots only? What about underlines and dashes? Please clarify what your full requirements are for us.

 

[rodriar]

As far as I can tell from an Internet search, the slash character is a valid character for the locale (the part in front of the @ sign), so your wanting to remove it raises the question as to what you consider a "special character". Is your request, perhaps, that you want email addresses made of letters and dots only? What about underlines and dashes? Please clarify what your full requirements are for us.

Hi Rick!

I actually work with a tool that only allows me to insert .txt files with the following format:

john.grime@accenture.com
name.surname@accenture.com
name.surname.surname@accenture.com

I would like to prevent people to send me txt files with this characters included: ('; _; ..; ...; -; /; (; ; %; &; etc.)


Is there any way to move this macro to work in column C row 2 rather than A1? So I can give it some formatting, and paint it like a table.


Really thanks in advance, this is really appreciated.

 

[rodriar]

rodriar,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider, if your raw data is in column A, per the following screenshots.

The macro will remove the / character, and, a space character.

Sample raw data:

Excel 2007

	A
1	john.doe@extension.com
2	john.doe@extension.com
3	john./doe@extension.com
4	john.doe@extension.com
5	john . Doe @ extension . com
6	john.doe@extension.com
7

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A
1	john.doe@extension.com
2	john.doe@extension.com
3	john.doe@extension.com
4	john.doe@extension.com
5	john.Doe@extension.com
6	john.doe@extension.com
7

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveSpecialCharacters()
' hiker95, 05/04/2015, ME852996
With Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ",""/"",""""),"""")")
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","" "",""""),"""")")
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveSpecialCharacters macro.

Hi !!

I actually work with a tool that only allows me to insert .txt files with the following format:

john.grime@accenture.com
name.surname@accenture.com
name.surname.surname@accenture.com

I would like to prevent people to send me txt files with this characters included: ('; _; ..; ...; -; /; (; ; %; &; etc.)


Is there any way to move this macro to work in column C row 2 rather than A1? So I can give it some formatting, and paint it like a table.


Really thanks in advance, this is really appreciated.

 

[Rick Rothstein]

The following macro will highlight, in red, any cells in Column C, starting with Row 2, which is not composed of only letters, digits and dots and where there is exactly one ampersand followed by at least one dot...

Sub VerifyEmailAddresses()
  Dim R As Long, CellVal As String
  For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
    CellVal = Cells(R, "C").Value
    If CellVal Like "*[!A-Za-z0-9.@]*" Or CellVal Like "*..*" Or _
       CellVal Like "*@*@*" Or Not CellVal Like "*@*.*" Then _
       Cells(R, "C").Interior.ColorIndex = 3
  Next
End Sub

 

[rodriar]

The following macro will highlight, in red, any cells in Column C, starting with Row 2, which is not composed of only letters, digits and dots and where there is exactly one ampersand followed by at least one dot...

Sub VerifyEmailAddresses()
  Dim R As Long, CellVal As String
  For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
    CellVal = Cells(R, "C").Value
    If CellVal Like "*[!A-Za-z0-9.@]*" Or CellVal Like "*..*" Or _
       CellVal Like "*@*@*" Or Not CellVal Like "*@*.*" Then _
       Cells(R, "C").Interior.ColorIndex = 3
  Next
End Sub

Hi Rick!

Thanks for your prompt response. Is there any way the macro can fix the list instead of higlighting it? For example, if it finds '..' to turn it into '.' and if it finds '/; - ; _ ; @ ; etc' to remove them?

 

[Rick Rothstein]

Hi Rick!

Thanks for your prompt response. Is there any way the macro can fix the list instead of higlighting it? For example, if it finds '..' to turn it into '.' and if it finds '/; - ; _ ; @ ; etc' to remove them?

Are you sure you want to automatically assume that a bad character should be removed as opposed to assuming it was a mistype of a dot (I am thinking particularly of the slash as it is right next to the dot, but the same could apply to a semicolon, apostrophe or comma as well)? Your original post asked to have them identified which made more sense to me as you could (theoretically) look at the email address and decide on a case-by-case basis whether the character should be removed or corrected to a dot. If you want automatic correction, I rewrite the code for you, just let me know, but be aware that such code will be much slower than the code I posted earlier.

Also, I am pretty sure you do not want to remove the @ symbol given the code is parsing email addresses. There is a question of what to do if there are two ampersands in the same address... you cannot expect the code to be able to decide which one should be removed. Also, if there is no dot after the ampersand, that email address would be improper... again, you cannot expect the code to figure out how to include one. For these two cases, I am guessing highlighting them in red is still okay to do even if your answer to my question above is to remove those "bad" symbols.

 

[hiker95]

rodriar,

I would like to prevent people to send me txt files with this characters included: ('; _; ..; ...; -; /; (; ; %; &; etc.)

I am confused by your text string list.

Can you display the separate characters vertically?