Deleting commas within quotes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RJF - 22 Apr 2008 16:26 GMT

I have a table that contains one field. I will be breaking this field into
several other fields by replacing the commas with semi-colons and then
exporting the table to a text document.

The problem I'm having is when there are any commas within quotes, as shown
below. How do I remove these commas before I replace all the other commas
with semi-colons?

Below is a sample row containing the extra commas. There are three fields
here.

333371,"http://maps.google.com/maps?+Broadway,+Providence,+RI+",JRaposo,

The row should look like this before I replace the commas with semi-colons.

333371,"http://maps.google.com/maps?+Broadway+Providence+RI+",JRaposo,

I know it probably doesn't make sense that I'm changing the commas to
semi-colons. It was an attempt to solve the commas w/in quotes problem (long
story). I could remove the step of replacing the commas with the
semi-colons, if I could just remove the commas between the quotes.

I hope I've made that clear enough. Any help would be so much appreciated.

Thank you,

Signature

RJF

Reply to this Message

KARL DEWEY - 22 Apr 2008 20:30 GMT

It appears that the unwanted commas are always followed by a plus sign so
then do this --
Replace([Yourfield], ",+", "+")
Then to replace the other commas with semicolons use this --
Replace(Replace([Yourfield], ",+", "+"), ",", ";")

Signature

KARL DEWEY
Build a little - Test a little

> I have a table that contains one field. I will be breaking this field into
> several other fields by replacing the commas with semi-colons and then
[quoted text clipped - 21 lines]
>
> Thank you,

Reply to this Message

RJF - 22 Apr 2008 20:43 GMT

Hi Karl,

No, they aren't always followed by a plus sign. I just happened to grab
that line as an example.

I think I just got it working. It appears to be anyway.

I created an update query where I'm updating the 2nd field with the quotes
around it. I'm updating field1 with the following:

Left([field1],InStr(1,[field1],Chr(34))-1) &
replace(Mid([field1],InStr(1,[field1],Chr(34)),InStrrev([field1],Chr(34))-7),","," ") & Mid([field1],InStrrev([field1],Chr(34))+1,1000)

Oh yeah, the quote field will always start at position 8.

I know there must be a much, much easier way. If you can think of anything,
please let me know. If not, I'll just go with what I have.

Thanks so much for the quick reply.

Signature

RJF

> It appears that the unwanted commas are always followed by a plus sign so
> then do this --
[quoted text clipped - 27 lines]
> >
> > Thank you,

Reply to this Message