filtering numerics in a text field

[RodinBangkok]

I'm writing an import filter from a cad program, one field in this program contains a combination of alphas and numerics in text form such as:

0.1uf

339K

22pf

and so on, however the same field for different components will contain only text:

shotky

transorb

and so on.

Our data base seperates the numerics, so to do a final import we need to filter the numerics in this field.

while experimenting I found that simply doing a text to num function on this field would in fact filter the numerics quite nicely, but when it encounters text only fields the result seems to be ambiguous, ie the result is not predictable sometimes a 1 or 0 or two shows up. Ok, frankly I did'nt expect it to work as well as it did.

So I next expanded the calculation to not perform the convert if no digits were in the field, the calc is listed below:

If(PatternCount(B2, "0")=0 and PatternCount(B2, "1") = 0 and PatternCount(B2, "2")=0 and PatternCount(B2, "3")=0 and PatternCount(B2, "4")=0 and PatternCount(B2, "5")=0 and PatternCount(B2, "6")=0 and PatternCount(B2, "7")=0 and PatternCount(B2, "8")=0 and PatternCount(B2, "9")=0, "", TextToNum(B2))

With this calc so far I've had success with the limited test files I have.

The calc works in FM Mac 6.0V4, but will it work in other platforms or versions, and of coarse many different variations on the field value.

So anyone with experience or suggestions along the lines of an easy way to extract numberics from text fields I'd be interested in your thoughts.

[cjaeger]

there are a couple of letters representing numbers in fileMaker.

mainly, these are "Y" and "N" for "Yes" (=1) and "No" (=0). In FileMaker 6, this also includes "T" or "t" for "true" and "F" "f" for false.

I remember reading that in some versions of FileMaker this would include other Characters (like "J" for "ja" in german FileMaker), but I cannot confirm this. in FM 6 german definetely not.

so your calc is:

Text2Num(Replace(Replace(Replace(Replace(Uppercase(Field);"Y";"")"N";"");"T";"");"F";"")

[RodinBangkok]

Thanks, I'll try this, much more elegant than my patch.

[Pupiweb]

Antother trick you can use is to add a 1 at the beggining of the field and then remove it:

Middle(TextToNum("1" & YourField);2;9999)