Menu
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)
957
1 Replace is a string function family in VBA. 2 In VBA, the replace function replaces all the supplied words with replaced string if the count parameter is not specified. 3 The start parameter will delete the number of characters supplied and show the remaining result.
public static string ReplaceLastOccurrence (string Source, string Find, string Replace) { int place = Source.LastIndexOf (Find); if (place == -1) return Source; string result = Source.Remove (place, Find.Length).Insert (place, Replace); return result; } Source is the string on which you want to do the operation.
The code uses InStrRev to locate the last occurrence of ValueCrnt, if any, If ValueCrnt is present, it replaces that final occurrence with ValueNew. Show activity on this post. Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question.
For the second method, we’re going to use the MATCH function, the SEQUENCE function, the MID function, and the LEN function to find the position of the last occurrence of a character in the string. Remember the SEQUENCE function is only available on Excel 365 or Excel 2021.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
