Is this Macro possible?

rodriar

New Member
Joined
May 4, 2015
Messages
14
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,
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;background-color: #FFFF00;;">john./doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;background-color: #FFFF00;;">john . Doe @ extension . com</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;background-color: #FFFF00;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;background-color: #FFFF00;;">john.Doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0000FF;;">john.doe@extension.com</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

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.

Code:
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 4, 2015
Messages
14

ADVERTISEMENT

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

New Member
Joined
May 4, 2015
Messages
14
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
1john.doe@extension.com
2john.doe@extension.com
3john./doe@extension.com
4john.doe@extension.com
5john . Doe @ extension . com
6john.doe@extension.com
7

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



After the macro:

Excel 2007
A
1john.doe@extension.com
2john.doe@extension.com
3john.doe@extension.com
4john.doe@extension.com
5john.Doe@extension.com
6john.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.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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...
Code:
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

New Member
Joined
May 4, 2015
Messages
14
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...
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,698
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,014
Messages
5,599,345
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top