Are all mediations single words or are some of the mediation names more than one word separated by a space?
If they are single words in every single case:
Substitute ( Medications; ["¶" ; "; "] ; [" " ; "" ] )
If there are cases where a medication name contains a space that you must not remove:
Substitute ( TrimAll ( Medications ; 0 ; 0 ); "¶" ; "; " )
Note how with both methods you "nest" one function inside another--a key method for combining functions into one calculation.
I made a separate calculation field just to see what it would look like, doesn't seem to be affecting the spaces. I tried changing the zeros....didn't have any effect. Not sure where to go from here.
This suggests that those "spaces" might not be actual space characters, but some other non printing character.
In my tests.
Substitute ( TrimAll ( Table::TextField ; 0 ; 1 ) ; ¶; "; " )
"value 1"; "value 2"
But there are other characters that look like spaces but aren't. If you can determine their code values, you can use additional bracket substitution pairs to eliminate them using the Char function.
Substitute ( TrimAll ( Table::TextField ; 0 ; 1 ) ; [ ¶; "; "] ; [Char ( 9 ) ; "" ] )
Char ( 9 ), BTW, returns the tab character, one of many such non visible characters.
Substitute ( TrimAll ( Table::TextField ; 0 ; 1 ) ; [ ¶; "; "] ; [Char ( 9 ) ; "" ] ) Worked like a charm.
Thank you Phil!