Forum Discussion
excel formula problem
I need to create a formula that changes a letter price code into the corresponding price. The price code is CONSUMERAD=1234567890, c=1, o=2, n=3 and so on. I need the formula to have a $#,###.## fo...
Here is the answer for you. You may change the cell reference instead of "cucudd" in the formula. The result will be text format.
=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER("cucudd"),"C",1),"O",2),"N",3),"S",4),"U",5),"M",6),"E",7),"R",8),"A",9),"D",0))/100,"$#,##0.00")
I'd like to suggest you that you'd better use the formula below without Text function above and apply your custom format or accounting format ($#,##0.00) to the cells you want.
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER("cucudd"),"C",1),"O",2),"N",3),"S",4),"U",5),"M",6),"E",7),"R",8),"A",9),"D",0))/100