Ampersand and Commas causing problems with LeftWords, etc

[philipcaplan]

I have a Calculation field containing text such as "Cats & Dogs" and "One, Two, Three"

I need to replace all occurrences of 'space' in every occurrence in this field with a string (' ' as it happens) but when I use functions such as LeftWords or PatternCount in doing this, the '&' is ignored, and the 'commas' in One, Two, Three get stripped out.

I presume that FMPro is treating anything non-alphanumeric as 'whitespace'.

What is the most elegant solution to this problem?

Using FMPro 8.5, and not planning to upgrade in the very near future...

[comment]

I need to replace all occurrences of 'space' in every occurrence in this field with a string

Why not use the Substitute() function?

I presume that FMPro is treating anything non-alphanumeric as 'whitespace'.

That is an incorrect assumption. Some characters are word delimiters, some are not. And some characters are word delimiters only under certain circumstances.

[philipcaplan]

Thanks! I took a wrong turn by choosing to use PatternCount( ) rather than Susbtitute( ).

It hadn't seemed obvious to me that Susbtitute( ) worked on ALL occurrences, and it happily handles & and ,

Thanks for your help. My problem now seems to be solved. :)