Trim functions

[Bruce105]

Hi, really appreciate any help. I'm using FM version 9 and have built a calculation field that I would like to show addresses without any spaces.

The original data was imported from various excel files. I also have a field that shows concatenated addresses..and they look identical, but when I look at the calculated field, some of those same addresses have spaces between the city and zip code etc....but not all.

My goal is use duplicate find, so I can remove duplicate addresses. I tried on the concatenated field, but the return was terrible...so I thought I'd build a calculated field to resolve unknown differences...but these spaces show up..how do I apply the trim function to the formula so that the spaces go away?

 

Many, many thanks, Bruce

[AHunter3]

ALL spaces? So you'd end up with LosAngelesCA90150?

 

Substitute (addressfield, " ", "")

[HBMarlowe]

Can you post a sample of the data that you want to manipulate, and the desired result? Then others can help you get there.

[Bruce105]

Hi, and thanks for the help.

 

I have an address field that has addresses that look like:

1234 Sunset Blvd.

1234 Sunset Blvd.

1234 Sunset Blvd.

I'd like to delete all of these duplicates (note; they come from different excel files)....but when I do a find, I only get two of the three results.

 

So....I thought I'd create a calculation field (without spaces would be fine), to get rid of the unseen gremlins, and I get:

1234SunsetBlvd.

1234SunsetBlvd.

1234Sunset Blvd.

 

I'd sure like to get rid of the space, so the duplicate request can recognize all three as the same. Which got me to the trim idea, but unsure how to apply it to the calculation....Bruce

Any ideas

[HBMarlowe]

The Trim function removes leading and trailing spaces, so " trim this " becomes "trim this"

 

That doesn't help with your examples, but the Substitute function mentioned by AHunter3 does what you need. That still won't allow your search for duplicates to find Boulevard vs. Blvd. or Avenue vs. Ave or Ave. You may have to do some further Substituting.