3 Replies Latest reply on Aug 18, 2010 1:50 PM by philmodjunk

    Multiple spaces and returns

    cosmo

      Title

      Multiple spaces and returns

      Post

      Good Afternoon -

      I have an unusual request - I have an excel sheet that I need to upload into Filemaker. Not that this is a problem, but instead of using soft returns within the cell - multiple spaces were entered until they were on the next line within the field.

      My problem is that I need to replace the spaces with a (one) paragraph code <p>

      To provide some visual, the number of spaces entered varies and if you exend the column width - the text from the previous lines move up along with the spaces.

      Note: I am working on a mac using Filemaker Pro 10.

      Thank you in advance for your suggestions.

        • 1. Re: Multiple spaces and returns
          philmodjunk

          Hmmm, TrimAll can be used to remove extra spaces between words, but inserting a single ¶ everywhere you have multiple spaces is a problem.

          TrimALL ( Substitute ( textField ; ["          "; ¶ ] ; ["          "; ¶ ] ; ["         "; ¶ ] ; ["        "; ¶ ] ; ["      "; ¶ ] ; ["     "; ¶ ] ; ["    "; ¶ ] ; ["   "; ¶ ] ; ["  "; ¶ ] ) ; 0 ; 0 )

          That should work for all blocks of spaces from 2 to 10 spaces in length (Each bracket string in quotes is a different number of spaces from 2 to 10 spaces in length.)

          You could clean up your imported text by using this calculation in a replace fields operation on your text field. Back up your file first so that you can start over if you don't like the results.

          • 2. Re: Multiple spaces and returns
            cosmo

            I tried the formula out, but it's not working correctly - all it is doing is takign the spaces out. Not sure why the substitute will not work.

            ...and I did put it n the replace fields operation. Do you have another suggestion?

            Thanks!

            • 3. Re: Multiple spaces and returns
              philmodjunk

              I copied the above expression into the definition of a calculation field so that I could compare results with the original and it works. I then used it with replace field contents to confirm that it works in that context also.

              Maybe what looks like a space isn't really a space character. There are other characters which look like spaces but aren't such as "non breaking spaces" and "em spaces" and these will also be trimmed by the TrimAll function--which would explain why the spaces were removed.

              If you can identify the character and its ASCII code, you may be able to replace "   " in the above expression with something like: Char ( 173 ) & Char ( 173 ) ....

              To figure out what the character code is, you might try defining a calculaition field such as: code ( Middle ( textField ; 20 ; 1 ) )

              You'd find a record where the character at position 20 in your field is one of these blanks.