Microsoft Excel has a number of incredible tools to greatly facilitate the work of analyzing, processing and manipulating data, text and numbers.
The more advanced tools in this program can seem cumbersome. But the reality is, with patience and practice, there is nothing to stop you from learning new skills, like creating macros easily.
Regular expressions in Excel
The regular expressions tool is one of the tools taught in advanced courses. However, its simple operation optimizes the quality of your work, implementing and highlighting the order.
Regular expressions will help you check if the list of emails that you have in your Excel file has the relevant components. So, you could check, using this function, if they are true, if they have errors, or if they lack any symbol.
Exploring the function
In order to access this function of Microsoft Excel, it is important that you verify if you have enabled the Developer or Developer tab, which will allow you to create the modules to start the validation process.
If you can’t see it, just right-click on the ribbon and you can enable it with these simple instructions:
- Click on the Customize Ribbon option.
- Enable the Developer box.
- Click on the OK button.
- You must enter 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 Regular Expressions 5.5 box.
Email Considerations
Taking into account all the above guidelines, you can start the validation process using regular expressions. If you want to send mass emails from Excel with attachments, but the email address is misspelled, Excel will automatically mark it in red.
Remember that email addresses have a nomenclature for their confirmation. Example: [email protected] (.com, .gob, .es, .com.ar, etc.). The username can be made up of letters, numbers, and some characters, but no spaces or commas are allowed.
Validation process
Already having the necessary clarity to begin the process of validating the emails in your Excel file, it is a matter of starting to follow the following instructions:
- Having the first module open, start giving a name to the function you are going to perform:
Public Function Match (ByVal cell As String) As Boolean
- Create the regular expression object:
Set ExpReg = CreateObjetct (“VBScript.RegExp”)
- Proceed to configure the object configuration:
With ExpReg
.IgnoreCase = True
.Global = False
.Pattern = “^ ([a-z0-9 _ \ – ] +) @ [a-z0-9 -] + (. [A-z0-9 -] +) * (. [Az] {2 , 3}) $ “
match = .test (cell)
end with
- Click Sheet 1 and locate a bar with the word General.
- A menu is displayed in which you will choose the Worksheet option.
- In the bar next to it, click to bring up another menu and select the Change option.
- Delete the last two lines that will appear on the sheet, which are:
Private Sub Worksheet_SelectionChange (ByVal Target as Range)
End Sub
- Type the following commands:
Dim value As String
value = activecell.Offset (-1,0) .value
if match (value) = false then
activecell.offset (-1,0) .font.Color = RGB (255,0,0)
else
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, what remains is to check if all this work has been carried out successfully. How to check it? Well, in the following way:
- Save all changes made
- In column A you can start writing your email. If it is spelled correctly, it will mark it in black.
- If it is written incorrectly (you can put: username.com or your [email protected] leaving spaces) it will mark it in red.
In this way, you already have one more skill when using advanced functions in Excel to facilitate your work 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.