This function does a simple text search and replace, no matter how many occurrences of the search string.
When LS4 came out with R5, I created a function using the new StrLeft and StrRight functions along with Instr which was used only as a "Contains" type of check. I had a rude surprise the first time I tried to search for "&" and replace it with the Web equivalent of "&a-m-p" (dashes added to prevent passthru). I sent the agent into an endless loop! Not very bright. So, I went back to the drawing board and rebuilt it so that it now works its way down the string and picks up immediately after the last replacement.
The beauty of this one is that it's backwards compatible with R4 and you can include the search string as part of the replacement string without worry of ending up in an endless loop! For example, you can replace "&" with the Web equivalent of "&a-m-p" (dashes added to prevent passthru).
Function searchReplace(startString As String, searchString As String, replaceString As String) As String %REM Function searchReplace Charles F. Phillips 23 May 2001 This function does a simple text search and replace, no matter how many occurances of the search string. The beauty of this one is that it's backwards compatible with R4 and you CAN include the search string as part of the replacement string without worry of ending up in an endless loop! i.e., you can replace "&" with "&" %END REM 'just in case a non-string value was passed On Error Resume Next 'dim strings used to reassemble final product Dim sPart1 As String Dim sPart2 As String 'dim iPos for use with Instr Dim iPos As Integer 'dump incoming string into searchReplace in order to 'fulfill function's output even if searchstring isn't found searchReplace = startString 'if startStrng or searchString were empty, then exit If searchReplace ="" Or searchString ="" Then Exit Function 'Use Instr to determine if startString actually contains searchstring. 'We will care where it actually is located, but it's also a quick "Contains" type of check. iPos = Instr(searchReplace, searchString) 'If Contains, then iPos will be a value higher than zero and the Do loop executes, 'but if not contains, then a zero is returned and we never enter Do loop. Do Until iPos = 0 'Get everything to the left of the startstring 'iPos minus 1 will be all characters to the left of, but not including, searchstring sPart1 = Left$(searchReplace, iPos-1) 'Get number of characters to the right of the searchstring: 'Total length of the string minus ((iPos-1) plus length of searchstring) sPart2 = Right$(searchReplace, Len(searchReplace)-((iPos-1)+Len(searchString))) 'Reassemble searchReplace with parts 1 and 2 surrounding the replacement value searchReplace = sPart1 & replaceString & sPart2 'Check to see if there is another occurance of the searchString, 'and if so, keep the loop going until no more occurances are found. 'Start looking after the last occurrance, plus the length of the replaceString 'in order to prevent something like search for "&" and replace with "&" from going into an infinite loop iPos = Instr((iPos-1)+Len(replaceString), searchReplace, searchString) Loop End Function
This was first published in June 2001