I have a text field which contains a code that is in the format of M followed by 5 numbers, which varies in position in the field and the actual numbers vary as well eg. M80410, M60234 etc.

I want to make a calculation that extracts just that code from the rest of the text in that field.

I tried using:

Middle(text field;Position(text field;"M#####";1;1);6)

Trying PatternCount (text field;"M#####") failed as well.

I then realised that the the wildcard # for numbers (and wildcards in general) didn't work in calculations. I can't figure out how to tell the middle function to look for an M followed by 5 numbers in the text field.

Thanks for any advice.

Maybe you could do something like this.

Let ( [ f= field;

        x= Substitute ( field ; [0;"^"]; [1;"^"]; [2;"^"]; [3;"^"]; [4;"^"]; [5;"^"];  [6;"^"]; [7;"^"]; [8;"^"];  [9;"^"] );

        p= Position ( x; "M^^^^^"; 1; 1 )  

      ]; 

        Case ( p;  Middle ( f; p; 6 ) )

     )

Edited January 26, 200718 yr by Guest
Added Empty check

Brillant ! So make a variable with every number in the field I need to parse from a "^" so then I can have a search string that works !

Thanks very much, works a treat.