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 compounded by a lack of a standard entry format.  My current approach is:

var textlist values = Split([Number test], " ");
var text valOne = Part(ToText($values), 1, ";");
var text valTwo = Part(ToText($values), 2, ";");
var text valThree = Part(ToText($values), 3, ";");

If(ToNumber($valOne) != 0, ToNumber($valOne),
    ToNumber($valTwo) != 0, ToNumber($valTwo),
    ToNumber($valThree) != 0, ToNumber($valThree))​


This will handle the majority of the cases, but I cannot be positive that a user will not have more than two words before the store number and in some cases the user did not leave a space between text and the store number.  I'm not sure I can do much about the latter.  

I'm hoping someone has a more effective solution to this issue.



------------------------------
Paul Peterson
------------------------------
Formulas and functions

7 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    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
        ------------------------------