1 2 3 Previous Next 36 Replies Latest reply on Dec 15, 2016 6:02 AM by FileMakerMentor

    Extract number from text

    WillLockwood

      Title

      Extract number from text

      Post

      I'm trying to extract a seven digit number from a text field. The number location is random in the text and has no significant prefix or suffix. It is not a phone number.

      For example the text might read like this:

      JoeFoo 6/17/2012: I found out that I need to order part 2469842 in order to complete the job. Please send via fed ex overnight.

      or second case:

      MoeFoo 6/18/2012: We need 5 copies of #9876543 sent out overnight please.


      The goal is to extract 2469842 in the first case and 9876543 in the second. Is there a simple way to achieve this reliably?

      Thanks, Will

       

        • 1. Re: Extract number from text
          philmodjunk

          Will the embedded number always be exactly 7 digits long?

          Do you have FileMaker Advanced?

          • 2. Re: Extract number from text
            WillLockwood

            Yes, it will always be exactly 7 digits long, though I have no control over how the user has entered the number. They may write #1234567 or - 1234567 or I1234567. All I need is the number in all cases. I have FM Pro Advanced 11 running.

            • 3. Re: Extract number from text
              philmodjunk

              The number of digits being predictable makes it easy to filter out other numbers such as the date and the "5" in your examples.

              With FileMaker Advanced, you can create a custom function that recurses through a filtered version of your text to extract a the first 7 digit number.

              Will call this function ExtractDigits ( text ; NumbDigits )

              Use this expression to get the 7 digit number you want: ExtractDigits ( Filter ( yourTable::YourTextField ) ; " 0123456789" ) ; 7 )

              // ExtractDigits ( DigitText ; NumbDigits )
              //
              // This custom function searches DigitText and returns the first number where the number of digits = NumbDigits
              //
              //  DigitText    : Text consisting only of numeric digits 0..9 separated by spaces. Use Filter ( yourTable::YourTextField ) to filter out nonnumeric characters
              //  NumbDigits : The number of digits expected for the digits to be extracted
              //
              Case ( IsEmpty ( DigitText ) ; "" ;
                         Length ( LeftWords ( DigitText ; 1 ) ) = NumbDigits ; Length ( LeftWords ( DigitText ; 1 ) ) ;
                         ExtractDigits ( RightWords ( DigitText ; WordCount ( DigitText ) - 1) ; NumbDigits )
                       )

              • 4. Re: Extract number from text
                WillLockwood

                Thanks, I'll give that a try. I haven't used custom functions or expressions in FM before. I'm confused about where the expression gets entered vs the custom function. Will I be adding two custom functions? Does the ExtractDigits expression get added to the Custom Funtions or as a field calculation? Also, there seems to be an extra parens in this expression:

                 

                ExtractDigits ( Filter ( yourTable::YourTextField ) ; " 0123456789" ) ; 7 )

                • 5. Re: Extract number from text
                  philmodjunk

                  You would use a reference to extractDigits--this is one custom function, however and wherever you need this result. It could be used in a calcualtion field, but this is not the only option. A script with set field, for example, could also use it and there are many other possibilities.

                  There are two errors that I see in my post:

                  use:

                  ExtractDigits ( Filter ( yourTable::YourTextField  ; " /0123456789" ) ; 7 )

                  I added the / and you may need to add other characters if they are used as delimitters for the date. Without them, 7/17/2012 is filtered into: 7172012 and thus is a 7 digit number--something we need avoid or the function will return the digits of the date instead of the 7 digit number you want.

                  • 6. Re: Extract number from text
                    WillLockwood

                    Well, this is working better now, but I keep getting the date, minus the /'s or 3/30/2012 = 3302012. It looks like I need to filter dates out of the text first, then refilter for 7 digit number. Is that possible?

                    This is what I tried: Let(numstrg = Filter(Text Field;"0123456789");  Left(numstrg;7))

                    That always gets me to the first date in the text field as 3302012. I need it to ignore anything with ##/##/#### format, before filtering for the 7 digit number. Is that possible?

                    • 7. Re: Extract number from text

                      Save your self a lot of grief and use a part number field since you can never be sure what the user will enter or whether they will enter spaces or Control+Space or anything else.

                      Of course if you are receiving this text you have no control over it and acheive bad results. Avoid scripting for scripting sake, at all costs.

                      Let the user double click to select and copy the text and then paste it into the previously mentioned part number field...

                      Remember: when you have no control over what the user may enter, make the user responsible.

                      It's amazing how often we are willing to spend hours or days to script at a problem that can be solved easily as mentioned here in.

                      • 8. Re: Extract number from text
                        WillLockwood

                        I totally agree. This is with an existing set of records that is being brought into a new db. Going forward, this will not happen because there is an explicit field. The goal here is to extract the number as accurately as possible, but I am extracting the first date instead right now. The number always is 7 digits with no characters in that string.

                         

                        I have a friend that thinks he can do this with a python script if it is too much of a challenge to do within FM. But I wanted to learn how to do this if possible.

                        • 9. Re: Extract number from text
                          philmodjunk

                          I agree with Jack, but can see cases where we can't always set it up that way or need a temporary "fix" until we can get all the changes needed to avoid that issue.

                          take another look at the modified expression that I posted:

                          ExtractDigits ( Filter ( yourTable::YourTextField  ; " /0123456789" ) ; 7 )

                          even a date with minium digits such as 1/1/2012

                          will not be 7 digits long and thus the digits from the dates should not be returned by the function. But the key to that is add / to the filter string.

                          • 10. Re: Extract number from text

                            Since dates can result in 7 digit numbers this must be considered.

                            Why not use a several step process using multiple caluclated fields and a table view of the resulting field(s).

                            I would start with Calc1

                            Filter(mainfield;"1234567890/")

                            this will produce something like

                            12/3/20111234567

                            The next calc2 field would be

                            Right(calc1;7)

                            which should produce

                            1234567

                            You can scan for those instances of more than two numbers or more digits etc...

                            Or use another field for where the date and part number are reversed and use Left(calc2;)

                            Not sure but this seems easier...  :)

                            No matter what you will do there will be an exception so use the above and maybe add a field for OK, find OKs and replace a finished part number field for the OKs. Omit and continue with your next calculation.

                            Doesn't it seem easy if you take out the garbage first?

                            • 11. Re: Extract number from text
                              WillLockwood

                              Yup, I agree, it's a rats nest that we will avoid in the future. Thanks for the good suggestions.

                              • 12. Re: Extract number from text

                                My laptop battery died yesterday before I could post this:

                                CalcA: Filter(mainfield;"1234567890/")

                                CalcB: Right(calc1;7)

                                CalcC: left(calc1;7)

                                Field D: a number field for 1 0r 0 Checkbox

                                CalcE: 

                                Case(

                                patterncount(CalcB;"/") > 0 ; CalcC ;

                                Patterncount(CalcC;"/") > 0 ; CalcB ;

                                FieldD = 1 ; CalcB ;

                                CalcC

                                )

                                And of course a blank field for cut and paste.

                                 

                                That should do it...

                                 

                                 

                                • 13. Re: Extract number from text
                                  philmodjunk

                                  Jack,

                                  Should "calc1" refer to "calcA"?

                                  And will it work if there's a third number in the text (not just a date and part number) as was shown in one example in the OP?

                                  It was the presence of the third number that led me to suggest a recursive CF in the first place. WHich should work as long as you use the updated filter expression that includes / along with Spaces and digits.

                                  • 14. Re: Extract number from text
                                    JimMac

                                    Phil, i was working on a loop solution when I saw your custom function. I was very impressed. After seeing the date exception, i still believe the loop solution will work and i will do that later.  Plus what if there is more than one product code in text?  If that doesn't happen then a loop will solve it. 

                                    Loop through each word for length of say 9, filter that word for digits 0...9, check length of exactly 7, if length 7 then code is found, if not, continue looping to next word until final word in text.

                                    by the way you had a slight error in your custom function, you returned the length, not the word in second case test...corrected:

                                    Case ( IsEmpty ( DigitText ) ; "";
                                     Length ( LeftWords ( DigitText ; 1 ) ) = NumbDigits; LeftWords ( DigitText ; 1 ) ;
                                     ExtractDigits (RightWords ( DigitText; WordCount ( DigitText ) -1); NumbDigits )
                                    )
                                    1 2 3 Previous Next