The VBA Replace function is great, but it has limitations. It’s easy to use it to replace all occurrences, the first occurrence, and the last occurrence, but it’s hard to use it to replace instances of a substring in the middle of a string.

That’s where this ReplaceN function comes in handy! This UDF replaces the Nth occurrence of a substring in a string with VBA. It combines the Mid, InStr and Replace string manipulation functions to create one handy function. Use it to replace the 2nd, 3rd, 4th or more instance of a string within another string.

Replace Nth Occurrence of substring in a string

Function ReplaceN(ByVal str1 As Variant, strFind As String, strReplace As String, N As Long, Optional Count As Long) As String
Dim i As Long, j As Long
Dim strM As String
strM = str1
If Count <= 0 Then Count = 1
For i = 1 To N - 1
    j = InStr(1, strM, strFind)
    strM = Mid(strM, j + Len(strFind), Len(strM))
Next i
If N <= 0 Then
    ReplaceN = str1
    ReplaceN = Mid(str1, 1, Len(str1) - Len(strM)) & Replace(strM, strFind, strReplace, Start:=1, Count:=Count)
End If
End Function

How to use the ReplaceN Function

Here’s an example of how to use the ReplaceN function:

Replace 2nd occurrence of substring in a string

Sub VBA_Replace1()
str1 = "One fish, two fish, red fish, blue fish"
str1 = ReplaceN(str1, "fish", "cat", 2)
End Sub

This macro replaces the 2nd instance of “fish” in a string with the word “cat.” It changes the string from

One fish, two fish, red fish, blue fish


One fish, two cat, red fish, blue fish

Use ReplaceN in an Excel Spreadsheet Cell

You can also use the ReplaceN function directly in your spreadsheet by entering it in a cell in Excel. Just type a formula like =ReplaceN(A1,"fish","cat",2) into any cell and watch it work its magic.

Replace middle 2 instances of a string starting with the 2nd occurrence

Notice I included an optional argument titled Count. This can be any positive integer. Unlike the normal VBA Replace function, the default of my function is to just replace 1 occurrence. Whatever number you put here, the function will replace that many of your substrings beginning with the Nth instance you defined in variable N.

For example, if you put

Sub VBA_Replace1()
str1 = "One fish, two fish, red fish, blue fish"
str1 = ReplaceN(str1, "fish", "cat", 2, 2)
End Sub

str1 would be changed to

One fish, two cat, red cat, blue fish

Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too

Your time is valuable. It's time to become a VBA expert.

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.