Forum Discussion

hlt1322's avatar
hlt1322
Copper Contributor
Mar 18, 2022

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 $#,###.## format so cucudd = $1,515.00

 

Anyone care to help me out?  

excel
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 19, 2022

    hlt1322 

    If return as text like

    it could be

    =TEXT(
  TEXTJOIN(,1,
      MOD(
         XMATCH( MID(B3, SEQUENCE(LEN(B3)), 1),
                         MID(B2, SEQUENCE(LEN(B2)), 1) ),
      10 )
   )/100,
  "[$$-en-US]#,##0.00" )

    If as number - above without TEXT() and apply proper format.

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 19, 2022

    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

Resources