replace space character with underscore

[n8kindt]

hey guys, i have a function that can recognize a space character in a string... how can i get it to replace a space with a underscore character

Public Function RemoveSpaces(strInput As String)
' Identifies spaces in a string of text
   If InStr(txtEmail, " ") = 0 Then
      msgbox "Spaces are not allowed)
   Else: Exit Function
End Function

[Stewart Ross]

Hi Nate. The Replace function does this in one call. It is used like this:

somestring = Replace (strInput, strFind, strReplace)

In your function you are mistakenly referring to an undeclared variable txtEmail in place of strInput. I correct this below, and change the name of the function slightly to reflect what it does.

Public Function ReplaceSpaces(strInput As String) as String
' Replaces spaces in a string of text with underscores
Dim Result as String
Result = strInput
If InStr(strInput, " ") = 0 Then
	Result = Replace(strInput, " ", "_")
	msgbox "Spaces have been replaced with underscores.")
Endif
ReplaceSpaces = Result
End Function

-Stewart

[n8kindt]

Hi Nate. The Replace function does this in one call. It is used like this:

somestring = Replace (strInput, strFind, strReplace)

In your function you are mistakenly referring to an undeclared variable txtEmail in place of strInput. I correct this below, and change the name of the function slightly to reflect what it does.

Public Function ReplaceSpaces(strInput As String) as String
' Replaces spaces in a string of text with underscores
Dim Result as String
Result = strInput
If InStr(strInput, " ") = 0 Then
	Result = Replace(strInput, " ", "_")
	msgbox "Spaces have been replaced with underscores.")
Endif
ReplaceSpaces = Result
End Function

-Stewart

perfect. thank you for that. that helped me out a lot. i'm always amazed i can't see the forest for the trees when looking for functions. could the name replace be any more obvious? lol

anyways, for future reference, here is my final code that is error free. i also added similar exception for apostrophes.

Public Function ReplaceSpaces(strInput As String) As String
' Replaces spaces in a string of text with underscores
Dim Result As String
Result = Nz(strInput, "")
If InStr(strInput, " ") > 0 Then
	Result = Replace(strInput, " ", "_")
End If
' Deletes apostrophes
If InStr(strInput, "'") > 0 Then
	Result = Replace(strInput, "'", "")
	
End If
ReplaceSpaces = Result
Debug.Print Result
End Function