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

Question

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.&nbsp; The problem is further compounded by a lack of a standard entry format.&nbsp; 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.&nbsp; I'm not sure I can do much about the latter.&nbsp;&nbsp;
I'm hoping someone has a more effective solution to this issue.------------------------------Paul Peterson------------------------------

markshnier__you · Answer

Can you post some example of how users enter the data?------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

paulpeterson1 · Answer

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.&nbsp; 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 · Answer

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 · Answer

Thank you Mark!&nbsp; 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)​
&nbsp;Thanks for the suggestion.&nbsp; Now that I think of it, can I use a REGEX in a formula field?------------------------------Paul Peterson------------------------------

paulpeterson1 · Answer

There was one additional issue I didn't anticipate.&nbsp; Some of the store numbers had a leading 0.&nbsp; I just changed the field type to formula - text and an getting the desired results.------------------------------Paul Peterson------------------------------

Forum Discussion

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