Discussions

Expand all | Collapse all

Formula to convert text line to number string

  • 1.  Formula to convert text line to number string

    Posted 16 days ago
    Hello All,

    Looking to convert up to 60 character text fields to a number that is unique to that specific text.

    Has anyone solved for this before?  Basically for each response would like a unique number or if the same response happened it could share that number also. 

    Hope this makes sense.




    ------------------------------
    letsmakeitbetter letsmakeitbetter
    ------------------------------


  • 2.  RE: Formula to convert text line to number string

    Posted 16 days ago
    Hi,

    So would the number being output by this 60 character text field also be a 60 character number?

    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------



  • 3.  RE: Formula to convert text line to number string

    Posted 16 days ago
    It could be but not necessary.  Only thing that is important is that unique text string has a unique number.  string doesnt need to be case sensitive.

    ------------------------------
    letsmakeitbetter letsmakeitbetter
    ------------------------------



  • 4.  RE: Formula to convert text line to number string

    Posted 16 days ago
    How many digits does the number need to be limited to?  Can it be a text string using 0-9 only?  Actual Quick Base numeric fields cannot have more than about 13 digits before they loose accuracy.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Formula to convert text line to number string

    Posted 16 days ago
    Thanks for responding!  The length of the number doesn't matter to me only that it is unique to text.

    ------------------------------
    letsmakeitbetter letsmakeitbetter
    ------------------------------



  • 6.  RE: Formula to convert text line to number string

    Posted 16 days ago
    Edited by Mark Shnier (YQC) 16 days ago
    I think this will work

    var text MyField = Upper([Text to Convert]);

    var text StepOne = SearchAndReplace($MyField,"A","1");

    var text StepTwo = SearchAndReplace($StepOne,"B","2");

    var text StepThree = SearchAndReplace($StepTwo,"C","3");


    etc

    var text StepTwentySix = SearchAndReplace($StepTwentyFive,"Z","26");

    var text StepTwentySeven = SearchAndReplace($StepTwentySix," ","27");

    $StepTwentySeven

    EDIT: This will be a formula text field type.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Formula to convert text line to number string

    Posted 16 days ago
    It was a multi list field and using both upper and lower case for more detailed numbering so hope thats not causing the issue.
    Here is the formula I made from that which returned the same text from Text To Convert .  Any ideas?  This sort of formula is new to me so hoping it is just a simple mistake.

    var text MyField = ToText([Text To Convert]);

    var text StepA = SearchAndReplace($MyField,"!","1");
    var text StepC = SearchAndReplace($MyField,"#","3");
    var text StepD = SearchAndReplace($MyField,"$","4");
    var text StepE = SearchAndReplace($MyField,"%","5");
    var text StepF = SearchAndReplace($MyField,"&","6");
    var text StepG = SearchAndReplace($MyField,"'","7");
    var text StepH = SearchAndReplace($MyField,"(","8");
    var text StepI = SearchAndReplace($MyField,")","9");
    var text StepJ = SearchAndReplace($MyField,"*","10");
    var text StepK = SearchAndReplace($MyField,"+","11");
    var text StepL = SearchAndReplace($MyField,",","12");
    var text StepM = SearchAndReplace($MyField,"-","13");
    var text StepN = SearchAndReplace($MyField,".","14");
    var text StepO = SearchAndReplace($MyField,"/","15");
    var text StepP = SearchAndReplace($MyField,"0","16");
    var text StepQ = SearchAndReplace($MyField,"1","17");
    var text StepR = SearchAndReplace($MyField,"2","18");
    var text StepS = SearchAndReplace($MyField,"3","19");
    var text StepT = SearchAndReplace($MyField,"4","20");
    var text StepU = SearchAndReplace($MyField,"5","21");
    var text StepV = SearchAndReplace($MyField,"6","22");
    var text StepW = SearchAndReplace($MyField,"7","23");
    var text StepX = SearchAndReplace($MyField,"8","24");
    var text StepY = SearchAndReplace($MyField,"9","25");
    var text StepZ = SearchAndReplace($MyField,":","26");
    var text StepAA = SearchAndReplace($MyField,";","27");
    var text StepBB = SearchAndReplace($MyField,"<","28");
    var text StepCC = SearchAndReplace($MyField,"=","29");
    var text StepDD = SearchAndReplace($MyField,">","30");
    var text StepEE = SearchAndReplace($MyField,"?","31");
    var text StepFF = SearchAndReplace($MyField,"@","32");
    var text StepGG = SearchAndReplace($MyField,"A","33");
    var text StepHH = SearchAndReplace($MyField,"B","34");
    var text StepII = SearchAndReplace($MyField,"C","35");
    var text StepJJ = SearchAndReplace($MyField,"D","36");
    var text StepKK = SearchAndReplace($MyField,"E","37");
    var text StepLL = SearchAndReplace($MyField,"F","38");
    var text StepMM = SearchAndReplace($MyField,"G","39");
    var text StepNN = SearchAndReplace($MyField,"H","40");
    var text StepOO = SearchAndReplace($MyField,"I","41");
    var text StepPP = SearchAndReplace($MyField,"J","42");
    var text StepQQ = SearchAndReplace($MyField,"K","43");
    var text StepRR = SearchAndReplace($MyField,"L","44");
    var text StepSS = SearchAndReplace($MyField,"M","45");
    var text StepTT = SearchAndReplace($MyField,"N","46");
    var text StepUU = SearchAndReplace($MyField,"O","47");
    var text StepVV = SearchAndReplace($MyField,"P","48");
    var text StepWW = SearchAndReplace($MyField,"Q","49");
    var text StepXX = SearchAndReplace($MyField,"R","50");
    var text StepYY = SearchAndReplace($MyField,"S","51");
    var text StepZZ = SearchAndReplace($MyField,"T","52");
    var text StepAAA = SearchAndReplace($MyField,"U","53");
    var text StepBBB = SearchAndReplace($MyField,"V","54");
    var text StepCCC = SearchAndReplace($MyField,"W","55");
    var text StepDDD = SearchAndReplace($MyField,"X","56");
    var text StepEEE = SearchAndReplace($MyField,"Y","57");
    var text StepFFF = SearchAndReplace($MyField,"Z","58");
    var text StepGGG = SearchAndReplace($MyField,"[","59");
    var text StepJJJ = SearchAndReplace($MyField,"^","62");
    var text StepKKK = SearchAndReplace($MyField,"_","63");
    var text StepLLL = SearchAndReplace($MyField,"`","64");
    var text StepMMM = SearchAndReplace($MyField,"a","65");
    var text StepNNN = SearchAndReplace($MyField,"b","66");
    var text StepOOO = SearchAndReplace($MyField,"c","67");
    var text StepPPP = SearchAndReplace($MyField,"d","68");
    var text StepQQQ = SearchAndReplace($MyField,"e","69");
    var text StepRRR = SearchAndReplace($MyField,"f","70");
    var text StepSSS = SearchAndReplace($MyField,"g","71");
    var text StepTTT = SearchAndReplace($MyField,"h","72");
    var text StepUUU = SearchAndReplace($MyField,"i","73");
    var text StepVVV = SearchAndReplace($MyField,"j","74");
    var text StepWWW = SearchAndReplace($MyField,"k","75");
    var text StepXXX = SearchAndReplace($MyField,"l","76");
    var text StepYYY = SearchAndReplace($MyField,"m","77");
    var text StepZZZ = SearchAndReplace($MyField,"n","78");
    var text StepAAAA = SearchAndReplace($MyField,"o","79");
    var text StepBBBB = SearchAndReplace($MyField,"p","80");
    var text StepCCCC = SearchAndReplace($MyField,"q","81");
    var text StepDDDD = SearchAndReplace($MyField,"r","82");
    var text StepEEEE = SearchAndReplace($MyField,"s","83");
    var text StepFFFF = SearchAndReplace($MyField,"t","84");
    var text StepGGGG = SearchAndReplace($MyField,"u","85");
    var text StepHHHH = SearchAndReplace($MyField,"v","86");
    var text StepIIII = SearchAndReplace($MyField,"w","87");
    var text StepJJJJ = SearchAndReplace($MyField,"x","88");
    var text StepKKKK = SearchAndReplace($MyField,"y","89");
    var text StepLLLL = SearchAndReplace($MyField,"z","90");
    var text StepMMMM = SearchAndReplace($MyField,"{","91");
    var text StepNNNN = SearchAndReplace($MyField,"|","92");
    var text StepOOOO = SearchAndReplace($MyField,"}","93");
    var text StepPPPP = SearchAndReplace($MyField,"~","94");

    $StepPPPP

    ------------------------------
    letsmakeitbetter letsmakeitbetter
    ------------------------------



  • 8.  RE: Formula to convert text line to number string

    Posted 16 days ago
    Each step has to refer to the previous step

    for example in the missing StepB

    var text StepB = SearchAndReplace($StepA,"#","3");

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------