Replace String

VBA – Replace String

The Replace function in VBA is used to replace all occurrences of a specified substring within a given string with another string.

Syntax:

Replace(string, find, replacewith[, start[, count]])

Parameter:

string: The original string where you want to perform the replacement.
find: The substring that you want to find and replace.
replacewith: The substring that will replace find.
start: (Optional) The position within string to start the search. If omitted, the search begins at the first character.
count: (Optional) The number of replacements to perform. If omitted, all occurrences are replaced.

Return Value:

String: Returns the modified string after the replacements have been made

Example

				
					Dim str1 As String
Dim newStr As String

str1 = "Hello, World! Hello again!"
newStr = Replace(str1, "Hello", "Hi") 

' newStr will be "Hi, World! Hi again!"

newStr = Replace(str1, "Hello", "Hi", 1) 

' newStr will be "Hi, World! Hello again!" (Only the first occurrence is replaced)

Use Cases:

Text Manipulation:
- Correcting typos or errors in text.
- Replacing specific words or phrases with others.
- Standardizing data formats.
Data Cleaning:
- Removing unwanted characters or symbols from strings.
- Replacing invalid characters with valid ones.
String Formatting:
- Changing the case of characters within a string.
- Replacing special characters with HTML entities.

Key Considerations:

The Replace function is case-sensitive by default.
To perform case-insensitive replacements, you can use the StrConv function to convert the strings to uppercase or lowercase before using Replace.

Example: (Case-Insensitive)

				
					Dim str1 As String
Dim newStr As String

str1 = "Hello, World! Hello again!"
newStr = Replace(StrConv(str1, vbUpperCase), "HELLO", "Hi") 

' newStr will be "Hi, World! Hi again!"