Forum Discussion

JasonJohnson's avatar
JasonJohnson
Qrew Assistant Captain
9 years ago

Removing numbers from a text field

I have a field that contains a ship to location coming from another company system. There is no standard formatting in the system of origin so we receive data that is numeric and text. I need to get the numeric out of the text.(basically a store number).
 Currently I am using the following formula to do this and mostly it works since the deliminator in most cases is # or - . I do refer back to the original field because sometimes it is just a number.

If(Contains([Ship to Location],"-"),Part([Ship to Location],2,"-"),
If(Contains([Ship to Location],"#"),Part([Ship to Location],2,"#"),
[Ship to Location]))

The problem is that I have many entries that will have multiple words and a number all separated by spaces and the number can be either in the front or the back. The good news is that there is only one set of numbers in an entry and they are  always the numbers I need.
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    9 years ago
    American Eagle 01019
    Q31
    Petro Site #331
    TSC #01758
    CC-2313
    434 J.Bank

    The Q31 is new but is a non-issue because I can keep or lose the Q
    • JasonJohnson's avatar
      JasonJohnson
      Qrew Assistant Captain
      8 years ago
      Gaurav,
      I have the IOL working and have tested out that solution and it worked great for a few days then it stopped working. Cannot figure out how it stopped working. Have you ever experienced this type of issue where a working code page has failed?
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      You inadvertently changed something any you probably have a syntax error in your JavaScript.
    • JasonJohnson's avatar
      JasonJohnson
      Qrew Assistant Captain
      8 years ago
      Here is the code we tested and it is yours so  maybe I made a mistake somewhere
      (function(){
        var querystring=document.location.search;

        if(/a=er/i.test(querystring)) {
          //EDIT RECORD PAGE ========================================
          alert("You are on the Edit Record Page");

          $("#_fid_50").attr("readonly", true);

          $("#_fid_17").on("blur, change, keyup", function() {
            var field = $("#_fid_17").val();
            var terms = field.split(/\D+/g);
            var product = _.reduce(terms, function(memo, item,index) {
              return item.length > 0 ? item * memo: memo;
            }, 1);
            $("#_fid_50").val(product);
          });

        }

      })();
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    9 years ago
    I went to his test and it tests as needed. His code is just at the edge of my knowledge and I cannot figure out how to apply it with my field name.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    If you don't want to use script and you know that the field has a limited number of characters, you could literally parse out each character and check if it is a number.  It would use a whole bunch of formula variables to parse out each character and then test it and add it to the string if it was numeric.
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    9 years ago
    I have the number last working now and only need to resolve the number first. I changed the formula to this.
    Part([Ship to Location],-1," !@#$%^&*';:?/><")