VBA string replace

To replace a sub-string withing a string with a new sub-string for a specified number of times, you can use replace(expression,search_string,replace_string[,start[,count,[Compare]]]).
-expression is a string that contains the sub-string to be replaced.
-search_string is a sub-string to be replaced.
-replace_string is a string used to replace.
-start specifies where the search begins. If omitted, the beginning of the string is where the search starts.
-count specifies the number of times to replace the sub-string. If omitted, all occurrences of the sub-string are replaced.
-Compare takes vbBinaryCompare value or vbTextCompare value. If you specify vbBinaryCompare, the comparison is case-sensitive. If you specify vbTextCompare, the comparison is case-insensitive.

Example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str, newstr As String
str = "VBA is a usful programming language for Office Applications."
newstr = Replace(str, "VBA", "VBA or Visual Basic for Applications", , , vbBinaryCompare)
MsgBox newstr

End Sub

Posted by: Dara | post date: 02-25-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.