I have a text field that has an order number in it that is a 13 digit number. This number is in each record but the text is always different and the order number can be different.

The only constant is that the order number is 13 characters long and is numeric.

I cannot know what the order number or text will be so I cant do a portal or loop though to find the order number. Best if explained by example:

record 1

qwreouh arw1226213456998eouaw awreohou arouw awgou

record 2

agw24 erh43'pwr|gw4uon w4gpnhp5216813050354w4gh

What im looking for is to make a calculation field that will trim off all the text and numbers that are not this particular 13 digit number order number. so for the above two records it would output each order number:

record 1

1226213456998

record 2

5216813050354

Thanks

You need a custom function to do this, but since it appears you do not have advanced (developer), you would need to use a script instead.

The brute force way would be to check if the first 13 chars are all digits, if so, they are the order number, otherwise, drop the first character off the front and try again.

   Length( Filter( Left(txt;13); "0123456789" ) = 13

would be the test to use.

having read over the available version I started hammering away at a cf...

ExtractNumber ( TextToParse ; StrengLength ) =

Case 

( 

  Length ( TextToParse ) > StringLength ; 

  Case 

  ( 

    Length ( 

            Filter ( Left ( TextToParse ; StringLength ) ; "0123456789" ) 

           ) = StringLength ; 

    Left ( TextToParse ; StringLength ) ;  

    ExtractNumber ( Right ( TextToParse ; Length (TextToParse) -1 ); StringLength )

  ) 

;

"no result"

)

it just takes the first occurrence of 13 digits. By the way there is no telling what to do if there should coincidentally be a string of 15 or 20 digits - in which case the actual sequence of numbers could be in many positions inside that string. Or if there is more than 1 occurrence of 13 strings, then what?

Are you sure there are no telltale delimiters, such as for instance a hash character or even "order no:" in there?

PS edit : of course I should have realised that GetAsNumber ( Left ( TextToParse ; StringLength ) ) will return a faulty string if the first digit is 0.... so The Shadows test in stead.... my bad

Edited May 25, 200817 yr by Guest
see PS edit

Try:

Let ( [

mask = Substitute ( text ;  [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ) ; 

pos = Position ( mask ; "#############" ; 1 ; 1 )

] ;

Middle ( text ; pos ; 13 )

)

Note that this assumes the order number is the only (or at least the first) string of 13 consecutive digits in text - and that text doesn't contain the "#" character, of course!

Edited May 25, 200817 yr by Guest
edited note

Sorry but im using FMP 8 v3 so the function ExtractNumber is not available to me. Is there a work around to this?

use comment's calc.... zowee!

Thanks, that got it. Did not think to set up each number with a #. Good one!

Very nicely done.