Search code examples
stringvbaexcelreplace

Replace only last occurrence of match in a string in VBA

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)

Solution

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