How to replace text using regular expressions in Excel

Who I am
Marie-Ange Demory

Author and references

The programs that make up the Microsoft Office suite are really useful and are found on most computers. They have become present on mobile devices, being the main allies of those who use the mobile phone as a work tool .

Each of these programs has unique functions and features. In addition to a series of tricks that facilitate the your job .

How to replace text using regular expressions in Excel | Replace the operation

Regular expressions

This time you will be able to explore how to replace text with regular expressions using the program data manipulation, calculations and statistics .

These types of tools make heavy work easier 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 referred to as the replacement operation , a function that you can master with practice until you can 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:

  1. Go to the View tab.
  2. Right-clicking selects the Customize Ribbon option.
  3. Check the Developer box.
  4. Click OK.

You need to activate the developer or programmer tab in Microsoft Excel.Once it's enabled, click on it and then go to the option Visual Basic on the left side of the screen.

  1. The Visual Basic window will open, in it you will find the Tools tab.
  2. A drop-down menu will appear from that tab, where you'll select References.
  3. Find and check the box with the Microsoft VBScript Regular Expressions 5.5 option.
  4. Press accept.
Read What is the best anonymous or private browser to browse the Internet?

Replace text with expressions

Once you have opened the Excel document in 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 the Microsoft Visual Basic, you will start to do the work of developer.

Define why you will replace

You need to be confident and decisive based on what you intend to replace with the text and the type of element you intend to 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. Proceeds:

  • Back to Microsoft Visual Basic.
  • Insert a new form .

In this box, you will start typing the relevant items for replacing the text. This procedure will be divided into two modules. In the first form 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:

Sub replaceCells ()

Dim v_original, v_reemplazo As String

Dim Rango As Range

For each rank In Selection.Cells

v_original = rango.Value

v_reemplazo = sostituisci (v_original)

Se v_replacement <> «» Allora

rank.Value = v_replacement

End if

The next

End Sub

Moment of truth

As soon as you respect the content of these two modules, proceed to save it. 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 on the macros.
  • Select the run option
  • Cells with words starting with the letter C should automatically switch to negative regular expression.
Read How to Create Shake or Shake Effect in Video with Sony Vegas Pro?

On our See How to do it website you can conveniently and easily learn how to use and make relative references in Excel while recording Macro.

add a comment of How to replace text using regular expressions in Excel
Comment sent successfully! We will review it in the next few hours.