Remove empty spaces, with a twist.

[AbsoluteVoice4u]

What is the best way to remove blank spaces from a zipcode field, throughout the database?

The field will sometime contain one or more spaces upon import. Either at the beginning or end and even sometimes in the middle.

I'm looking for a script solution that will look at the field and remove any blank spaces, move everything to the left and not delete any characters or numbers in the record. Sounds simple but I'm driving myself crazy attempting this. Any suggestions or even a solution is greatly appreciated.

[LaRetta]

Hi there!!!

Try: TrimAll ( zipfield ; 0 ; 3 )

This will remove ALL spaces from the field, before, after, middle or double spaces etc.

[bruceR]

Replace [ zipCode; trim(zipCode) ]

Replace operates on the entire found set so it's a good idea to have a backup or original data source when using it.

[AbsoluteVoice4u]

LaRetta.....******* yer good! Oooops...I mean "darn"!

Works like a charm!

Thank youuuuuuuuuu :-)

[marxster]

LaRetta I have a question.

I'm trying to remove spaces before and after the Last Name and First Name. Your suggestion works fine, however, I had already a TextFormatRemove function on those same fields.

When I remove the TextFormatRemove, your suggestion works fine, spaces are removed, however, when I put the TextFormatRemove back in, with the TrimAll function, it either replaces text with other text or writes a 0 instead.

How do I make sure that both functions work fine at the same time? Thanks.

Marco

Edited August 13, 2010 by Guest

[bruceR]

Which order do you do it?

trimAll( textformatRemove( self); 3)

or

textFormatRemove( trimAll( self; 3))

[marxster]

Actually, I now found a better calculation that not only gets rid of spaces but also of carriage returns and tabs (see below).The thing is I also want to remove formatting. The text field is "Last Name". Before, when I wasn't using the space removal calculation I had only TextFormatRemove ( Last Name ). So how do I go about making sure that the latter and the below work together?

Let(

[

// Determine value of original string with ALL whitespace removed.

t2 = Substitute( text; [ " "; "" ]; [ " "; "" ]; [ " "; "" ]; [ "¶"; "" ] );

// Determine position of first non-ws character in original string.

first_char = Position( text; Left( t2; 1 ); 0; 1 );

// Determine position of last non-ww character in original string.

last_char = Position( text; Right( t2; 1 ); Length( text ); -1 )

];

// If any non-whitespace characters exist return appropriate

// middle portion of original text.

Case( first_char; Middle( text; first_char; last_char - first_char + 1 ) )

)

[bruceR]

Not clear what the problem is. Use textformatRemove().

etc. etc....

Case( first_char; Middle( textFormatRemove( text );

first_char; last_char - first_char + 1 ) )

[marxster]

Bruce,

I'm not a filemaker developer, so these calculations are Chinese to me.

So, from your post I gather that I'm to insert TextRemoveFormat into the other calculation, right?

Edited August 13, 2010 by Guest

[bruceR]

Insert into the other calc, yes.

Or you can just change the top part like this:

