Replace only last occurrence of match in a string

Question

I have a string like this

"C://Documents/TestUser/WWW/Help/Files/Move_Help.txt"

and have to replace Move_Help.txt with Move_Job.txt

I am using the below code in VBA EXCEL

str = "C://Documents/TestUser/WWW/Help/Files/Move_Help.txt"
rlpStr = Replace(str, 'Help', 'Job')

I am getting

"C://Documents/TestUser/WWW/Job/Files/Move_Job.txt"

Expected

"C://Documents/TestUser/WWW/Help/Files/Move_Job.txt"

Can you please help on this.

FYI : I can't match Move_Help to Move_Job (Move_ is not constant. It can be any string)

Answer 1

There's a one-line solution for this:

rlpStr = StrReverse(Replace(StrReverse(str), StrReverse("Help"),  StrReverse("Job"), , 1))

Technically, it's slightly less efficient than combining InStr and Replace but it can be used inside another expression if you need to. Also, I like the one-line solutions so long as they're not incomprehensible.

Answer 2

Would the technique in the code below meet your requirement?

The intial value of Str is:

 C://Documents/TestUser/WWW/Help/Files/Move_Help.txt

The final value is:

 C://Documents/TestUser/WWW/Help/Files/Move_Job.txt

The code uses InStrRev to locate the last occurrence of ValueCrnt, if any, If ValueCrnt is present, it replaces that final occurrence with ValueNew.

Option Explicit
Sub Demo()

  Dim Pos As Long
  Dim Str As String
  Dim ValueCrnt As String
  Dim ValueNew As String

  Str = "C://Documents/TestUser/WWW/Help/Files/Move_Help.txt"

  ValueCrnt = "Help"
  ValueNew = "Job"

  Pos = InStrRev(Str, ValueCrnt)

  If Pos > 0 Then
    Str = Mid(Str, 1, Pos - 1) & Replace(Str, ValueCrnt, ValueNew, Pos)
  End If

  Debug.Print Str

End Sub