This article illustrates how to replace a character in a string by position using VBA in Excel with 4 effective approaches. We’ll use Excel’s built-in functions and methods like the Replace, and Mid functions to configure our code. Let’s dive into the examples to explore the techniques that can get your job done fast and easily.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

4 Effective Ways to Replace Characters in String by Position Using VBA in Excel

Write Code in Visual Basic Editor

To replace characters in a string by position, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Replace Character in String by Position by Using VBA Mid Function

Introduction to VBA Mid Function:

The Mid function in Excel VBA allows us to replace characters in a string by position. The syntax of the function is –
Mid (string, start, [length])
Where,
string – the string we want to replace characters from.
start– the position of the starting character to be replaced.
[length] – the number of characters to be replaced.
Task: Correct the spelling mistake of the string “Excelfemy” to “Exceldemy”.
Solution: We need to replace the 6th character “ f ” of the word “Excelfemy” with the character “ d “.
Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub ReplaceCharacterInString()
Dim str As String
str = "Excelfemy"
Mid(str, 6) = "d"
MsgBox str
End Sub

Output: The MsgBox showed the replaced string as the output.

Similarly, a number of characters inside a string can be replaced by position by using the Mid function easily.

2. Use of VBA Replace Function to Replace Character in String by Position

Introduction to VBA Replace Function:

The Replace function in Excel VBA facilitates us to replace characters within a string by specifying the start position of the string to be replaced and the number of replacements to be done. The syntax of the function is-

Replace(expression, find, replace, [start, [count, [compare]]] Where,
expression – the string we want to replace characters from.
find – the substring within the string to be replaced.
replace – new substring to replace the found substring.
start– the specified position to start finding and replacing.
count– no of replacements to be done the found substrings.
compare– represents the type of comparison. There are several types of comparison.

2.1 Replace the First Occurrence of Substring in a String

Task: Replace the status of the 1st order from Pending to Paid. The list of orders is: “Order 1: Pending; Order 2: Pending; Order 3: Pending”
Solution: We need to find the substring “Pending” in the order list and then replace only the first occurrence by specifying the count argument as 1.
Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub ReplaceCharacterInString()
Dim str As String
str = "Order 1: Pending; Order 2: Pending; Order 3: Pending "
str = Replace(str, "Pending", "Paid", Count:=1)
MsgBox str
End Sub

Output: The status of the 1st order changed to Paid from Pending.

2.2 Replace the Last Occurrence of Substring in a String 

Task: Replace the status of the 1st order from Pending to Paid. The list of orders is: “Order 1: Pending; Order 2: Pending; Order 3: Pending, Order 4: Pending ”
Solution: We need to find the substring “Pending” in the order list and then replace only the last occurrence. To do that, we’ll use the StrReverse function to reverse the character order of the string in the above code.
Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub ReplaceCharacterInString()
Dim str As String
str = "Order 1: Pending ; Order 2: Pending ; Order 3: Pending, Order 4: Pending "
str = StrReverse(Replace(StrReverse(str), StrReverse("Pending"), StrReverse("Paid"), Count:=1))
MsgBox str
End Sub

Output: The status of the last order changed to Paid from Pending.

3. Run a VBA Code with WorksheetFunction.Substitute Method to Replace a Character in String by Position

Introduction to the WorksheetFunction.Substitute Method:
This method also replaces a specific text within a string. The syntax is-

WorksheetFunction. Substitute(text, old_text, new_text, [nth_appearance])

Where,
text– the string we want to replace characters from.
old_text– the substring within the string to be replaced.
new_text– new substring to replace the found substring.
nth_appearance– the specified occurrence to replace from the found substrings.

Task: Replace the status of the 2nd order from Pending to Paid. The list of orders is: “Order 1: Pending; Order 2: Pending; Order 3: Pending, Order 4: Pending ”
Solution: We need to find the substring “Pending” in the order list and then replace only the 2nd occurrence by specifying the [nth_apperance] argument as 2.
Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub ReplaceCharacterInString()
Dim str As String
str = "Order 1: Pending; Order 2: Pending; Order 3: Pending; Order 4: Pending "
str = WorksheetFunction.Substitute(str, "Pending", "Paid", 2)
MsgBox str
End Sub

Output: Output: The status of the 2nd order changed to Paid from Pending.

4. Use of VBA Mid and Replace Functions to Replace Character in String by Position

Task: Replace the status of the 2nd and 3rd orders from Pending to Paid. The list of orders is: “Order 1: Pending; Order 2: Pending; Order 3: Pending, Order 4: Pending “.
Solution: To show the full list of orders after replacement, we need to take the help of the Mid function along with the Replace function in our code.
Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub ReplaceCharacterInString()
Dim orders, str As String
Dim position As Integer
orders = "Order 1: Pending ; Order 2: Pending ; Order 3: Pending; Order 4: Pending "
position = InStr(1, orders, "2", vbBinaryCompare)
str = Mid(orders, 1, position - 1) & Replace(orders, "Pending", "Paid", Start:=position , Count:=2)
MsgBox str
End Sub

Code Explanation: Here we used the InStr function to find the position of the substring 2 in the order list. We then used the position as the start argument in the Replace function to start searching for the word “Pending” after order no 1 in the list.
Output: The status of the 2nd and 3rd orders changed to Paid from Pending.

Notes

Let’s use only the Replace function without the Mid function. The code is-

Sub ReplaceCharacterInString()
Dim orders, str As String
orders = "Order 1: Pending; Order 2: Pending; Order 3: Pending; Order 4: Pending "
str = Replace(orders, "Pending", "Paid", Start:=18, Count:=2)
MsgBox str
End Sub

And the output would look like this–

The Replace function does truncate the part of the string starting from character no 1 to the specified character no as the start argument.

Conclusion

Now, we know how to replace characters in a string using VBA code with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

The post Excel VBA: Replace Character in String by Position (4 Effective Ways) appeared first on ExcelDemy.