Objective
To find and replace strings in a column using VBA Excel.
Syntax of replace method
expression.Replace (What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
Replace method parameters
What : It represents the string which we want to replace, in simple terms it is the string which already exists.
Replacement: It represents the string which we want to bring in as replacement string, it will replace the old string.
LookAt – This is optional parameter,it has 2 options xlPart and xlWhole, by this option we define whether the string should match exactly or it can be part of search string. For example if we choose xlWhole then in that case if we try to search “Test” , then it will not find “Test1”. But if we choose xlPart , then it will find “Test1” as well , because “Test” is a substring of “Test1”.
SearchOrder – It is an optional parameter, it has 2 options, one is xlByRows and other one is xlByColumns.In case of XlByRows, it searches in an entire row , then it moves on to next row. In case of xlByColumn, it searches in one column then moves to next column.
MatchCase – It is an optional parameter. It has two options True or False.If it is set as true then it searches the string in case sensitive manner, if it is set as false , then it does not search in case sensitive manner.
MatchByte: It is also optional parameter, It has two options True or False .Some languages like Chinese , Japanese are stored in double byte format, so if you have installed double byte support in excel then you can use it.Set it as True if you want to match double byte character with double byte character , and set it as false if you want double byte character to match single byte character.
SearchFormat: It is optional parameter. , It has two options True or False .We can also search and find by matching its format. For example, “Test” and “Test” can be treated differently if we set this value as True.If we set its value as False then format of the search string will not matter, which means “Test” and “Test” will be treated as same.
ReplaceFormat: It is optional parameter which has values as True or False. Here we can replace a string with another string which has some another format. For example we can replace “Test” with “NewTest” , here “NewTest” is bold and italic.
Replace string “without” search format and replace format
In this approach we shall simply search for the string and replace it with the target string. In below shown code, we shall first select the range of cells in column A, then we shall replace the string which we are searching in the range with replacement string.
Sub ReplaceStrings()
Dim LastRow As Long
Dim RowNum As Long
Sheets("sheet1").Select
'Find the last row in column A
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through rows to find the empty cells
Range("A" & "2" & ": A" & LastRow).Select
'Replace the string in column A
Selection.Replace What:="Test_1", Replacement:="Test_N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Replace string “with” search format and replace format
In this approach we shall first check the format of the string which we are searching, then we shall replace the string with another string. Here the replacement string will have another format. In the example below we shall only replace string “Test_1” which is in italics , while those values of “test_1” which are not in italics will not be replaced.
In the code below we can see the find format and replace format.Find format specifies the format of search string while replace format specifies the format of replacement string.
Sub ReplaceStringsWithFormat()
Dim LastRow As Long
Dim RowNum As Long
Sheets("sheet1").Select
'Find the last row in column A
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through rows to find the empty cells
Range("A" & "2" & ": A" & LastRow).Select
'Setting the specific format which we shall use
With Application.FindFormat.Font
.FontStyle = "Italic"
.Subscript = False
.TintAndShade = 0
End With
With Application.ReplaceFormat.Font
.FontStyle = "Bold Italic"
.Subscript = False
.TintAndShade = 0
End With
'Replace the string in column A
Selection.Replace What:="Test_1", Replacement:="Test_N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=True
End Sub
In the image below we can see that this VBA code has replaced “Test_1” [which is in italic] , with “Test_1” , [which is in italic and bold]. Code has not replaced “Test_1” in cell A6, because it was not in italic. This is the usage of format in replace method, although it is not used very much , but it is good to know all the feataures of this method.
Notes
Don’t get confused between replace method and replace function. Replace method is associated with range class which falls under Excel library, while Replace function is a member of strings class and it falls under VBA library. It can be seen in object browser.
Post you may like
Copy data from one sheet to another workbook in VBA Excel
Reference
https://docs.microsoft.com/en-us/office/vba/api/excel.range.replace
