4 Replies Latest reply: May 15, 2014 10:02 AM by Oli _ RSS

    Numbers in a text field

    Oli _

      Hi,

      I'd like to extract 8-digit number out of a text field.

       

      The place of the 8-digit number is not always at the same place. There is some text before this 8-digit number and sometimes behind. Which formula is the right one for this job ?

       

      KR

      Oli

        • Numbers in a text field
          Grant Perkins

          Hi Oli,

           

          If your Monarch version has the STRIP function you could consider stripping al alpha characters just leaving the numerics.

           

          This is not a sophisticated solution and there may be other aspects of what you need to do that make it inappropriate - but it would work. Possibly some sort of Trim function could come into play as well to tidy things up.

           

          HTH.

           

           

           

          Grant

            • Numbers in a text field
              Oli _

              Hi Grant;

              E.g.

              "Automatische Gutschrift wurde erstellt. BelegNr.: 62462331. Buchungsdatum des Quellbeleges:2008-06-0"

               

              I'd like to extract the 62462331 in a numeric field.

               

              What is therfore the right formula ?

               

              KR

              Oli

                • Numbers in a text field
                  Grant Perkins

                  Hi Oli,

                   

                  Firstly do you have a version of Monarch that includes the STRIP() function?

                   

                  If so have a look at the Help for that function  - you should see some examples.

                   

                  Basically the formula would look something like

                   

                  STRIP(,"abcdefghijklmnopqrstuvwxyz-.,:;)

                   

                  and include all the characters that the field might contain EXCEPT the numerics.

                   

                  However, what you did not mention in the first post is that there may be more than one number group in the string. That means that at some point you also have to select for the number group you want. There are several ways to do that. Which one is best for you will depend on whether the fields you need to extract from are relatively consistent.

                   

                  For example, if the number you want always appears after the first ":" in the string then you could use the LSPLIT() function to break the string into 2 fields at the colon position and then use the LEFT() function (and a LTRIM() ) to get the number string.

                   

                  If the number does not always follow the FIRST colon but does always appear after the first "BelegNr.:" then you could use the INSTR() function to find the start position of the "BelegNr.:" string within the complete data. You would then know that the number you want starts 10 characters after that. That information allows you to use the SUBSTR() function to extract the number information you need.

                   

                  HOWEVER ...

                   

                  If the long data string does not offer such rules or consistency to help you 'slice and dice' the information you may need to experiment with some more concepts for conditional processing until you can identify all of the different ways the data may be presented in the report. Once you have identified the logic of the possibilities it should be possible develop the formulas that Monarch will need.

                   

                  For your posted sample field if you strip out all the characters except spaces and numerics and then do LTRIM()  on the result you should get something like:

                   

                  62462331   2008060

                   

                  LSPLIT() on that using space and take the first part would get you waht you want.

                   

                  HTH.

                   

                   

                   

                  Grant