Feel free to incorporate the following code in your own module. I would appreciate it though if you don't change it in any way - without letting me know.
Call it as follows.
NewString=fx_ReplaceChar(CurrentString," ",","
'
'=====================================================================
'
' Function Name : fx_ReplaceChar
'
' Description : Replace all occurrances of a substring in a string with another substring.
'
' Input Parameters : Text$ - String in which to substitute substring
' : TextToFind$ - Substring to be replaced
' : TextToReplace$ - Substring to replace
'
' Return Value : Substituted String
'
' Calls : Mid$
'
'---------------------------------------------------------------------
'
' Author: : Stephen J Duckworth
' Date: : May 1999
' Company : Bournville Consultants Limited
' Email : Support@Bville.clara.net
'
'---------------------------------------------------------------------
'
' Change History:
'
' Version Date Author Description
' --------------------------------------------------------------------
' 1.0 07/05/99 SJD Initial version
' 2.0 20/06/02 SJD Remove length restrictions
'=====================================================================
'
Function fx_ReplaceChar(Text$, TextToFind$, TextToReplace$) As String
Dim LT As Long, LR As Long, LF As Long
Dim PF As Long, TS$
On Error GoTo err_fx_ReplaceChar
LT = Len(Text$)
LR = Len(TextToReplace$)
LF = Len(TextToFind$)
TS$ = Text$
If TextToFind$ = TextToReplace$ Then GoTo exit_fx_ReplaceChar
Do While InStr(Text$, TextToFind$) > 0
PF = InStr(Text$, TextToFind$)
Text$ = Mid(TS$, 1, PF - 1) & TextToReplace$ & Mid(TS$, PF + LF, Len(TS$))
LT = Len(Text$)
LR = Len(TextToReplace$)
LF = Len(TextToFind$)
TS$ = Text$
Loop
exit_fx_ReplaceChar:
fx_ReplaceChar = Text$
Exit Function
err_fx_ReplaceChar:
Resume exit_fx_ReplaceChar
End Function
