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 ?




        • Extract numbers of a string
          Grant Perkins



          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.






            • Extract numbers of a string
              Oli _

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




                • 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.




                    • 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:



                        • 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



                          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:




                          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:




                          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.