removing extra characters

[e3digital]

I have imported about 1000 records from an Excel spreadsheet. One of the fields is CSZ (city state zip)...when the data came in, it was enclosed in quotation marks, which I don't want. I need to know 1 or both of these things:

A. where did they come from?

B. how do I get rid of them?

I'm sure there is a script or combination of functions/calculations I could write to remove the first and last characters in the field, but nothing I've tried so far seems to work...There is some duplication, but not every CSZ is the same length, which complicates it for me...

Any help would really save me a lot of piddly deleting...

Thanks!

Susan

[slstrother]

The quotation marks are normal for a comma delimited file. They should have been removed when the data was imported. Since they were not, you can remove them using Replace and the Substitute function.

Make a copy of your data base in case something goes wrong.

Show all records, place your cursor in CSZ field of the first record, select Record>Replace, select Replace with calculated result, enter the following calc:

Substitute(CSZ,""","")

Hit OK, Hit Replace.

[e3digital]

Thank you!!

That is so cool...why does it work?

[jasonwood]

The syntax is like this...

Substitute (fieldname, "what to look for", "what to replace it with")

In your case you are replacing a quotation mark with nothing.

[CobaltSky]

As you may by now have discovered, entering the formula as suggested above will generate a syntax error and will not permit you to proceed with the 'Replace Contents' command. That's because the quote inside the quotes is interpreted by FileMaker as being the end of what you're looking for to substitute, so the quote after that is interpreted as misplaced (because a comma is expected there).

However there is a way around the problem. The special syntax for specifying a literal quote within a calculation formula, is a sequence of four quotes, not three. If you try the formula as:

Substitute(CSZ,"""","")

(note the extra quote character ) it will work correctly. Strange though it may seem

[rangebob]

What if I have the same type of problems but at the end of my data I have a , How do I get rid of that?

[andygaunt]

Exactly the same way (as long as there are meant to be no comma's in your data) you can do

Substitute(Your Field,",","")

If however you do have comma's in the field, and the one you want to remove is the last character.

Substitute(Your Field, Right(Your Field, 1), "")

Always practice on a backup or just a single record.

NEVER practice on ALL your data.

[rangebob]

Do you have a recomendation on a book that I can pick up on how to use some of these features in FileMaker Pro? Thanks for your help.

[jasonwood]

I find the FileMaker help files most useful for learning individual functions. Books are better for concepts. There is a separate forum for "FileMaker Resources". Have a look there if you're looking for books.