Removing unwanted characters from a string 5

[JoanieB]

Occasionally I run into single quotes in a string of data I am using. I don't know where or when they will occur-if at all. How can I get them out?!

 

[jebry]

Hi!

Try using the following function:

Public Function ExtractCharacter(WrongString As String, BadChar As String) As String
'This function will extract the all occurances of the character sent

'Declare local variables
Dim ClearedString As String
Dim CharPos As Long

'Main Loop
ClearedString = WrongString
CharPos = InStr(ClearedString, BadChar)
Do Until CharPos = 0
ClearedString = Left$(ClearedString, CharPos - 1) & Mid$(ClearedString, CharPos + 1)
CharPos = InStr(ClearedString, BadChar)
Loop

'Set the function
ExtractCharacter = ClearedString

End Function

Where WrongString is the string with the superflorous characters and BadChar is the character you want removed.

hth
Jeff Bridgham

 

[Scoty]

Nice one Jeff
Here's your Star

Scoty : Lean from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[JoeMiller]

Here's another one that allows you to look at a string and find multiple or single characters and then replace if found with multiple or single characters. I know this has been answered already above, but someone else may want this variation:

[tt]
Function fstrTran(ByVal sInString As String, _
sFindString As String, _
sReplaceString As String) As String
Dim iSpot As Integer, iCtr As Integer
Dim iCount As Integer

iCount = Len(sInString)
For iCtr = 1 To iCount
iSpot = InStr(1, sInString, sFindString)
If iSpot > 0 Then
sInString = Left(sInString, iSpot - 1) & _
sReplaceString & _
Mid(sInString, iSpot + Len(sFindString))
Else
Exit For
End If
Next
fstrTran = sInString

End Function
[/tt]

HTH Joe Miller
joe.miller@flotech.net

 

[Sameal]

Take a look at Replace(). I had a problem with single apostrophe's in SQL statements.

strSQL = Replace(strMyString, "'", "''&quot

I don't know about your particular situation but Access reads two apostrophe's as one so having it replace all occurances of "'" with "''" works great.