another string value.
Option Public
Option Declare
Public Function replaceSubstring( Byval strSource As String, strFrom As String,
strTo As String ) As String
'strSource represents your string
'strFrom is the substring that will be replaced by strTo
'
'Syntax: ReplaceSubString( "UNC Mens Basketball", "Mens", "Womens" )
'Variable used to indicate position of occurance of strFrom in strSource
Dim intPosition As Integer
'Variable used to indicate start position for Mid()
Dim intStart As Integer
'Variable used to hold length of strSource
Dim intLengthSource As Integer
'Variable used to hold length of strFrom
Dim intLengthFrom As Integer
'Variable used to hold length of strTo
Dim intLengthTo As Integer
'Get length of "source" string
intLengthSource = Len( strSource )
'Get length of "from" string
intLengthFrom = Len( strFrom )
'Get length of "to" string
intLengthTo = Len( strTo )
'Set start position in string to first character
intStart = 1
'Check for an occurance of strFrom
If Not ( Instr( 1, strSource, strFrom ) = 0 ) Then
'Substring found proceed ...
'Continue as long as strFrom is encountered in strSource
Do While Not ( Instr( intStart, strSource, strFrom ) = 0 )
'In each loop, when strFrom is encountered, set strSource equal
'to characters before strFrom + strTo + characters after strFrom
'Get position of occurance of strFrom in strSource
intPosition = Instr( intStart, strSource, strFrom )
'replace strFrom w/ strTo
strSource = Mid( strSource, 1, intPosition - 1 ) & strTo & _
Mid( strSource, intPosition + intLengthFrom, intLengthSource )
'Change start so we advance in strSource string
intStart = intPosition + intLengthTo
Loop 'Not ( InStr( intStart, strSource, strFrom ) = 0 )
End If 'Not ( InStr( 1, strSource, strFrom ) = 0 ) Then
'Return value
ReplaceSubString = strSource
End Function
This was first published in November 2000