Removing numbers from a text field

  • 1
  • 2
  • Question
  • Updated 1 year ago
  • Answered
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.
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb

Posted 2 years ago

  • 1
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,266 Points 50k badge 2x thumb
can you post some sample data?
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
Hi Jason,

Please have a look at the Dan solution.

https://community.quickbase.com/quickbase/topics/i-would-like-to-grab-numbers-from-a-text-field-and-... 

It will useful for you.

Thanks,

Gaurav
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,372 Points 20k badge 2x thumb
You inadvertently changed something any you probably have a syntax error in your JavaScript.
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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);
    });

  }

})();
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,266 Points 50k badge 2x thumb
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.
Photo of Jason

Jason

  • 1,012 Points 1k badge 2x thumb
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," !@#$%^&*';:?/><")