Replace – String Processing Functions (VBA Functions)

If you constantly have to perform the same action or there is a need to automate some movements, then Excel has a special function – macros. These are small (or not so) scripts that allow you to perform a number of actions for the user. If you use macros correctly, you can perform almost any action, even one that is not provided by the standard functionality.

The simplest macros can be recorded using a dedicated button. It is enough to click it and perform certain actions. But for a more advanced level, you still have to learn a programming language. But actually, it’s not that difficult. The main thing is to have a good idea in your head how you can apply the macro. 

Macro programming is carried out using the VBA language. And it has one of the frequently used functions – Replace. 

Replace function definition, description

With the Replace function, you can replace an entire string or a single substring using a specific pattern as a guide. As a result, this function returns the resulting string. Moreover, the replacement can be carried out a certain number of times, and the function automatically makes the necessary adjustments.

With this function, you can automatically perform several actions at once:

1. Find a specific string and substring to be replaced. 
2. Replace text with the one specified by the user or returned by a formula. 
3. Find the next similar fragment. And replace it again. And so as many times as necessary. Specifically, this number is recorded in the Count parameter, which is recorded next.

If you need to replace within a certain range, then you need to use the Replace function for each cell. But if you use the Range object, you can greatly simplify the process.

To put it more simply, this function is a replacement for the “Find and Replace” window. It allows you to automate this process, for example, based on the results of calculations or actions taken earlier. For example, you can program a macro to automatically load student grades, and if the new grade is higher than the previous one, then instead of the text “studies badly”, write the line “studies better”. Thus, it is possible to greatly exceed those possibilities. which are used in the standard “Conditional Formatting” function, since such a comparison is not as convenient to implement with standard Excel tools as with macros (although if you get confused, you can). But there are also more complex tasks that can be implemented exclusively with the help of macros. 

Almost all Excel users know the Find and Replace function. It allows you to replace the desired string one or more times. Before we begin to consider how to automate the process, we must first understand the standard algorithm of user actions if he wants to replace one text with another. This is because you cannot consider more advanced tools without understanding the simple ones. 

So, in order to replace certain text with some string without a macro, you need to find the “Editing” item on the “Home” tab. There, in the “Editing” group, you need to find such a button. After that, a small menu will open, in which you will need to select the “Replace” item.

You can make your life much easier by remembering the key combination Ctrl + H once.

After that, such a window will appear.

In it we see two lines. In the first one, you need to specify the line that the program should find. The second contains the text that the first line should be replaced by the line described above. The window also provides the ability to configure the format by clicking on the “Options” button.

If it is necessary to replace all similar places, then this can be done using the “Replace All” button. If you simply click the “Replace” button, then you can choose which fragment to replace and which not. To skip some entry, you need to click the “Find next” button, and do this until the option that requires replacement is found. The user then clicks the Change button.

A nice bonus: using the Ctrl + Enter key combination, you can create an artificial line break to write multiple lines within the same cell.

What does this have to do with our topic? And the relationship is the most direct. Typically, to remove these breaks, the user manually removes the corresponding non-printing characters. But this can be done more easily using the Find / Replace window. After that, in the “Find” field, a line break character is indicated. It can be entered by pressing the key combination Ctrl + J. A dot will appear in the appropriate place, which indicates this non-printable character.

After that, it remains for us to enter the character to which the replacement will be carried out. Since our goal is to remove all line breaks, it is logical to replace them with spaces. If this is not done, then the characters can simply merge into one big word, which we do not need.

We see that after we clicked “OK”, the three lines were merged into one, and at the same time there is a space after each word. 

If you look at the search and replace window again, you will see. that after clicking on the “Parameters” button, a number of more parameters appear, making it possible to select the search area, viewing features and where to search for the desired values.

You can also set the display of values ​​​​to be case-sensitive (that is, large and small letters), and also check the box next to the “Entire cell” item. As for the format, it will be displayed in a special field next to the settings button. 

Replace function syntax and parameters

In general, the syntax of this function is as follows.

Replace(expression, find, replace, [start], [count], [compare])

Let’s look at the arguments of this function in more detail:

