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.
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 10th 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.
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