Let(

[

text = textformatRemove( text);

// Determine value of original string with ALL whitespace removed.

t2 = Substitute( text; [ " "; "" ]; [ " "; "" ]; [ " "; "" ]; [ "¶"; "" ] );

etc.

[marxster]

I got it to work. See below. Thank you for the help.

Let(

[

// Determine value of original string with ALL whitespace removed.

t2 = Substitute( First Name; [ " "; "" ]; [ " "; "" ]; [ " "; "" ]; [ "¶"; "" ] );

// Determine position of first non-ws character in original string.

first_char = Position( First Name; Left( t2; 1 ); 0; 1 );

// Determine position of last non-ww character in original string.

last_char = Position( First Name; Right( t2; 1 ); Length( First Name ); -1 )

];

// If any non-whitespace characters exist return appropriate

// middle portion of original text.

Case( first_char; Middle( TextFormatRemove( First Name ); first_char; last_char - first_char + 1 ) )

)

[marxster]

Actually, I just realized that it works for those records that I create, however, it doesn't remove the spaces from existing records. How do I achieve that?

[LaRetta]

If I understand your request, you want to remove all beginning and ending spaces, carriage returns, tabs before and after text only. And you want to also be left with formatting removed? Have you considered something a bit easier such as:

[color:blue]TextFormatRemove ( LeftWords ( text ; WordCount ( text ) ) )

Also, I assume then that your field is text with auto-enter (Replace)? If so, you can properly format all records quickly ( BACK UP FIRST ) by going to field definitions and changing your text field to calculation with the above calc and be sure at the bottom that the result is text. Do NOT touch the Storage Options ... you don't want to make it unstored or you will lose your data.

Exit out of field definitions. You will see the text field corrects itself. Now go back in and change it back to type Text. If you go back into your auto-enter calculation you will see that your calc will still be there. Back at auto-enter tab, be sure to again uncheck 'do not replace existing data'.

NB: xWords removes all non word-break characters from beginning and end automatically. Note that it will also remove periods, slashes and so forth (all which would work well in value list items, names etc but NOT in sentences.

Update: You can use LeftWords() as well so I changed the calc. The reason I like this approach is because it is far simpler than stripping each character individually and it allows word-delimiters in between words (such as space, dash etc) which is important ... someone can have a last name with a space in it ... ask someone from France for instance. :^)

Edited August 13, 2010 by Guest

[marxster]

No, my fields are not auto-enter, but I have to import records from a webform. If people enter spaces, I end up with spaces in my database.

Anyway, the solution I mentioned in my last post works on all newly created records but not on existing records.

[LaRetta]

You asked, "Actually, I just realized that it works for those records that I create, however, it doesn't remove the spaces from existing records. How do I achieve that?"

I gave you a way to do just that - correct your existing records AND keep junk out automatically using an auto-enter calculation on the field with my calculation and unchecking 'do not replace existing data'. What ... my calc is too long for you?

You might want to give it a try.

UPDATE: For it to work seamlessly with imports, be sure to check 'perform auto-enter' when asked during your import.

Edited August 13, 2010 by Guest

[comment]

I like your approach, but you should keep the TrimAll() - in case that French person put two spaces between their names:

TrimAll ( LeftWords ( text ; WordCount ( text ) ) ; 0 ; 0 )

However, that still doesn't handle tabs and returns between words.

[bruceR]

No, my fields are not auto-enter, but I have to import records from a webform. If people enter spaces, I end up with spaces in my database.

Anyway, the solution I mentioned in my last post works on all newly created records but not on existing records.

You will have to use a replace operation on your existing records, using the calculated Replace operation. When working with Replace - and *especially* when first learning about Replace make sure you first have a backup.

If your auto-enter calc is now working correctly you can just replace the field with itself and the auto-enter calc will do its job.

Find your existing records.

Click once in the field in question.

Choose menu Records: Replace Field Contents...

Click the bottom "Specify" button for Replace with Calculated Result

Select your field

Say OK to everything

[LaRetta]

The method I suggested is faster than Replace Field Contents[], it doesn't require going to a layout with that field on it and it doesn't require showing all records first. It also means that one can establish the auto-enter (replace) calculation once without having to 1) create it in field definitions AND then still go run the Replace Field Contents[] setting the field to itself (or a calculation).

But then, that's just my opinion.

[LaRetta]

Good morning, Michael.

No, it doesn't protect from unwanted characters between possible two-word last names but I can only go by the information given (an instruction said to me repeatedly by an old friend). And I even stated fact that it was addressing before and after only to Markster.

I did consider potential garbage between the rare two-word last names but it felt a bit like overkill. But was there a reason you dropped the TextFormatRemove()?

[comment]

I can only go by the information given

Of course - but since I suggested a modification to your approach, I felt bound to point out its exact (limited) scope.

was there a reason you dropped the TextFormatRemove()?

Not really relevant to the topic, is it?

[LaRetta]

I wasn't being critical nor defensive. I really appreciated your input. But Markster's request was with formatting removed and you do very little without reason so I wondered why you dropped it; that's all. :wink2:

[marxster]

Got everything to work. Thanks everyone for the helpful advice.