Skip to main content
Question

Removing numbers from a text field

  • February 24, 2017
  • 0 replies
  • 151 views
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.
      This topic has been closed for replies.

      can you post some sample data?
      • JasonJohnson
      • Author
      • Quickbase Alumni
      • February 24, 2017
      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
      • GauravSharma3
      • Quickbase Alumni
      • February 25, 2017
      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
      • JasonJohnson
      • Author
      • Quickbase Alumni
      • February 25, 2017
      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.
      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
      • Author
      • Quickbase Alumni
      • February 25, 2017
      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," !@#$%^&*';:?/><")
      Terms & ConditionsAccessibility statement