Excel is a well-known program developed by Microsoft, which is used to perform mathematical calculations and arithmetic operations in a spreadsheet. Although it seems quite limited, many users have given it numerous different applications, including programming. Regular expressions serve precisely to be able to handle data in an automated way.
This software can also be obtained by installing Microsoft Office online on Windows 10 and starting to enjoy its features, including the one mentioned in this tutorial.
Regular expressions in Excel are an invaluable tool for handling strings of data. These are used to locate different combinations of characters and apply actions based on the encoding given.
Its applications go more towards the area of programming and although there are tools that do their job, they are still of great value in the area.
What are regular expressions?
These expressions are defined as patterns whose function is to find different combinations of elements embedded in a text string. Thanks to his invention, data can be located in large amounts of information in a simple and flexible way.
Knowing how to use all the components of Microsoft Office | Mainly Word, Excel or PowerPoint, the rest will be a piece of cake when working with them.
For example, if you have a list of names and you want to find all those called “Luis, Louis and Lois” and group them. The regular expression pattern would be “L(u|ou|o)is”. This would allow you to create a lasso that joins the different elements under a match.
These expressions tend to be found under the abbreviation “Regex” or “Regexp”. Which come from the abbreviation of its name translated into English.
In programming its uses and applications are greater. However, the most used is the manipulation and search of data groups with a match, which is determined by the pattern that is established in the regular expression.
Regular expressions in Excel
Within the Excel program you can configure the use of these expressions. This will allow you to create the patterns that the program will use to find the data you want.
In addition to applying certain actions such as grouping them, separating them, dividing their characters, performing some arithmetic operation with their values, among others. It is important to have a minimum knowledge of programming to carry out the development of this tool in an optimal way.
Enable “Developer” or “Developer” tab
Depending on the version of Excel you have, it may appear with the title “Developer” or “Programmer”. To enable this option within Excel, it is necessary to select the “File” tab within the program. In the menu that is displayed, you must locate the word “Options” and select it. This will bring up the Excel settings menu.
In that menu, you must select the option “Customize ribbon actions”. On the right side, the option “Developer” or “Programmer” will be searched and the box will be selected to activate it. Then you must click on the “Accept” option to display the tab in the main bar of Excel as easy as changing the language in Microsoft Excel.
Add “Microsoft VBScript 5.5 Regular Expressions”
To start building this type of expression, this element must be added to Excel. To do this you must follow the steps below. Having completed the steps to enable the “Developer” tab.
This should be selected, which will change the actions on the bottom toolbar. In this bar you must select the “Visual Basic” option. This will display a separate window from Excel, called “Microsoft Visual Basic for Applications”.
In the “Microsoft Visual Basic for Applications” window, locate and click on the “Tools” option. Then click on the “References” option in the drop-down menu. Which will take you to another options window.
Find and check the box titled “Microsoft VBScript 5.5 Regular Expressions.” To finish adding this tool, you must select “Accept” in that same window.
Complying with the steps, you will be able to use this tool within Excel. It should be noted that it is necessary to learn the language of these expressions in order to create a functional code.
