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.
