Regular expressions
This time you will be able to explore how to replace text with regular expressions using the data manipulation, calculations and statistics.
This type of tool makes heavy work easier by using pattern detection. That is, they analyze and look for the elements you need such as letters or numbers.
How do I insert regular expressions?
These types of operations are often called replacement operation, a feature you can master with practice until you achieve it perfectly.
To start you must have the card Developer enabled located in the Excel toolbar. In case it does not appear, you must:
- Locate next to the View tab.
- Right-clicking selects the Customize Ribbon option.
- Check the Developer box.
- Click OK.
You need to activate the developer or programmer tab in your Microsoft Excel.Once enabled, click on it and then go to the option Visual Basic on the left side of the screen.
- The Visual Basic window will open, in it you will find the Tools tab.
- A menu will appear from that tab, where you will select References.
- Find and check the box with the Microsoft VBScript 5.5 Regular Expressions option.
- Press accept.
Replace text with expressions
Once you have opened the Excel document to which you want to apply the replace operation (The document must already be saved on your computer, in case you have opened a new one to take the test) and Microsoft Visual Basic, you will begin to do the work of developer .
Define why you are going to replace
You need to be confident and decisive based on what you will do to replace the text and what type of element you will use as a replacement. In this case, imagine you have a list of words in column A.
The parsing and search pattern for the replacement will be the words starting with the letter C or an asterisk that you want to remove, replaced by the negative expression. Income:
- Back to Microsoft Visual Basic.
- Insert a new form.
This box is where you will start typing the relevant items for text replacement. This procedure will be divided into two modules. In form one you will enter the following:
Once the first module is formed, it's time to model the second to start seeing the effects of the replacement. Enter the second form to enter the following:
Substitute Cells ()
Dim v_original, v_replacement As String
Attenuation range as a range
For each range in the selection cells
v_original = range.Value
v_reemplazo = replace (v_original)
If v_replacement <> «» Then
range.Value = v_replacement
End if
Next
End subtitle
Moment of truth
As soon as you have completed the content of these two modules, proceed to save. Remember that the your Excel file must be saved as a macro-enabled Excel workbook. When you go to the Excel file, you just need to test the added content as follows:
- Select cells with content.
- Click macro.
- Select the run option
- Cells with words starting with the letter C should automatically change to negative regular expression.
On our Look How to do it site you can learn in a practical and simple way how to use and make related references in Excel while recording Macros.
