Excel Replace Function: Replace a String of Characters

Replace Excel function is another useful function. It is used to replace character or string of characters within another string.

The Basics:

Replace( StringToProcess, StringToFind , StringToReplace , Starting Position , NumberOfSubstitutions , CompareOption )

StringToProcess = The string that will be searched

StringToFind = The string that will be searched for

StringToReplace = The string that will replace each occurrence of the StringToFind value

Starting Position = Position where the replace function is to start. For example, you can specify that the search and replace function should start from the 10^th character. This value is optional and not required to be defined.

NumberOfSubstitutions = How many replacements you want to do. For example, you only want the replace function to be performed twice in each row. This value is optional and not required to be defined.

CompareOption = You can defined the compare option to use such as case sensitive or not. This value is option and

Now let’s look at example. Let’s assume that we have a group of date values in Excel, which contain month, day and year values separated by forward flashes. Let’s assume we want to replace the forward slash with dash values. Also for some strange reason we only want to replace one occurrence of the dash values.

Code

Dim A As Long 'The counter we will be using the process our records

Dim StringToProcess As String 'The variable that will contain the string to be evaluated

Dim Position As Long 'This variable will contain the position of the character searched for

For A = 1 To 4

StringToProcess = ActiveSheet.Cells(A, 1).Value 'Assigns the value to the variable to be processed. The value assigned to the variable

'StringToProcess will change as the variable A changes.

StringToProcess = Replace(StringToProcess, "/", "-", 1, 1, vbTextCompare)

ActiveSheet.Cells(A, 1).Value = StringToProcess

Next

Output