1. expression. This is the original string that needs to be replaced.
2. Find is the substring that is part of the top-level string in which to replace the value.
3. Replace is a substring that replaces the substring described in the previous argument. In other words, the text you want to replace with.
4. start. This parameter is optional. Characterizes the ordinal number of the string character from which the macro will search. The part of the string that is located before this number is simply not taken into account when replacing.
5. count. This is the number of iterations during which the replacement will be performed. This parameter is also optional. 
6. compare. This is a numeric value that is used to indicate the type of comparison. This parameter is also optional.

If you omit all the optional arguments, then the syntax of the Replace function looks less intimidating.

Replace(expression, find, replace)

Many people have a question: what is the difference between a parameter and an argument. Despite the fact that these terms are often used as synonyms, this is not entirely correct. A function argument is actually a variable that is used in the calculation. A parameter is a value that is in a variable. 

compare parameter

Using the compare parameter has its own peculiarities. You need to specify not a text value, but a numeric value, which serves as a constant indicating the type of comparison. Let’s look at them in more detail.

1. -one. In this case, the value specified by the Option Compare statement is used as an argument.
2. 0. This is a binary comparison.
3. 1 – text comparison.

The difference between text and binary comparisons is that letter case is different. In the first case, they are not taken into account, and in the second, uppercase and lowercase letters are taken into account when searching for strings and replacing them.

Binary comparison is used by default. Therefore, if you do not specify this argument in the function, then registers will be taken into account. If you need to make sure that the program does not take them into account, then you must specify the unit as the last parameter. 

Return value

In programming, there is such a thing as a data type. Since Excel tables are also programmable, even a person unfamiliar with macros understands what it is. There are several basic data types in VBA. In our case, the type String is returned. 

If the value of the Expression argument is null, then the string will return null. If the value of Expression was Null, then the formula will give an error. There are a few other things to keep in mind when using this feature:

1. If a string with a length of zero is entered as an argument to Find, then the function will return the same value that is entered as the Expression parameter. Technically, this means that there is no special substring to look for within the string that is passed to the Expression argument.
2. If the length of the Replace parameter is zero, then the string corresponding to the first parameter is returned with part of the second parameter removed. Technically, this means that a string of text has been replaced with an empty value. 
3. If a number greater than the length of the search string was passed as the Start parameter, then an empty string will be returned. If the number 0 is passed to this argument, then the search will be carried out after the null character, that is, immediately. Therefore, the value that is specified as the Expression parameter will be returned.

These points need to be paid special attention to and remembered before using the Replace function. This will help a lot in debugging written macros if you know these features. 

Example № 1

In this example, there is a single occurrence of the searched substring. And it needs to be replaced. 

SubFirst1()

Tell me

a = “Butter”

a = Replace(a, “Creamy”, “Redfish”)

MsgBox a

Result: “Rice oil”

End Sub

Example № 2

This example replaces multiple occurrences of a substring. 

SubFirst2()

Tell me

a = “A bear is coming, a fox is coming, a rook is coming”

‘with the default compare parameter

a = Replace(a, “going”, “running”)

MsgBox a

‘Result:

‘A bear is walking, a fox is running, a rook is running

a = “A bear is coming, a fox is coming, a rook is coming”

‘with parameter compare=1(vbTextCompare)

a = Replace(a, “walking”, “running”, , , 1)

MsgBox a

‘Result:

‘bear runs, fox runs, rook runs

End Sub

Example № 3

In this case, one substring is replaced, the search starts only from the fifteenth character.

SubFirst3()

Tell me

a = “A bear is coming, a fox is coming, a rook is coming”

a = Replace(a, “walking”, “running”, 15, 1)

MsgBox a

‘Result:

‘the fox is running, the rook is coming

End Sub

Conclusions

We see that the Replace function is quite convenient to use. With its help, you can achieve maximum flexibility, which is certainly important in working with spreadsheets. It is the building block that turns Excel from a program for compiling large databases into a full-fledged tool that can do everything. 

The “Find and Replace” function is actively used and can save a huge amount of time if used correctly. And this is if it is used by itself. If you write a script, then you need to invest time once in order to save it later. 

There are still a huge number of functions that allow you to automate the work with text in Excel. And there are some fancy ones, such as the Space() function, which makes it possible to get a string based on the number of spaces. Each function can be combined with others, providing an almost limitless range of possibilities. 

However, we see that this VBA function does not completely replace all of Excel’s string replacement capabilities. So, setting the format and a number of other features is not supported. However, this can be done with other functions, so there is no need to use such a large number of arguments in one simple procedure.