4 Replies Latest reply on May 27, 2017 10:42 AM by TomHays

    How to parse the numbers left of the first letter in a string

    petermontague1

      How do you parse the number left of a piece of a letter?

      E.g. 3b1 or 33b1? to get 3 or 33.

        • 1. Re: How to parse the numbers left of the first letter in a string
          erolst

          For your example:

           

          Left ( yourText ; Position ( yourText ; "b" ; 1 ; 1 ) - 1 )

           

          or more generic:

           

          Let ( [

            letter = "b" ;

            letterpos = Position ( yourText ; letter ; 1 ; 1 )

            ] ;

            Left ( yourText ; letterpos - 1 )

          )

           

          I suggest: FileMaker Pro 16 Help

          • 2. Re: How to parse the numbers left of the first letter in a string
            doughemi

            Or, even more generic:

            Let (

            [letters = Filter ( yourText ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQUSTUVWXYZ" );

            firstletter = Left ( letters ; 1 );

            letterpos = Position ( yourText ; firstletter ; 1 ; 1 )

              ] ;

              Left ( yourText ; letterpos - 1 )

            )

            • 3. Re: How to parse the numbers left of the first letter in a string
              TomHays

              Or even more generic with the assumption that the "left of a piece of a letter" is always a string of simple digits 0-9 (and not 4.2 or -3.14 or 6.0221e+23). And the letter can be anything that is not digits 0-9 (e.g. spaces, punctuation, or letters in any character set).

               

              with a field named yourText...

               

              Let(

              [

              theSpacedText = Substitute(yourText;

                 ["1"; " "]; ["2"; " "]; ["3"; " "];

                 ["4"; " "]; ["5"; " "]; ["6"; " "];

                 ["7"; " "]; ["8"; " "]; ["9"; " "]; ["0"; " "]

              );

              firstNonDigitChar = Left(Trim(theSpacedText);1);

              firstNonDigitCharPosn = Position(yourField; firstNonDigitChar; 1; 1)

              ];

              Case(

                 IsEmpty(firstNonDigitChar); Trim(yourField);

                 Trim(Left(yourField; firstNonDigitCharPosn - 1))

              )

              )

              • 4. Re: How to parse the numbers left of the first letter in a string
                TomHays

                And another revision to handle situations where the digits could preceded by non-digits, e.g. x33b1 --> 33

                 

                For a field named yourField...

                 

                Let(

                [

                firstDigitChar = Left(Filter(yourField; "0123456789"); 1);

                firstDigitCharPosn = Position(yourField; firstDigitChar; 1; 1);

                theNumberedText = Right(yourField; Length(yourField) - firstDigitCharPosn + 1);

                theSpacedText = Substitute(theNumberedText;

                   ["1"; " "]; ["2"; " "]; ["3"; " "];

                   ["4"; " "]; ["5"; " "]; ["6"; " "];

                   ["7"; " "]; ["8"; " "]; ["9"; " "]; ["0"; " "]

                );

                firstNonDigitChar = Left(Trim(theSpacedText);1);

                firstNonDigitCharPosn = Position(theNumberedText; firstNonDigitChar; 1; 1)

                ];

                Case(

                   IsEmpty(firstDigitChar); "";

                   IsEmpty(firstNonDigitChar); Trim(LeftWords(theNumberedText; 1));

                   Trim(Left(theNumberedText; firstNonDigitCharPosn - 1))

                )

                )

                 

                 

                The input could be

                "33b1"

                "33"

                " 33 "

                "33é72"

                "a33"

                "a33b1"

                "33 b1"

                "-33 "

                "b33 1"

                "a33 b1"

                 

                with this function returning "33" for any of the above inputs.

                 

                 

                -Tom