Remove leading, trailing and double spacing in an address field.

[AbsoluteVoice4u]

Here is my concern. I have an address field with house number, street name and street type, like this: 100 Main St.

Sometimes, when I import addresses, there are spaces at the beginning or at the end of the address, as well as more than one space between the street number and the street name. A field calculation seems required.

What's the best calculation to have these extra spaces removed, yet leave ONE space between house number, street name and ST, Ave, Lane, etc. ? It would be great to also remove any "periods". A field calculation seems required for my auto enter and manual enter, to correct all.

 

I'm thinking the same calculation could be used for my field collectively housing the first and last name as well?

 

Many attempts to resolve this on my end have failed. Any thoughts or maybe a solution?

 

Any help is appreciated greatly.

 

[Lee Smith]

Have you looked at the custom functions at Brian Dunning's site? I believe that the Trim4 does what you want.

[doughemi]

Use the TrimAll () and Substitute() functions:

 

Let(~trimmed = TrimAll(AddressField; 1; 2);

Substitute(~trimmed; "."; "")

)

 

See FileMaker's help page for definitions of the parameters.

[AbsoluteVoice4u]

Thank you Lee and doughemi.

I did check Brian's Trim4.:

Let(~trimmed = TrimAll(AddressField; 1; 2);

Substitute(~trimmed; "."; "")

)

 

Did not do the trick........doughemi's solution is now working fluidly!!!

 

Thank you both again.....This is one AWESOME FM Forum!

[LaRetta]

Hey Doug, just curious why you used:  

 

TrimAll(AddressField; 1; 2)

 

Being Roman, why not use 0 and 0?

[doughemi]

Hey Doug, just curious why you used:  

 

TrimAll(AddressField; 1; 2)

 

Being Roman, why not use 0 and 0?

 

ummm..because I didn't read the Filemaker 12 Developer Reference explanation closely enough?