Replacing Part of String with VBA

Replacing information in an Excel sheet or workbook is a useful tool to use with VBA.  This can be quite useful for cleaning imported data from a source system.  Where the data is imported into Excel and needs to be cleansed before it gets manipulated. The following is an example of replacing data in a string with other data.  For Example;

Department 1 (500672)
Department 2 (600672)

If we want to remove everything after the first bracket "(" so the information looks like the following;

Department 1
Department 2

The following VBA code will do this without the use of a looping construct so the code runs very efficiently.

Option Explicit
Sub FindRep() 'Excel VBA to replace strings.
Dim strA As String
Dim strB As String

strA=" (*"
strB=""
Rows("1:10").Replace strA, strB
End Sub

Or if you want to remove data between two points in a string.  If for example the data looked like this;

Department 1 (500672) HR
Department 2 (600672) Finance

and you want the data to look like this;

Department 1 HR
Department 2 Finance

Then the following VBA will remove everything between the open bracket and the close bracket.

Sub FindRep2() 'Excel VBA for replace with wildcard.
Dim strA As String
Dim strB As String
strA=" (*)"
strB=""
Rows("1:10").Replace strA, strB
End Sub

The above Excel VBA coding can be reduced to the following without the use of variables.

Sub FindRep3() 'Alternative VBA method, no variables.
Rows("1:10").Replace " (*)", ""
End Sub

The above Excel VBA examples are shown in the attached file.