4 Replies Latest reply on Jan 8, 2013 6:20 PM by EricaJackson

    Remove spaces after pasting a column

    EricaJackson

      Title

      Remove spaces after pasting a column

      Post

           Hello

           I copy a column list of medcations and paste the list into the "Medications" text field. The filed has an autocalculation TextFormatRemove (Self).   I then have a calulation field     cMedications    that turns the list in the first field into a paragraph

           Substitute ( Medications; "¶" ; "; " )    The resulting paragraph has many empty spaces  between each medication. I've been reading on the Forum and I think I need to use a trim function but I'm not sure which field to apply to. And since both fields already have a calculation, can another calculation be added? Or should I try to add a third calculation field and apply the trim function to cMedcation field...I tried a couple of combinations and I keep getting error pop ups

           Thanks for the help!

           Erica

        • 1. Re: Remove spaces after pasting a column
          philmodjunk

               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.

          • 2. Re: Remove spaces after pasting a column
            EricaJackson

                 Thanks, Phil

                 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.

            • 3. Re: Remove spaces after pasting a column
              philmodjunk

                   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 ) ; ¶; "; " )

                   Produced:

                   "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.

              • 4. Re: Remove spaces after pasting a column
                EricaJackson

                     Substitute ( TrimAll ( Table::TextField ; 0 ; 1 ) ; [ ¶; "; "] ; [Char ( 9 ) ; "" ] )  Worked like a charm.   

                     Thank you Phil!

                     Erica