Stripping out ALL but Letters, Numbers and Spaces

[rich graham]

I import a text file that has a product name field with content like this:

 

16.9oz. Oil/Water Set/2

Blue-Widget/Red-Widget™

7CY-QUARTER-A/F-Ajax©

Michael@Home/Black Artwork

 

**these are non-sensical on purpose

 

I'm trying to make all of these lower case, stripping out all of the funky characters and replacing them with single hyphens

 

16-9oz-oil-water-set-2

blue-widget-red-widget

7cy-quarter-a-f-ajax

michael-home-black-artwork

 

I've studied this page all afternoon, and deep into the night. 

 

http://help.filemaker.com/app/answers/detail/a_id/4888/~/tip%3A-removing-non-numeric-characters

 

My eyeballs are crossed. Does anybody know how to do this?

[doughemi]

Lower(Substitute(YourTable::YourField; ["."; "-"]; ["/";  "-"]; ["@"; "-"]; ["™";  "-"]; ["©"; "-"]))

 

The fact that you wish to replace the odd characters with hyphens precludes the use of the Filter() function.  Filter() merely eliminates ANY character that is not in the filter list.

[comment]

I'm trying to make all of these lower case, stripping out all of the funky characters and replacing them with single hyphens

 

You would need a custom recursive function for the last part. And you would need the Advanced version of the application in order to install it.

Alternatively, you could script the process, and apply the recursion through a looping script.

 

Please update your profile to reflect your version and OS.

[Lee Smith]

As comment requested, please change your profile to reflect your current information.

 

Here is a quick link for for you convience. MY PROFILE

[Josh Ormond]

Would you be able to replace the special characters with spaces, then use TrimAll, then substitute "-" for spaces?

 

You would need a custom recursive function for the last part. And you would need the Advanced version of the application in order to install it.

Alternatively, you could script the process, and apply the recursion through a looping script.

 

Please update your profile to reflect your version and OS.

[comment]

Would you be able to replace the special characters with spaces, then use TrimAll, then substitute "-" for spaces?

 

Yes, of course. But the first part - "replace the special characters with spaces" - is non-trivial, because there is no known list of the "special characters". The only known list is the one of allowed characters. Therefore you must either:

 

• Substitute out all allowed characters to obtain a list of "special characters", which you can then use to substitute each one of these with a space;

 

or:

 

• Process the given text character-by-character, comparing each character to the given list of allowed characters, and write out either the character or a space to the output.

 

I believe that with relatively short texts such as the ones shown, the second approach would be preferable - but both require recursion/iteration.

[Josh Ormond]

Gotcha. I thought I remembered seeing a calc somewhere that did something similar. So I went looking, and it was a custom function that I had seen.