3 Replies Latest reply on Feb 19, 2017 5:13 PM by Philip_Jaffe

    Parse Phone Numbers from Text

    fmdataweb

      I have a general notes field where users have entered in phone numbers and other text, for example:

       

      Home: 123 456 7890 Mobile: 987 654 3211 best to call after 5pm

       

      I'm trying to come up with a method to extract the phone numbers from the text so I can move them to dedicated phone number fields in a looping script, but I'm stuck on how I can identify where a phone number starts and ends (given there can be multiple phone numbers in the text field).

       

      Anyone been down this road before or know of a solution (recursive custom function etc?).

       

      Thanks!

        • 1. Re: Parse Phone Numbers from Text
          Vaughan

          If the sample data you posted is representative of the real data, the phone numbers are preceded by a colon and a space. Get the position of each colon and the next 12 characters are the phone number (with spaces).

           

          If there is no pattern to the data entry (that is, the phone numbers aren't always preceded by a colon) then the task becomes difficult to the point where human intervention is required.

          • 2. Re: Parse Phone Numbers from Text
            Philip_Jaffe

            Well, it's easy enough to get the numbers out using the Filter function, but beyond that you will need to first establish a set of rules (algorithms) as to what constitutes a phone number.  Usually it's something like what is surrounding the numbers left and right that you are looking for.  And ususally it will be a combination of passes you will need to make to do that.

             

            I would look on Briandunning.com to see if any of the custom parsing functions come close to what you need.

             

            Ultimately it will be determined by what your data looks like.  Once you get it done, set up some field validation or formatting auto-enter calculation to keep users from doing this in the future.

             

            How many records are you working with?

            • 3. Re: Parse Phone Numbers from Text
              Philip_Jaffe

              If as Vaughan says, the phone numbers are always preceded by a colon+space there are definitely a few custom functions that will do a lot of the work for you.  But it will likely still require a little human intervention.

               

              One idea off the top of my head is to keep your original field on the layout at least for a while (maybe in a popover button) so if users come across a phone number in your new field that doesn't look right, they can refer to the original field and manually correct the outliers over time.