Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace character in VBA Excel code 1

Status
Not open for further replies.

UnsolvedCoding

Technical User
Jul 20, 2011
424
US
I want to replace all ' in the string with nothing so that when I load it into SQL I don't keep running into problems. The code given works to replace only one ' and then stops looking.

I have been looking for awhile and can't find any code samples to say how to do this and I know it can be done.

My code -
sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")

How do I replace all ' in the string?
 
Code:
do while instr(1,sCOMMENT_TEXT,"'")>0
    sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")
loop

_________________
Bob Rashkin
 

Your code should work like you had it in the first place, replacing all single quotes:
Code:
sCOMMENT_TEXT = "This is ' some ' text with '' some ' quotes"

sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")

Debug.Print sCOMMENT_TEXT[blue]
This is  some  text with  some  quotes[/blue]
So I don't know what you mean by: "The code given works to replace only one ' and then stops looking"

Have fun.

---- Andy
 
The proper way to deal with single quotes in SQL is to replace each single quote with 2 single quotes

e.g.
Code:
sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "''")
This way, if you enter "Bob O'Brien" into a record, you won't end up with "Bob OBrien".
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top