Replace the last occurrence of a string

Replace the last occurrence of a string

Here is a one-liner that replaces the last occurrence of a substring in a string. It is slightly less efficient than using the InstrRev plus a Replace statement, but at least you can use it in-line inside another expression. And it is also one of the few occasions to use the StrReverse function, undoubtedly the least useful addition to the VBA language in years:

' replace only the LAST occurrence of a the SEARCH substring in SOURCE ' with the REPLACESTR string - search isn't case-sensitiveReplaceLast = StrReverse(Replace(StrReverse(Source), StrReverse(Search), _    StrReverse(ReplaceStr), , 1))


Share the Post: