Need help getting this calc to work: replace every other comma with a pilcrow

[Rich S]

Hi there:

I'm close—I just can't figure out what I need to get this calc to work:

Attached is a screenshot of the error message (from the DataViewer) and of course, the file; the calc is "remarked out" so I could preserve it and close the editing window.

I'd sure appreciate the help. Thanks!

Temp.fmp12

[comment]

Try it this way?

While ( [ 
result = YourField ;
n = PatternCount ( result ; ", " ) 
] ; 
n ; 
[ 
pos = Position ( result ; ", " ; 1 ; n ) ;
result = If ( Mod ( n ; 2 ) ; result ; Replace ( result ; pos ; 2 ; ¶ ) ) ;
n = n - 1
] ; 
result
)

This is assuming the input is consistently separated by a comma followed by a space.

 

Here is another way you could look at it, which I think is closer to your attempt:

While ( [ 
values = Substitute ( Yourfield ; ", " ; ¶ ) ;
n = ValueCount ( values ) ; 
result = "" ;
i = 1
] ; 
i < n ; 
[ 
value = GetValue ( values ; i ) & ", " & GetValue ( values ; i + 1 ) ;
result = List ( result ; value ) ;
i = i + 2
] ; 
result
)

This could be streamlined to:

While ( [ 
values = Substitute ( Yourfield ; ", " ; ¶ ) ;
n = ValueCount ( values ) ; 
result = "" 
] ; 
n ; 
[ 
value = GetValue ( values ; n - 1 ) & ", " & GetValue ( values ; n ) ;
result = List ( value ; result ) ;
n = n - 2
] ; 
result
)

 

Edited 17 hours ago by comment

[comment]

Just for fun, you could also do:

While ( [ 
values = Substitute ( Yourfield ; ", " ; ¶ ) ;
n = ValueCount ( values ) ; 
result = "" 
] ; 
n ; 
[ 
result = GetValue ( values ; n ) & Choose ( Mod ( n ; 2 ) ; ¶ ; ", " ) & result ;
n = n - 1
] ; 
result
)

But this one will have a trailing carriage return, unless you change the output line to something like:

Left ( result ; Length ( result ) - 1 )

or:

Substitute ( result & ¶ ; "¶¶" ; "" )

 

Edited 15 hours ago by comment

[Rich S]

Beauty! Thanks!!