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