Forum Discussion

PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
3 years ago

Is it possible to extract just the numeric part of text field?

I have a field that will contain a number that I need for other activities and know there will be too much push back if the users are asked to go back and modify the data.  The problem is further comp...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
Can you post some example of how users enter the data?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
3 years ago
In most cases the data is:

Customer then Store Number, TTTTTTT #####
But in some cases the user enters the word "Store" between the company and the store number.
Others add the city after Store Number, and still others do not leave a space between the word store and the number, and finally the case I don't think simple logic will resolve, in some instances the users prepend the the store number with the country.  Although, now that I think of it, I am pretty sure I can handle removing the country because that account uses a fairly standard format.

------------------------------
Paul Peterson
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    This should work if you complete the series up to Z.  Can you get it working and post back so I can add your knowledge to the Borg Collective.


    var text Hash = SearchAndReplace(Upper([Store # text]), "#","");
    var text Space = SearchAndReplace($Hash, " ","");
    var text A = SearchAndReplace($Space, "A","");
    var text B = SearchAndReplace($A, "B","");
    var text C = SearchAndReplace($B, "C","");
    var text D = SearchAndReplace($C, "D","");

    ToNumber($D)

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • PaulPeterson1's avatar
      PaulPeterson1
      Qrew Assistant Captain
      3 years ago
      Thank you Mark!  I omitted the first line as I didn't see anyone using an octothorpe in the field, but the rest of it went something like this:

      var text Space = SearchAndReplace(Upper([Store Number]), " ","");
var text A = SearchAndReplace($Space, "A","");
var text B = SearchAndReplace($A, "B","");
var text C = SearchAndReplace($B, "C","");
var text D = SearchAndReplace($C, "D","");
var text E = SearchAndReplace($D, "E","");
var text F = SearchAndReplace($E, "F","");
var text G = SearchAndReplace($F, "G","");
var text H = SearchAndReplace($G, "H","");
var text I = SearchAndReplace($H, "I","");
var text J = SearchAndReplace($I, "J","");
var text K = SearchAndReplace($J, "K","");
var text L = SearchAndReplace($K, "L","");
var text M = SearchAndReplace($L, "M","");
var text N = SearchAndReplace($M, "N","");
var text O = SearchAndReplace($N, "O","");
var text P = SearchAndReplace($O, "P","");
var text Q = SearchAndReplace($P, "Q","");
var text R = SearchAndReplace($Q, "R","");
var text S = SearchAndReplace($R, "S","");
var text T = SearchAndReplace($S, "T","");
var text U = SearchAndReplace($T, "U","");
var text V = SearchAndReplace($U, "V","");
var text W = SearchAndReplace($V, "W","");
var text X = SearchAndReplace($W, "X","");
var text Y = SearchAndReplace($X, "Y","");
var text Z = SearchAndReplace($Y, "Z","");

ToNumber($Z)​
       

      Thanks for the suggestion.  Now that I think of it, can I use a REGEX in a formula field?

      ------------------------------
      Paul Peterson
      ------------------------------
      • PaulPeterson1's avatar
        PaulPeterson1
        Qrew Assistant Captain
        3 years ago
        There was one additional issue I didn't anticipate.  Some of the store numbers had a leading 0.  I just changed the field type to formula - text and an getting the desired results.

        ------------------------------
        Paul Peterson
        ------------------------------