gosportlad
asked on
Find and Replace Solution??????
I want to find a string at a predetermined location (say 15)in a record,
grab that string length (say 35) and replace it with a string.
Thanks Fred
grab that string length (say 35) and replace it with a string.
Thanks Fred
ASKER
Thanks for your response, but I want to tell the function where the string is located (to validate) within each record.
Regards Fred
Regards Fred
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Function ReplaceString(sSearch As String, sSearchFor As String, sReplaceWith As String) As String
' *** Searches the sSearch variable for sSearchFor
' *** and replaces it with sReplaceWith.
On Error GoTo ERROR__ReplaceString
Dim lFoundLoc As Long ' Location of match.
Dim lLenRemove As Long ' Length of string being replaced.
' *** Set length of original text to skip.
lLenRemove = Len(sSearchFor) ' Set location of match.
lFoundLoc = InStr(1, sSearch, sSearchFor)
' If sSearchFor isn't found in sSearch
' just return the original string.
If lFoundLoc = 0 Then
ReplaceString = sSearch
' If match is found, return original string up to match
' location, concatenate new text, and search the rest of
' the string recursively for additional matches.
Else
ReplaceString = Left(sSearch, lFoundLoc - 1) & sReplaceWith & ReplaceString(Mid(sSearch,
End If
EXIT_ReplaceString:
Exit Function
ERROR__ReplaceString:
' Print error to Debug window and don't interrupt query.
Debug.Print "Error Replacing String """ & sSearchFor & """ with """ & sReplaceWith & """ in text """ & sSearch & """"
' If there is an error, return original string
' and exit the function.
ReplaceString = sSearch
Resume EXIT_ReplaceString
End Function