Removeing all characters except numbers

[XJoe]

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, 2009 by Guest

[TheTominator]

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

Filter(your_field;“0123456789”)

[XJoe]

Worked great, thanks you two

[XJoe]

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, 2009 by Guest

[sicSRT8]

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.

[comment]

Use the Middle() function.

[sicSRT8]

Rad! Thankyou

[Richard Corso]

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?

[comment]

Are you using a calculation field?

[Richard Corso]

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.

[comment]

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.

[Richard Corso]

I'll give it try

[Richard Corso]

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

[Richard Corso]

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