• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace Numbers with Letter

S P P

Member
Goodnight!

Replace Numbers with Letter
 

Attachments

  • Replace Numbers with Letter SPP.xlsx
    10.9 KB · Views: 14
Try,

In D3, array (CSE) formula copied down :

=SUBSTITUTE(TRIM(SUBSTITUTE(TEXTJOIN("",,TEXT(SUBSTITUTE(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),$E$1:$N$1,$E$2:$N$2),";;;@")),"."," "))," ",",")

74934
 

Attachments

  • Replace Numbers with Letter SPP (BY).xlsx
    12.1 KB · Views: 4
bosco_yip

Thanks for the answer

Not translating TEXTJOIN

Can you replace

INDEX & MATCH or the function VLOOKUP
 
bosco_yip

Thanks for the answer

Not translating TEXTJOIN

Can you replace

INDEX & MATCH or the function VLOOKUP
Then, use the old day method,

In D3 formula copied down :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,E$2),2,F$2),3,G$2),4,H$2),5,I$2),6,J$2),7,K$2),8,L$2),9,M$2),0,N$2),".",",")

Edit : the above formula has fixed (in replacing 0 with "X" as per the OP's comment)

Regards
 
Last edited:
bosco_yip

Zero is not replacing with X
Sorry, my mistake,

here is the fixed of the formula :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,1,E$2),2,F$2),3,G$2),4,H$2),5,I$2),6,J$2),7,K$2),8,L$2),9,M$2),0,N$2),".",",")

Regards
 
bosco_yip

keep it up

The end X is missing, but before the end it replaces

$125,30 UDC,T

$120,30 UDX,T
 
bosco_yip

keep it up

The end X is missing, but before the end it replaces

$125,30 UDC,T

$120,30 UDX,T
Then,

Use this formula as replacement :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B3,"#.00"),1,E$2),2,F$2),3,G$2),4,H$2),5,I$2),6,J$2),7,K$2),8,L$2),9,M$2),0,N$2),".",",")

74939
 
SPP
For the Brazilian Portuguese version of the formula, you have to change the format of the TEXT function, where this "TEXT(B3,"#.00")" changes to "TEXTO(B3;"#,00")", Comma

Hope this helps

Hugs
decio
 
bosco_yip

TEXT(B3,"#.00")
I just needed to change the semicolon
TEXT(B3,"#,00")
Worked perfectly

Thanks a lot for the help

deciog

Muito obrigado por participar deste tópico
Já tinha feito a substituição, mas mesmo assim valeu.
 
Back
Top