6 Replies Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    Extract numbers of a string

    Oli _

      Hi, I like to extract a 8-digit number extracted out of a string field.

       

      e.g. "Give back-Nr: 12345678"

      or    "extra ordinay voucher: "11223344"

       

      I like to have only the "12345678"

      or "11223344"

       

      What is the correct formula for this request ?

       

      KR

      Oli

        • Extract numbers of a string
          Grant Perkins

          Oli,

           

          Based on the examples ...

           

          Use the RSPLIT() function in a calculated field to separate the number part from the text. This will give you a CHARACTER field.

           

          If the field needs to be a numeric field use the VAL() function to make it numeric.

           

          VAL(RSPLIT(,2," ",1))

           

          Should give what you want or get you close to it.

           

          HTH.

           

           

          Grant

            • Extract numbers of a string
              Oli _

              sorry, the " " is no content of the field. So I don't have a clear identifier.

               

              Rgds

              Oli

                • Extract numbers of a string
                  Nigel Winton

                  You could always try Strip(Field,"A,B,....") for all letters of the alphabet. A bit long winded but it works.

                   

                   

                  Nigel

                    • Extract numbers of a string
                      Data Kruncher

                      If your string always ends with an eight number sequence regardless of what precedes it, then you can use:

                       

                      Val(Right(Field,8))[/CODE]

                        • Extract numbers of a string
                          Nick Osdale-Popa

                          Why I'm bringing up an old thread: I had a need of stripping all numbers from a string and wanted to post a "tip" to these forums, but thought there might have already been a thread about it and I found this one.

                           

                          This tip is for all versions that have the STRIP() command.

                           

                           

                          I'm cleaning up a database where the phone number could look like any of the follwing:

                          888-123-4567

                          (888)123-4567

                          888/123-4567

                          (888) 123-4567

                          (888)-123-4567[/b]

                           

                          Not knowing exactly what characters may be used, one could just assume the following characters for exclusion: "()-/ "[/b] and be done with it.

                           

                          What I did was actually strip any non-numeric characters by calling the STRIP() command twice:

                          First, strip out all numbers:

                           

                          STRIP(,"0123456789")[/b]

                           

                          This will return all non-numeric characters from the string.

                           

                          Now use that as the second parameter of the STRIP() command to return just the numbers:

                           

                          STRIP(,STRIP(,"0123456789"))[/b]

                           

                          Now you have a field with only numbers.

                           

                          I'm not sure if this tip has been brought up before, but I'd thought I'd share nonetheless.

                            • Extract numbers of a string
                              Grant Perkins

                              That's a neat piece of lateral thinking Nick!

                               

                              The basic idea - firstly 'removing' what you want to keep in order to exactly identify what you really need to filter - is easily overlooked when seeking ways to approach this sort of problem but has some wide application if only one remembers the potential when the need arises!

                               

                              Nice work.

                               

                               

                              Grant