How to validate emails with the use of regular expressions in Excel

Who I am
Shane Conder
@shaneconder
Author and references

Regular expressions in Excel

The regular expression tool is one of the tools taught in advanced courses. However, its simple operation optimizes the quality of your work by implementing and highlighting the order.

Regular expressions will help you check if the email list you have in your Excel file has the relevant components. Then, you can check, using this function, if they are true, if they have errors or if they are missing symbols.


Exploration of the function

To access this Microsoft Excel function, it is important to check if the tab Developer o Developer is enabled, which will allow you to create forms to start the validation process.


If you don't see it, right-click on the ribbon and you can enable it with these simple instructions:

  • Click the Customize Ribbon option.
  • Enable the Developer checkbox.
  • Click the OK button.
  • You need to go to the Developer tab and then click on Visual Basic.
  • A window will open in which you will look for the Tools tab.
  • Click References.
  • Find and check the Microsoft VBScript 5.5 Regular Expressions box.

Email Considerations

Taking into account all the guidelines above, you can start the validation process using regular expressions. If you want to send bulk messages from Excel with attachments, but the e-mail address is incorrect, Excel will automatically mark it in red.

Remember that email addresses have a nomenclature for their conformation. Example: username@domain.origin (.com, .gob, .es, .com.ar, etc.). The username can consist of letters, numbers and some characters, but spaces or commas are not allowed.



Validation process

Already having the clarity needed to start the validation process of the emails in your Excel file, it is a matter of starting to follow the following instructions:

  • With the first module open, start giving a name to the function you are going to perform:

Public Function Match (cella ByVal As String) As Boolean

  • Create the regular expression object:

Imposta ExpReg = CreateObjetct (“VBScript.RegExp”)

  • Proceed with the configuration of the object configuration:

With ExpReg

.IgnoreCase = True

.Global = False

.Pattern = “^ ([a-z0-9 _ -] +) @ [a-z0-9 -] + (. [A-z0-9 -] +) * (. [Az] {2, 3} ) $

match =.test (cella)

finish with

  • Click Sheet 1 and locate a bar with the word General.
  • A menu appears for you to choose your option Worksheet.
  • In the bar next to it, click to see another menu and select the Edit option.
  • Delete the last two lines that will appear on the sheet, which are:

Private sub worksheet_SelectionChange (ByVal Target as range)


End subtitle

  • Type the following commands:

Dimmed value as a string

value = active cell.Offset (-1,0) .value

if match (value) = false then

activecell.offset (-1,0).font.Color = RGB (255,0,0)

other

activecell.offset (-1,0).font.Color = RGB (0,0,0)

end if

  • Remember to save the document as a macro-enabled Excel workbook.

Check the work done

At this point all that remains is to check if all this work has been done successfully. How to verify it? Well, as follows:


  • Save all changes made
  • In column A you can start writing yours email. If it is spelled correctly, it will mark it in black.
  • If it is spelled incorrectly (you can put: username.com or your email@gmail.com leaving some spaces) it will mark it in red.

This way, you already have an extra skill when using advanced functions in Excel to make your work easier when checking data of this type.

In this way, you can also learn how to validate data using regular expressions in Excel from our website.


add a comment of How to validate emails with the use of regular expressions in Excel
Comment sent successfully! We will review it in the next few hours.