Excel VBA: Replace Character in String by Position (4 Effective Ways)

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.

Replace Character in String by Position in Excel VBA

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.

Replace Character in String by Position in Excel VBA


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.

Replace Character in String by Position in Excel VBA


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_textnew 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.

Replace Character in String by Position in Excel VBA


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.

Replace Character in String by Position in Excel VBA


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.

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo