Hello all,

I have a field I imported from a SQL file. In each record it has text and a 5 digit number.

INSERT INTO `zipcode` VALUES('00401'

I would like to remove everything except the numbers. I have 42000 records.

Anybody got any thoughts how this may be done.

Thanks

Edited August 31, 200916 yr by Guest

After importing the data into the field, replace with calculated result using something like this formula.

Filter(your_field;“0123456789”)

Worked great, thanks you two

If I could ask one thing more.

The other fields that have the city and state names have in the format 'VT' and 'Bourbon'

How can I remove the ' ' and just leave the city name.

I tried Filter ( City ; "abcdefghijklmnopqrstuvwxyz" ) It took out the ' out and also took out the first letter of the name.

thanks

I see I had to include the caps for each letter.

Got it, Thanks

Edited August 31, 200916 yr by Guest

Not tryign to steal your thread but is it possible to filter the 2nd or 3rd letter?

Say I want a field to show the 3rd letter of another field. CAR9999999

So it would calc the R into the field.

Use the Middle() function.

Rad! Thankyou

I am using the function Middle in a field:

Middle (Gift Card Number; Position (Gift Card Number; ";"; 1; 1) + 1; Position (Gift Card Number; "="; 1; 1) -2)

The problem I am having is it only works if I select the field before this one. Other wise the data is not put in the next field and not completely Middled. Is there something I have left out?

Are you using a calculation field?

No it is a text field and it is axially moving the data 2 fields over .I have a other database that is set up just like this one but the same field has a slightly deferant calculation:

Middle (Gift Card Number; 1; Position (Gift Card Number; "="; 1; 1)-1)

and this one works just fine. But I need to keep defferant info.

it is a text field and it is axially moving the data 2 fields over .

I'm afraid I have no idea what that means. I think that if you have a text field named 'Gift Card Number', and you want to extract a portion of it, you should use another field defined as Calculation, with the result type set to Text.

I'll give it try

I am not taking the data from a field, I am using a card swipe to input the card number.

Ok, now it is working. It seemed to be the Tab Order. Once I removed all tabs, then it work just fine.