The VBA Replace function is a function which you can use in your code to replace one set of string characters with another set of string characters, or to replace a set of string characters with an empty string.

For example, you may have a paragraph of text which refers to the city “New York” repetitively and you may wish to change this city to “Boston.”  You can use the replace function to find all instances of “New York” and replace those instances with “Boston”

The syntax of the replace function is:

Replace(Expression, Find, Replace, [Start], [Count], [Compare])

Where the arguments that are contained within square brackets are optional and do not need to be utilized if not required.

The Expression is the string of text that you would be searching within.

The Find is the string you would be searching for eg: “New York”

The Replace is the string of text that you would be replacing the string in the Find.

The [Start] is the position in the Expression string that you wish to start at.  If omitted (which is usually is), this starts at position 1 – so the beginning of the string.

The [Count] is the number of times you want the function to find the Find string, and replace it with the Replace string.  This is usually omitted and the function will search the entire Expression string until it no longer find the Find string.

The [Compare] is the type of data you are looking for.  This is usually omitted, and the default of binary data (vbBinaryCompare) is used.  However, you can specify it to vbTextCompare or vbDatabaseCompare if required.

For example,

Sub ReplaceText
Dim strNew As String
Dim strOld As String
'populate the original string
strOld = "The show is going to be set in New York.  New York is a vibrant city in the state of New York in the USA."
strNew = Replace(strOld, "New York", "Buffalo")
MsgBox strNew
End Sub

When I run the code above, the following message box is displayed

Now, as Buffalo isn’t a state – that is not quite correct!  

We can therefore limit the replace function to just replace the first two instances of New York, while leaving the first one as it is.

Sub ReplaceText
Dim strNew As String
Dim strOld As String
'populate the original string
strOld = "The show is going to be set in New York.  New York is a vibrant city in the state of New York in the USA."
strNew = Replace(strOld, "New York", "Buffalo",1,2)
MsgBox strNew
End Sub

In the above example, we are starting at position 1 of the variable strOld – but are only looking for two instances of the string that we wish to find.

If we only wanted to return the second sentence and not both sentences in the code above, we could amend the code to look like this:

Sub ReplaceText
Dim strNew As String
Dim strOld As String
'populate the original string
strOld = "The show is going to be set in New York.  New York is a vibrant city in the state of New York in the USA."
strNew = Replace(strOld, "New York", "Buffalo",42,1)
MsgBox strNew
End Sub

As starting at position 42 would give us two occurrences of the word “New York”, we would need to amend the [Count] argument to only look for 1 occurrence.

Leaving out the [Compare] option means that the Replace function will only find text that is in the correct case syntax.  For example, if we look for New York, but the text is new york – then the text will not be found.  If we want to make sure ALL instances of New York are found, regardless of upper or lower case, then we need to use vbTextCompare.  This is particularly useful when are looking through information that has been manually entered and may be subject to human error.

This code below:

Sub ReplaceText
Dim strNew As String
Dim strOld As String
'populate the original string
strOld = "The show is going to be set in New York.  New York is a vibrant city in the state of new york in the USA."
strNew = Replace(strOld, "New York", "Buffalo",,,[vbTextCompare])
MsgBox strNew
End Sub

Will return this message box below:

However, if we leave out [vbTextCompare] and therefore we would be using vbBinaryCompare, then this is what would be returned in the message box.

The [vbDatabaseCompare] option is only available to be used in Microsoft Access and where searching through Access data is based on the locale ID of the database table.  You can read more about this at https://msdn.microsoft.com/en-us/library/aa266181(v=vs.60).aspx

In addition to replacing one string with another, the replace function can be an extremely useful function to use for data cleaning when looping through information held in Excel spreadsheets or Access tables. Often, imported data will contain characters that are not required, causing the data to need “cleaning.”   In instances such as that, the replace function can be invaluable.

For example, in the VB snippet below, information could be passed to the CleanSpaces function where the information is meant to be a number, but the information may contain a space – thus turning the information from a number value into a string value – such as “2 000”

Sub CleanMe()
	MsgBox CleanSpaces("2 000")
End Sub

Function CleanSpaces(strV As String) As Double
    strV = Replace(strV, " ", "")
    CleanSpaces = CDbl(strV)
End Function

The replace function is looking for a space in the string value and replacing it with nothing, thus enabling the string value to be converted to a double value.

In this article I have explained about the value of using the Replace function in VBA and the importance that the different arguments of the Replace function.   It is also extremely useful in cleaning data that you may have imported from CSV files into Excel and Access where the information may contain characters that you do not want in your data – this could include dollar signs, percent signs, spaces and apostrophes to name just a few.