I am hoping someone can spot the illogic ... this CF (file attached) seems to work fine except if I put a non-word character at the front of the text. There are surely better ways to write this calculation but I am puzzled why this can recurse itself to death (apparently) only with beginning non-word character.

Purpose produce 1 of every word in any number of strings, separated by a space. Each word should be individual (from both lists) but only replace full-word matches.

I have calculation (result is text): DeDupWords ( Category & " " & SubCategory )

DeDupWords ( text )

Let ( [

string = " " & Substitute ( Proper ( text ) ; [ "," ; " "] ; [ "-" ; " " ] ) & " " ;

word = " " & LeftWords ( Proper ( string ) ; 1 ) & " " ;

remainder = Substitute ( string ; word ; " " ) ;

kount = WordCount ( remainder )

] ;

LeftWords ( string ; 1 )

&

Case (

kount ; " " & DeDupWords ( remainder ) )

)

You can safely open the file and see that it works. You can add regular words to either list and it adjusts fine. But if you type a non-word character at the beginning of either Category or SubCategory fields, it will run forever. Is it because I am running it again, using the remainder from the Let()? I can't imagine that would do it but I can't imagine it crashing either. I have changed the calc since I posted it originally on another thread so the two people who downloaded it before I pulled it might want this one instead; it works well (except for the crashing, that is, LOL).

If someone can spot what is wrong in my calc to allow this type of runaway, I would sure appreciate the ideas. :-)

DeDupTEST2.zip

I think it's because the $rock never gets substituted.

I took your function and made it non-recursive and returned the first result and what gets fed into the next recursion:

Let ( [

text = "$rock soul, bLUES, reggae" ;

string = " " & Substitute ( Proper ( text ) ; [ "," ; " "] ; [ "-" ; " " ] ) & " " ;

word = " " & LeftWords ( Proper ( string ) ; 1 ) & " " ;

remainder = Substitute ( string ; word ; " " ) ;

kount = WordCount ( remainder ) ;

leftW = LeftWords ( string ; 1 ) ;

remainder = " " & Remainder

result = leftW & ¶ & remainder

] ;

result

)

result =

Rock

$Rock Soul Blues Reggae

Basically the problem is the Substitute() in the remainder variable. You're really saying Substitute ( "$rock soul, bLUES, reggae" ; " rock" ; " " ). But $rock won't be affected by [space]rock.

How to fix it? Depends. Do you ever want to return special characters like $ or #? If not, Filter() the starting text down to just alpha and spaces. If not, I'd go with substituting all the spaces for pilcrows and using GetValue() instead of LeftWords() and the custom function AntiFilterValues ( or Substitute( ¶ & string & ¶ ; ¶ & value & ¶ ; "" ) and dealing with empty values.