Embedded Quotes

What is the best way to get rid of embedded quotes as in
Chain 3.75" Pitch 3/8" Diameter.
I just want to eliminate the " or CHR(34) and leave nothing.
I import several thousand rows and 15-20 have embedded quotes in them. I want a function to clean these out.

Thanks
Paul
PaulCr125Asked:
Who is Participating?
 
Dave_GreeneConnect With a Mentor Commented:
You need to put the text in a variable...  the function doesn't know how to react to the text you have entered

Try this

StringX = Chr(34) & "Chain 3.75" & Chr(34) & " Pitch 3/8" & Chr(34) & " Diameter" & Chr(34)

Then

Replace(StringX, Chr(34), "")


0
 
Dave_GreeneCommented:
StrResult = Replace(StringIN, Char(34), "")
0
 
PaulCr125Author Commented:
Dave
I'm Access 97 no replace function.
But I do have VB6 and when I run
Replace("Chain 3.75" Pitch 3/8" Diameter",Chr(34),"")
... I get an expected list seperator or ) error

I appreciate your help
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rspahitzCommented:
Dave's right--you can't embed a quoted string into VB as though it's coming from a database and assume the syntax is correct!

My only recommendation, for clarity, is to replace the "constants":

Const DoubleQuote as string = """" ' chr$(34)

OriginalString = "Chain 3.75" & DoubleQuote & " Pitch 3/8" & DoubleQuote & " Diameter '---> Chain 3.75" Pitch 3/8" Diameter

NewString = Replace(OriginalString, DoubleQuote, vbNullString)

Also, if you want to do this in Access97, use the same code, but create your own replace function:

Function Replace(OriginalString, SearchString, ReplaceString) as String
  ' Note that this will not work if ReplaceString is a substring of SearchString

  dim strTemp as string
  dim iFoundPosit as integer

  strTemp = OriginalString

  do
    iFoundPosit = instr(strTemp, SearchString)
    if iFoundPosit = 0 then exit do
    strTemp = left$(strTemp, iFoundPosit-1) _
            & ReplaceString _
            & mid$(strTemp, iFoundPosit+len(SearchString)+1)
  loop

  Replace = strTemp
end Function
0
 
PaulCr125Author Commented:
Thanks Dave,
   I guess this errant embedded quote is a bigger problem than I thought.
I am parsing a database so I'm out of luck to do it in a function?

Paul
0
 
PaulCr125Author Commented:
Thanks Dave,
   I guess this errant embedded quote is a bigger problem than I thought.
I am parsing a database so I'm out of luck to do it in a function?

Paul
0
All Courses

From novice to tech pro — start learning today.