4 Replies Latest reply on Mar 30, 2015 3:15 AM by raybaudi

    Extract from text a number with specific number of digits

    Henry_2

      Title

      Extract from text a number with specific number of digits

      Post

      Hi,

      From a text (more specifically a file path) I want to extract a number if the string contains a number of exactly 13 consecutive digits (an ean barcode).

      fyi: The text field may contain more characters, spaces, numbers dates etc. but it is unlikely to contain multiple occurences of 13 consecutive digits (If so one of the occurrences would do fine).

      Any suggestions?

      Henry

        • 1. Re: Extract from text a number with specific number of digits
          philmodjunk

          If there were no other numeric digits in the string this would be much simpler. But since this string can contain numeric digits, you'll need to use a method that loops through the string testing groups of numeric digits.

          This might be a looping script or (if using FileMaker Advanced) a custom function that uses recursion to do the looping.

          Do you have FileMaker Advanced?

          And can you post a few examples of what this text might look like?

          • 2. Re: Extract from text a number with specific number of digits
            Henry_2

            Thank you Phil for thinking with me,

            I am using Filemaker Advanced and I would know how to add a custom function (not yet how to write them though).

            Two example texts would be:

            X:/source_assets_inbox_uploadtool/2015-03-28_17-05-20 example folder/Product_60mm_applegreen_2_6411920054297_3.jpg

            X:/source_assets_inbox_uploadtool/6411920054297/my second product 1253.jpg

            In these examples I'd need to extract 6411920054297

             

            • 3. Re: Extract from text a number with specific number of digits
              raybaudi

              Try this one:

              /*
              EanExtract ( text ) Custom Function
              recursive
              Extracts the first Ean found in a string of text
              */
              Let([
              adj = Substitute ( text ; [ "." ; " " ] ;  [ "," ; " " ] ; [ "-" ; " " ] ) ;
              word = LeftWords ( adj ; 1 ) ;
              IsEan = Filter ( word ; 1234567890 ) = word  and Length ( word ) = 13 ;
              rest = RightWords ( text ; WordCount ( text ) - 1 )
              ];
              Case(
              WordCount ( text ) ;
              If ( IsEan ; word ; EanExtract ( rest ) )
              )
              )

              • 4. Re: Extract from text a number with specific number of digits
                raybaudi

                This is better:

                /*
                EanExtract ( text ) Custom Function
                recursive
                Extracts the first Ean found in a string of text using the feature of TrimAll of add a space between roman and not roman chars
                */
                Let([
                adj = Substitute ( TrimAll ( KanjiNumeral ( text ) ; 1 ; 1 )
                ; [ "一" ; 1 ]
                ; [ "二" ; 2 ]
                ; [ "三" ; 3 ]
                ; [ "四" ; 4 ]
                ; [ "五" ; 5 ]
                ; [ "六" ; 6 ]
                ; [ "七" ; 7 ]
                ; [ "八" ; 8 ]
                ; [ "九" ; 9 ]
                ; [ "〇" ; 0 ]
                ) ;// puts a space between digits and whichever other character
                word = LeftWords ( adj ; 1 ) ;
                IsEan = Filter ( word ; 1234567890 ) = word  and Length ( word ) = 13 ;
                rest = RightWords ( adj ; WordCount ( adj ) - 1 )
                ];
                Case(
                WordCount ( adj ) ;
                If ( IsEan ; word ; EanExtract ( rest ) )
                )
                )