Just wanted to share this with the group. Just a quick and dirty cipher for those who are looking to quickly make text show as numbers.
There are many other ways to do this that are more classy, but if you just need a simple paste-in for a field to spit out a number this is it. Feel free to change or suggest upgrades as needed.
Notes: Replace [Job Title] with your text field. You can add a . or , or - or whatever in the "" area. 1,1 is 1st letter, 2,1 is 2nd letter...
(ToNumber(List("",(ToText(Case(Mid([Job Title],1,1),"a",1,"b",2,"c",3,"d",4,"e",5,"f",6,"g",7,"h",8,"i",9,"j",10,"k",11,"l",12,"m",13,"n",14,"o",15,"p",16,"q",17,"r",18,"s",19,"t",20,"u",21,"v",22,"w",23,"x",24,"y",25,"z",26,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26," ",0))),(ToText(Case(Mid([Job Title],2,1),"a",1,"b",2,"c",3,"d",4,"e",5,"f",6,"g",7,"h",8,"i",9,"j",10,"k",11,"l",12,"m",13,"n",14,"o",15,"p",16,"q",17,"r",18,"s",19,"t",20,"u",21,"v",22,"w",23,"x",24,"y",25,"z",26,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26," ",0))),
(ToText(Case(Mid([Job Title],3,1),"a",1,"b",2,"c",3,"d",4,"e",5,"f",6,"g",7,"h",8,"i",9,"j",10,"k",11,"l",12,"m",13,"n",14,"o",15,"p",16,"q",17,"r",18,"s",19,"t",20,"u",21,"v",22,"w",23,"x",24,"y",25,"z",26,"A",1,"B",2,"C",3,"D",4,"E",5,"F",6,"G",7,"H",8,"I",9,"J",10,"K",11,"L",12,"M",13,"N",14,"O",15,"P",16,"Q",17,"R",18,"S",19,"T",20,"U",21,"V",22,"W",23,"X",24,"Y",25,"Z",26," ",0))))))
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.