Forum Discussion
MarkShnier__You
Qrew Legend
3 years agoCan you post some example of how users enter the data?
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
PaulPeterson1
3 years agoQrew Assistant Captain
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
------------------------------
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__You3 years ago
Qrew Legend
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
------------------------------- PaulPeterson13 years agoQrew Assistant CaptainThank 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
------------------------------- PaulPeterson13 years agoQrew Assistant CaptainThere 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
------------------------------